Oracle在查询执行期间不使用文字值进行分区修剪

嗨，汤姆，

我们有一张表，有大约700个分区。

我们的测试查询是

select count(*) from fs_1d f where f.fs_date = '03-JAN-2017' and f.feed_code = 'E4F1'
复制

当我们解释这个查询的计划时，这个计划是正确的。它直接进入分区 #697 (修剪)，然后使用索引FS_1D_IDX2。

SQL> explain plan for
  2  select count(*)
  3  from fs_1d f
  4  where f.fs_date  = '03-JAN-2017'
  5  and f.feed_code  = 'E4F1'
  6  ;

Explained.

Elapsed: 00:00:00.02

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    14 |  1112   (0)|       |       |
|   1 |  SORT AGGREGATE                     |            |     1 |    14 |            |       |       |
|   2 |   PARTITION RANGE SINGLE            |            |  1189 | 16646 |  1112   (0)|   697 |   697 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| FS_1D      |  1189 | 16646 |  1112   (0)|   697 |   697 |
|*  4 |     INDEX RANGE SCAN                | FS_1D_IDX2 |  7392 |       |   109   (0)|   697 |   697 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("F"."FS_DATE"=TO_DATE(' 2017-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - access("F"."FEED_CODE"='E4F1')

20 rows selected.

Elapsed: 00:00:00.07
复制

但是当我们实际执行查询时，Oracle没有使用上面解释的计划。

SQL> select count(*)
 2  from fs_1d f
  3  where f.fs_date  = '03-JAN-2017'
  4  and f.feed_code  = 'E4F1'
  5  ;

  COUNT(*)
----------
    240345

Elapsed: 00:09:49.21

SQL> SELECT sql_id, piece, sql_text
  2  FROM v$sqltext
  3  WHERE sql_id = 'fx1ugkqm3hx5q'
  4  ORDER BY piece
  5  ;

SQL_ID             PIECE SQL_TEXT
------------- ---------- ----------------------------------------------------------------
fx1ugkqm3hx5q          0 select count(*) from fs_1d f where f.fs_date  = :"SYS_B_0" and f
fx1ugkqm3hx5q          1 .feed_code  = :"SYS_B_1"

Elapsed: 00:00:00.04

SQL> SELECT
  2  id, depth,
  3  operation, options,
  4  object_type, object_owner, object_name,
  5  cardinality, bytes, cost
  6  FROM v$sql_plan_statistics_all
  7  WHERE sql_id = 'fx1ugkqm3hx5q'
  8  ORDER BY id
  9  ;

  ID DEPTH OPERATION            OPTIONS    OBJECT_TYP OBJECT_OWN OBJECT_NAM CARDINALITY      BYTES    COST
---- ----- -------------------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
   0     0 SELECT STATEMENT                                                                           1096697
   1     1 SORT                 AGGREGATE                                             1         14
   2     2 VIEW                            VIEW                  VW_TE_2            125               1096697
   3     3 UNION-ALL
   4     4 PARTITION RANGE      SINGLE                                              119       1666    1096697
   5     5 TABLE ACCESS         FULL       TABLE      OCFGMR_P   FS_1D              119       1666    1096697
   6     4 PARTITION RANGE      SINGLE                                                6         84    1096696
   7     5 TABLE ACCESS         FULL       TABLE      OCFGMR_P   FS_1D                6         84    1096696

8 rows selected.

Elapsed: 00:00:00.04
复制

实际执行计划就好像没有提供实际的文字值一样。

SQL> explain plan for
  2  select count(*)
  3  from fs_1d f
  4  where f.fs_date  = :d1
  5  and f.feed_code  = 'E4F1'
  6  ;

Explained.

Elapsed: 00:00:00.04

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     1 |    12 | 23544   (1)|       |       |
|   1 |  SORT AGGREGATE           |         |     1 |    12 |            |       |       |
|   2 |   VIEW                    | VW_TE_2 |  1309 |       | 23544   (1)|       |       |
|   3 |    UNION-ALL              |         |       |       |            |       |       |
|   4 |     PARTITION RANGE SINGLE|         |  1247 | 14964 | 23544   (1)|   KEY |   KEY |
|*  5 |      TABLE ACCESS FULL    | FS_1D   |  1247 | 14964 | 23544   (1)|   KEY |   KEY |
|   6 |     PARTITION RANGE SINGLE|         |    62 |   744 | 23544   (1)|   KEY |   KEY |
|*  7 |      TABLE ACCESS FULL    | FS_1D   |    62 |   744 | 23544   (1)|   KEY |   KEY |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("F"."FEED_CODE"='E4F1' AND ("F"."FS_DATE"=TO_DATE(' 2016-07-30
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."FS_DATE">=TO_DATE(' 2016-07-23
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."FS_DATE"=TO_DATE(' 2016-07-19
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."FS_DATE"=TO_DATE(' 2016-07-25
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."FS_DATE"


我们从这个测试用例中得到的问题:

(1) 为什么Oracle解释计划和实际计划会有所不同 (共享池很干净，因为我们在此测试之前进行了新的冲洗)
(2) 为什么Oracle不进行peek并使用查询中提供的文字值，而是对实际计划使用绑定。

对这两个问题有什么见解和建议吗？

非常感谢
 

复制

1) 当我看到

f.fs_date = :"SYS_B_0"

它告诉我你启用了光标共享。所以你所有的文字都将被绑定取代。

2) 我们在 “真正的” 执行过程中窥视绑定。如果您运行 “解释计划” 命令，那么我们 * 不会 * 偷看绑定。这就是为什么解释计划命令可以对你 “撒谎”。

但是光标共享 ....那不是一个有趣的地方 :-)