分区表绑定变量执行计划的问题

霍俊路

你好：
我的sql语句在使用绑定变量时性能很差。我对比了一下，主要问题出现在对分区表的访问上。我想请教一下您，在第7步中，怎样确认分区表的KEY？查看其在执行时访问了哪几个分区。
SQL> select count(*) from  SITE_DAILY_REPORT A,
  2  (SELECT DISTINCT DRAW_ID FROM DRAWID_SEQ
  3  WHERE GAME_ID like DECODE(:v1,'All','%',:v2)
  4  ) D  where A.DRAW_ID=D.DRAW_ID and A.REPORT_DATE BETWEEN TO_DATE(:v4,'yyyy-mm-dd') AND TO_DATE(:v5 || ' 23:59:59','
yyyy-mm-dd hh24:mi:ss');

  COUNT(*)
----------
      7515

Elapsed: 00:00:01.57

Execution Plan
----------------------------------------------------------
Plan hash value: 3242075036

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |     1 |    26 | 32705   (5)| 00:06:33 |    |          |
|   1 |  SORT AGGREGATE            |                      |     1 |    26 |            |          |    |          |
|*  2 |   HASH JOIN                |                      | 87959 |  2233K| 32705   (5)| 00:06:33 |    |          |
|   3 |    VIEW                    |                      |  7140 | 92820 |    92   (8)| 00:00:02 |    |          |
|   4 |     HASH UNIQUE            |                      |  7140 | 78540 |    92   (8)| 00:00:02 |    |          |
|*  5 |      FILTER                |                      |       |       |            |          |    |          |
|*  6 |       MAT_VIEW ACCESS FULL | DRAWID_SEQ           |  7140 | 78540 |    91   (7)| 00:00:02 |    |          |
|   7 |    PARTITION RANGE ITERATOR|                      | 87959 |  1116K| 32612   (5)| 00:06:32 |   KEY |   KEY |
|*  8 |     INDEX FAST FULL SCAN   | PK_SITE_DAILY_REPORT | 87959 |  1116K| 32612   (5)| 00:06:32 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

Maclean Liu(刘相兵

给出sqlhc 对上述查询的输出

霍俊路

您好，sqlhc已上传，请查看。谢谢。

霍俊路

霍俊路 发表于 2013-6-17 15:04
您好，sqlhc已上传，请查看。谢谢。

说明一下。统计信息已经收集了。 统计信息收集的语句
begin
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HELIOS',
                                 tabname => 'SITE_PRIZE_DAILY_REPORT',
                                 estimate_percent => 100,
                                 method_opt => 'for all columns size skewonly',
                                 degree => DBMS_STATS.AUTO_DEGREE,
                                  GRANULARITY=>'ALL',
                                 cascade=>TRUE);
   end;
   /
SQL> SELECT inserts,updates FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='SITE_DAILY_REPORT';

   INSERTS    UPDATES
---------- ----------
         5          3
。

低调小马哥

这个sql不用绑定变量的话执行计划是怎样的？和用绑定变量有差别么？时间有对比么？

霍俊路

低调小马哥 发表于 2013-6-17 21:47
这个sql不用绑定变量的话执行计划是怎样的？和用绑定变量有差别么？时间有对比么？ ...

不绑定变量
SQL> select count(*) from  SITE_DAILY_REPORT A, (SELECT DISTINCT DRAW_ID FROM DRAWID_SEQ WHERE GAME_ID like DECODE('All','All','%','All') ) D  where A.DRAW_ID=D.DRAW_ID and A.REPORT_DATE BETWEEN TO_DATE('2011-05-01','yyyy-mm-dd') AND TO_DATE('2011-05-01' || ' 23:59:59','yyyy-mm-dd hh24:mi:ss') ;

  COUNT(*)
----------
      7515

Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 3584154692

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |     1 |    27 |   567   (3)| 00:00:07 |    |  |
|   1 |  SORT AGGREGATE          |                      |     1 |    27 |            |          |    |  |
|*  2 |   HASH JOIN              |                      | 35067 |   924K|   567   (3)| 00:00:07 |    |  |
|   3 |    VIEW                  |                      |  7140 | 92820 |    90   (6)| 00:00:02 |    |  |
|   4 |     HASH UNIQUE          |                      |  7140 | 78540 |    90   (6)| 00:00:02 |    |  |
|*  5 |      MAT_VIEW ACCESS FULL| DRAWID_SEQ           |  7140 | 78540 |    89   (5)| 00:00:02 |    |  |
|   6 |    PARTITION RANGE SINGLE|                      | 35067 |   479K|   476   (2)| 00:00:06 |    90 |    90 |
|*  7 |     INDEX FAST FULL SCAN | PK_SITE_DAILY_REPORT | 35067 |   479K|   476   (2)| 00:00:06 |    90 |    90 |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."DRAW_ID"="D"."DRAW_ID")
   5 - filter(TO_CHAR("GAME_ID") LIKE '%')
   7 - filter("A"."REPORT_DATE"<=TO_DATE(' 2011-05-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2525  consistent gets
          0  physical reads
          0  redo size
        336  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
绑定变量：
SQL> select count(*) from  SITE_DAILY_REPORT A,
  2       (SELECT DISTINCT DRAW_ID FROM DRAWID_SEQ
  3       WHERE GAME_ID like DECODE(:v1,'All','%',:v2)
  4       ) D  where A.DRAW_ID=D.DRAW_ID and A.REPORT_DATE BETWEEN TO_DATE(:v4,'yyyy-mm-dd') AND TO_DATE(:v5 || ' 23:59:
59','
  5   yyyy-mm-dd hh24:mi:ss');

  COUNT(*)
----------
      7515

Elapsed: 00:00:01.31

Execution Plan
----------------------------------------------------------
Plan hash value: 3242075036

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |     1 |    26 | 32705   (5)| 00:06:33 |    |          |
|   1 |  SORT AGGREGATE            |                      |     1 |    26 |            |          |    |          |
|*  2 |   HASH JOIN                |                      | 87959 |  2233K| 32705   (5)| 00:06:33 |    |          |
|   3 |    VIEW                    |                      |  7140 | 92820 |    92   (8)| 00:00:02 |    |          |
|   4 |     HASH UNIQUE            |                      |  7140 | 78540 |    92   (8)| 00:00:02 |    |          |
|*  5 |      FILTER                |                      |       |       |            |          |    |          |
|*  6 |       MAT_VIEW ACCESS FULL | DRAWID_SEQ           |  7140 | 78540 |    91   (7)| 00:00:02 |    |          |
|   7 |    PARTITION RANGE ITERATOR|                      | 87959 |  1116K| 32612   (5)| 00:06:32 |   KEY |   KEY |
|*  8 |     INDEX FAST FULL SCAN   | PK_SITE_DAILY_REPORT | 87959 |  1116K| 32612   (5)| 00:06:32 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."DRAW_ID"="D"."DRAW_ID")
   5 - filter(TO_DATE(:V4,'yyyy-mm-dd')<=TO_DATE(:V5||' 23:59:59','  yyyy-mm-dd hh24:mi:ss'))
   6 - filter(TO_CHAR("GAME_ID") LIKE DECODE(:V1,'All','%',:V2))
   8 - filter("A"."REPORT_DATE">=TO_DATE(:V4,'yyyy-mm-dd') AND "A"."REPORT_DATE"<=TO_DATE(:V5||'
              23:59:59','  yyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2525  consistent gets
          0  physical reads
          0  redo size
        336  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

低调小马哥

加绑定变量后分区访问由PARTITION RANGE SINGLE变成PARTITION RANGE ITERATOR了，而且多了一步第5步的filter，绑定变量后关联的数据多了，然后再filter，没绑定变量的时候直接在PK_SITE_DAILY_REPORT 索引过滤后才hash join，所以我觉得可能是这个问题慢了点，对了，绑定变量和你固定值是一样的么？

Maclean Liu(刘相兵

                      Peeked Binds (identified by position):
                      --------------------------------------

                         3 - (VARCHAR2(30), CSID=852): '2011-05-01'
                         4 - (VARCHAR2(30), CSID=852): '2011-05-31'



似乎开了bind peek


你在6楼的演示似乎说明  2者的逻辑读 物理读都是一样的， 但为什么 耗费的时间很大差异，这是需要挖掘的地方之一

至于执行计划对于 bind variable 不显示 KEY 我觉得也是正常的， 因为不同的variable 显然对应不同的KEY 不会在计划里写死，否则也谈不上游标共享了

霍俊路

低调小马哥 发表于 2013-6-18 14:09
加绑定变量后分区访问由PARTITION RANGE SINGLE变成PARTITION RANGE ITERATOR了，而且多了一步第5步的filte ...

数据是一样的，这个我可以保证 。

霍俊路

打印了2份。不过这2个内容差不多。

霍俊路

Maclean Liu(刘相兵 发表于 2013-6-18 14:52
Peeked Binds (identified by position):
                      ----------------- ...

v$sql_plan_statistics查询的结果如下：
绑定变量的统计信息各个步骤的执行时间
  
        HV         cn ID           cost       card operation                                                 pos       ROWS    ELAPSED
---------- ---------- ------ ---------- ---------- -------------------------------------------------- ---------- ---------- ----------
1067002255          0    0          587            SELECT STATEMENT                                       587
1067002255          0    1                       1  SORT AGGREGATE                                          1     1        1.3
1067002255          0    2A         587      35067   HASH JOIN                                              1       7515           1.3
1067002255          0    3           89       7191    VIEW                                                  1     142793           .22
1067002255          0    4           89       7191     HASH UNIQUE                                          1     142793           .22
1067002255          0    5F                             FILTER                                              1     142800           .14
1067002255          0    6F          88       7191       MAT_VIEW ACCESS FULL DRAWID_SEQ                    1     142800             0
1067002255          0    7          497      35067    PARTITION RANGE ITERATOR :KY-KY                       2      17261           .89
1067002255          0    8F         497      35067     INDEX FAST FULL SCAN PK_SITE_DAILY_REPORT:KY-KY          1      17261        .89

非绑定变量的统计信息各个步骤的执行时间                                                   
        HV         cn  ID           cost       card operation                                                 pos       ROWS    ELAPSED
---------- ---------- ------ ---------- ---------- -------------------------------------------------- ---------- ---------- ----------
3939726461          0    0          564            SELECT STATEMENT                                       564
3939726461          0    1                       1  SORT AGGREGATE                                          1     1        .44
3939726461          0    2A         564      35067   HASH JOIN                                              1       7515           .43
3939726461          0    3           87       7191    VIEW                                                  1     142793           .19
3939726461          0    4           87       7191     HASH UNIQUE                                          1     142793           .19
3939726461          0    5F          86       7191      MAT_VIEW ACCESS FULL DRAWID_SEQ                     1     142800             0
3939726461          0    6          476      35067    PARTITION RANGE SINGLE :90-90                         2      17261           .06
3939726461          0    7F         476      35067     INDEX FAST FULL SCAN PK_SITE_DAILY_REPORT:90-90          1      17261        .06

根据上面执行时间的差异，绑定变量的执行计划在使用PARTITION RANGE ITERATOR时，执行时间明显大。感觉这个计划在内部卡住了。而2个计划的整个逻辑读和物理读却都一样，这个地方让我非常的不明白。能否指点一下，我跟踪一下这个sql在内部怎么执行的？卡在了哪个地方？
                                                   

霍俊路

低调小马哥 发表于 2013-6-18 14:09
加绑定变量后分区访问由PARTITION RANGE SINGLE变成PARTITION RANGE ITERATOR了，而且多了一步第5步的filte ...

非常感谢您的指点。关于由PARTITION RANGE SINGLE和 PARTITION RANGE ITERATOR。我在上面
的执行结果上 2个的consistent gets结果是一样的。所以代价应该是相同的吧？这个地方是不是有BUG?感觉好像是内部卡住了。

低调小马哥

我觉得可能不是bug，10046跟踪一下吧，看看绑定变量sql语句的时间都花在什么地方了

霍俊路

低调小马哥 发表于 2013-6-18 21:24
我觉得可能不是bug，10046跟踪一下吧，看看绑定变量sql语句的时间都花在什么地方了 ...

绑定变量的10046 trace
*** 2013-06-19 09:27:52.774
*** ACTION NAME:() 2013-06-19 09:27:52.774
*** MODULE NAME:(SQL*Plus) 2013-06-19 09:27:52.774
*** SERVICE NAME:(cslcq) 2013-06-19 09:27:52.774
*** SESSION ID:(150.16) 2013-06-19 09:27:52.774
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339458274193442
*** 2013-06-19 09:28:07.252
WAIT #2: nam='SQL*Net message from client' ela= 14139148 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339458288332910
=====================
PARSING IN CURSOR #1 len=267 dep=0 uid=58 oct=3 lid=58 tim=1339458288333705 hv=13638860 ad='5c45f330'
select count(*) from  SITE_DAILY_REPORT A,
(SELECT DISTINCT DRAW_ID FROM DRAWID_SEQ
WHERE GAME_ID like DECODE(:v1,'All','%',:v2)
) D  where A.DRAW_ID=D.DRAW_ID and A.REPORT_DATE BETWEEN TO_DATE(:v4,'yyyy-mm-dd') AND TO_DATE(:v5 || ' 23:59:59','yyyy-mm-dd hh24:mi:ss')
END OF STMT
PARSE #1:c=0,e=611,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1339458288333702
BINDS #1:
kkscoacd
Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=128 off=0
  kxsbbbfp=2af6504ebf40  bln=32  avl=03  flg=05
  value="All"
Bind#1
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=0 off=32
  kxsbbbfp=2af6504ebf60  bln=32  avl=03  flg=01
  value="All"
Bind#2
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=0 off=64
  kxsbbbfp=2af6504ebf80  bln=32  avl=10  flg=01
  value="2011-05-01"
Bind#3
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=0 off=96
  kxsbbbfp=2af6504ebfa0  bln=32  avl=10  flg=01
  value="2011-05-01"
EXEC #1:c=7999,e=7337,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1339458288341119
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339458288341167
FETCH #1:c=1184819,e=1156963,p=0,cr=2525,cu=0,mis=0,r=1,dep=0,og=1,tim=1339458289498159
WAIT #1: nam='SQL*Net message from client' ela= 413 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339458289499291
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1339458289499336
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339458289499355
*** 2013-06-19 09:28:27.943
WAIT #1: nam='SQL*Net message from client' ela= 19039499 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339458308538875
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2525 pr=0 pw=0 time=1157076 us)'
STAT #1 id=2 cnt=7515 pid=1 pos=1 obj=0 op='HASH JOIN  (cr=2525 pr=0 pw=0 time=4073660 us)'
STAT #1 id=3 cnt=142793 pid=2 pos=1 obj=0 op='VIEW  (cr=374 pr=0 pw=0 time=168307 us)'
STAT #1 id=4 cnt=142793 pid=3 pos=1 obj=0 op='HASH UNIQUE (cr=374 pr=0 pw=0 time=168305 us)'
STAT #1 id=5 cnt=142800 pid=4 pos=1 obj=0 op='FILTER  (cr=374 pr=0 pw=0 time=142923 us)'
STAT #1 id=6 cnt=142800 pid=5 pos=1 obj=180005 op='MAT_VIEW ACCESS FULL DRAWID_SEQ (cr=374 pr=0 pw=0 time=107 us)'
STAT #1 id=7 cnt=17261 pid=2 pos=2 obj=0 op='PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=2151 pr=0 pw=0 time=535415 us)'
STAT #1 id=8 cnt=17261 pid=7 pos=1 obj=117887 op='INDEX FAST FULL SCAN PK_SITE_DAILY_REPORT PARTITION: KEY KEY (cr=2151 pr=0 pw=0 time=518104 us)'
=====================
PARSING IN CURSOR #2 len=55 dep=0 uid=58 oct=42 lid=58 tim=1339458308539284 hv=2217940283 ad='0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #2:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1339458308539281
EXEC #2:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1339458308539388
非绑定变量的trace
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339457838993623
WAIT #2: nam='SQL*Net message from client' ela= 4051658 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339457843045564
=====================
PARSING IN CURSOR #1 len=290 dep=0 uid=58 oct=3 lid=58 tim=1339457843050011 hv=3939726461 ad='5db4e858'
select count(*) from  SITE_DAILY_REPORT A, (SELECT DISTINCT DRAW_ID FROM DRAWID_SEQ WHERE GAME_ID like DECODE('All','All','%','All') ) D  where A.DRAW_ID=D.DRAW_ID and A.REPORT_DATE BETWEEN TO_DATE('2011-05-01','yyyy-mm-dd') AND TO_DATE('2011-05-01' || ' 23:59:59','yyyy-mm-dd hh24:mi:ss')
END OF STMT
PARSE #1:c=4000,e=4290,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1339457843050003
BINDS #1:
EXEC #1:c=0,e=120,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1339457843050198
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339457843050250
FETCH #1:c=290955,e=339862,p=0,cr=2525,cu=0,mis=0,r=1,dep=0,og=1,tim=1339457843390142
WAIT #1: nam='SQL*Net message from client' ela= 400 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339457843391457
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1339457843391518
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339457843391538
*** 2013-06-19 09:21:01.852
WAIT #1: nam='SQL*Net message from client' ela= 29511409 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1339457872902989
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2525 pr=0 pw=0 time=339917 us)'
STAT #1 id=2 cnt=7515 pid=1 pos=1 obj=0 op='HASH JOIN  (cr=2525 pr=0 pw=0 time=521667 us)'
STAT #1 id=3 cnt=142793 pid=2 pos=1 obj=0 op='VIEW  (cr=374 pr=0 pw=0 time=144593 us)'
STAT #1 id=4 cnt=142793 pid=3 pos=1 obj=0 op='HASH UNIQUE (cr=374 pr=0 pw=0 time=144591 us)'
STAT #1 id=5 cnt=142800 pid=4 pos=1 obj=180005 op='MAT_VIEW ACCESS FULL DRAWID_SEQ (cr=374 pr=0 pw=0 time=85 us)'
STAT #1 id=6 cnt=17261 pid=2 pos=2 obj=0 op='PARTITION RANGE SINGLE PARTITION: 90 90 (cr=2151 pr=0 pw=0 time=34598 us)'
STAT #1 id=7 cnt=17261 pid=6 pos=1 obj=117887 op='INDEX FAST FULL SCAN PK_SITE_DAILY_REPORT PARTITION: 90 90 (cr=2151 pr=0 pw=0 time=34589 us)'
=====================
PARSING IN CURSOR #2 len=55 dep=0 uid=58 oct=42 lid=58 tim=1339457872903627 hv=2217940283 ad='0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #2:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1339457872903625
EXEC #2:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1339457872903749

从trace 文件看，就是在fetch的时候， 绑定变量时间较大，cpu时间明显较大。

霍俊路

SQL>  select /*+INDEX(A PK_SITE_DAILY_REPORT) */ count(*) from SITE_DAILY_REPORT A  where A.REPORT_DATE BETWEEN TO_DAT
:v4,'yyyy-mm-dd') AND TO_DATE(:v5 || ' 23:59:59','yyyy-mm-dd hh24:mi:ss');

  COUNT(*)
----------
     17261

Elapsed: 00:00:00.98

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |     1 |     8 |   143K  (2)|       |       |
|   1 |  SORT AGGREGATE            |                      |     1 |     8 |            |       |       |
|*  2 |   FILTER                   |                      |       |       |            |       |       |
|   3 |    PARTITION RANGE ITERATOR|                      | 87959 |   687K|   143K  (2)|   KEY |   KEY |
|*  4 |     INDEX FULL SCAN        | PK_SITE_DAILY_REPORT | 87959 |   687K|   143K  (2)|   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE(:V4,'yyyy-mm-dd')<=TO_DATE(:V5||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))
   4 - access("A"."REPORT_DATE">=TO_DATE(:V4,'yyyy-mm-dd') AND
              "A"."REPORT_DATE"<=TO_DATE(:V5||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))
       filter("A"."REPORT_DATE">=TO_DATE(:V4,'yyyy-mm-dd') AND
              "A"."REPORT_DATE"<=TO_DATE(:V5||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2136  consistent gets
          0  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>    select /*+INDEX(A PK_SITE_DAILY_REPORT) */ count(*) from SITE_DAILY_REPORT A  where A.REPORT_DATE BETWEEN TO_D
E('2011-05-01','yyyy-mm-dd') AND TO_DATE('2011-05-01' || ' 23:59:59','yyyy-mm-dd hh24:mi:ss');

  COUNT(*)
----------
     17261

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                 | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                      |     1 |     8 |  2144   (1)|       |       |
|   1 |  SORT AGGREGATE         |                      |     1 |     8 |            |       |       |
|   2 |   PARTITION RANGE SINGLE|                      | 35067 |   273K|  2144   (1)|    90 |    90 |
|*  3 |    INDEX FULL SCAN      | PK_SITE_DAILY_REPORT | 35067 |   273K|  2144   (1)|    90 |    90 |
-----------------------------------------------------------------------------------------------------

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

   3 - access("A"."REPORT_DATE">=TO_DATE(' 2011-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "A"."REPORT_DATE"<=TO_DATE(' 2011-05-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter("A"."REPORT_DATE"<=TO_DATE(' 2011-05-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2136  consistent gets
          0  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
从单表的绑定变量的执行计划看，确实是在执行iteration range scan 的时候，内部出了些问题，导致其执行时间过长

低调小马哥

我觉得绑定变量时，可能是PARTITION RANGE ITERATOR PARTITION: KEY KEY执行了全部的分区的索引扫描，没有进行分区裁剪，而没有绑定变量时用到分区裁剪了，只进行一个分区的索引扫描，虽然两者分区表扫描的逻辑读相同，但我觉得oracle实际上在绑定变量时执行PARTITION RANGE ITERATOR PARTITION可能执行了全部的分区扫描，时间对比上518104/34589=14，分区数是不是14个？以上纯属本人猜测，仅供参考。

霍俊路

低调小马哥 发表于 2013-6-20 23:12
我觉得绑定变量时，可能是PARTITION RANGE ITERATOR PARTITION: KEY KEY执行了全部的分区的索引扫描，没有 ...

非常感谢您的指导。这个分区表一共有122个分区。我觉得会不会是这样，当分区的个数比较多的时候，oracle自身在使用ITERATOR 算分区的个数时，他自身的算法存在问题，导致了这样的结果。

psufnxk2000

sqlhc_cslcq_cqdb1_10.2.0.4.0_4npnv8ffvn3qn_20130617140116.html 文件中
Peeked Binds (identified by position):
                      --------------------------------------

                         3 - (VARCHAR2(30), CSID=852): '2011-05-01'
                         4 - (VARCHAR2(30), CSID=852): '2011-05-31'

而 在网页中给出的是
E('2011-05-01','yyyy-mm-dd') AND TO_DATE('2011-05-01' || ' 23:59:59','yyyy-mm-dd hh24:mi:ss');

end值是不一样的。。

你的原因会不会是共享了不当的执行计划。

Stone

霍俊路 发表于 2013-6-17 15:32
说明一下。统计信息已经收集了。 统计信息收集的语句
begin
   DBMS_STATS.GATHER_TABLE_STATS(ownname  ...

首先根据收集信息的语句可以看出应该是收集错对象啦，应该是“SITE_DAILY_REPORT”吧 :)
所以建议最好重新对正确的对象收集下信息。

begin
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HELIOS',
                                 tabname => 'SITE_PRIZE_DAILY_REPORT',
                                 estimate_percent => 100,
                                 method_opt => 'for all columns size skewonly',
                                 degree => DBMS_STATS.AUTO_DEGREE,
                                  GRANULARITY=>'ALL',
                                 cascade=>TRUE);

而且从sqlh的报告也可以印证这一点：

Tables Summary

#	Table Name	Owner	Num Rows	Table Sample Size	Last Analyzed	Indexes	Avg Index Sample Size	Table Columns	Columns with Histogram	Avg Column Sample Size
1	DRAWID_SEQ	HELIOS	142800	142800	14-JUN-13 09:42:02	1	142800	3	3	142800
2	SITE_DAILY_REPORT	HELIOS	35183760	57062	23-OCT-12 22:32:53	2	0	42	0	4792

Indexes Summary

#

Table Name

Table Owner

Index Name

Index Owner

In MEM Plan

In AWR Plan

Num Rows

Index Sample Size

Last Analyzed

Index Columns

Columns with Histogram

Avg Column Sample Size

1

DRAWID_SEQ

HELIOS

SYS_C007083

HELIOS

142800

142800

14-JUN-13 09:42:03

2

2

142800

2

SITE_DAILY_REPORT

HELIOS

IDX_SITE_DAILY_REPORT2

HELIOS

34730178

0

06-DEC-12 22:01:38

3

0

5568

3

SITE_DAILY_REPORT

HELIOS

PK_SITE_DAILY_REPORT

HELIOS

YES

34730178

0

06-DEC-12 22:01:38

4

0

5568

Good luck.

霍俊路

psufnxk2000 发表于 2013-6-21 21:19
sqlhc_cslcq_cqdb1_10.2.0.4.0_4npnv8ffvn3qn_20130617140116.html 文件中
Peeked Binds (identified by p ...

我后来有传了一份上去。之前的传错了。sorry