a few days ago,jianxia call me to help her a strange execute plan.
The sql statement is like that:
select count(*)
from par_autoupdown_log a
where QiyeBianma = '000000000005912310001121'
and LogTime >= TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and logtime<=TO_DATE('2010-10-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
talbe “par_autoupdown_log” is partition table.ervry partition has 300W-400W rows.and the partition key is “logtime”.the distinct number of logtime in a partition is 200W-300W.
table “par_autoupdown_log” has been analyzed.so as we know,the statement above must be scand by index. But ,actually,it is scand by table access full.so ,why?
First,let’s see the explan plan for the statement:
----------------------------------------------------------------------------------------
SQL_ID 5njbp2nx1bhtb, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/count(*) from par_autoupdown_log where QiyeBianma =
'000000000005912310001121' and LogTime >= TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') and logtime<=TO_DATE('2010-10-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Plan hash value: 4139055448
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.14 | 7419 | 5095 |
| 2 | PARTITION RANGE SINGLE| | 1 | 1652 | 14444 |00:00:00.06 | 7419 | 5095 |
|* 3 | TABLE ACCESS FULL | PAR_AUTOUPDOWN_LOG | 1 | 1652 | 14444 |00:00:00.04 | 7419 | 5095 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("QIYEBIANMA"='000000000005912310001121' AND "LOGTIME"<=TO_DATE('2010-10-20 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "LOGTIME">=TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
------------------------------------------------------------------------------------------
And ,if we enforce it using index by hint.the explan plan will be like that:
------------------------------------------------------------------------------------------------
SQL_ID 8s123p6mfacu9, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(a IDX_AUTOUPDOWNLOG_LOGTIME)*/count(*) from par_autoupdown_log a where
QiyeBianma = '000000000005912310001121' and LogTime >= TO_DATE('2010-10-19 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and logtime<=TO_DATE('2010-10-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Plan hash value: 2653469445
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.14 | 11537 |
| 2 | PARTITION RANGE SINGLE | | 1 | 1652 | 14444 |00:00:00.27 | 11537 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| PAR_AUTOUPDOWN_LOG | 1 | 1652 | 14444 |00:00:00.25 | 11537 |
|* 4 | INDEX RANGE SCAN | IDX_AUTOUPDOWNLOG_LOGTIME | 1 | 19170 | 37571 |00:00:00.04 | 212 |
---------------------------------------------------------------------------------------------------------------------------
19170
29848
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("QIYEBIANMA"='000000000005912310001121')
4 - access("LOGTIME">=TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"LOGTIME"<=TO_DATE('2010-10-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
You see that “table access full ” consume only 7419 buffers compared whih 11537 “index range scan”.that’s why cbo use “table access full”. But,according to the data distribution of the logtime,we know that “index range scan” must be more better.
So ,we do the 10053 event,and the contents like that:
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PAR_AUTOUPDOWN_LOG Alias: A Partition [2]
#Rows: 746204 #Blks: 7535 AvgRowLen: 66.00
#Rows: 746204 #Blks: 7535 AvgRowLen: 66.00
Index Stats::
Index: CON_AUTOUPDOWNLOG_ID Col#: 1
LVLS: 2 #LB: 4965 #DK: 1606749 LB/K: 1.00 DB/K: 1.00 CLUF: 401697.00
Index: IDX_AUTOUPDOWNLOG_LOGTIME Col#: 3 PARTITION [2]
LVLS: 2 #LB: 4120 #DK: 379660 LB/K: 1.00 DB/K: 1.00 CLUF: 204455.00
LVLS: 2 #LB: 4120 #DK: 379660 LB/K: 1.00 DB/K: 1.00 CLUF: 204455.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#4): QIYEBIANMA(VARCHAR2) Part#: 2
AvgLen: 24.00 NDV: 25 Nulls: 0 Density: 0.04
Column (#4): QIYEBIANMA(VARCHAR2)
AvgLen: 24.00 NDV: 25 Nulls: 0 Density: 0.04
Column (#3): LOGTIME(DATE) Part#: 2
AvgLen: 8.00 NDV: 379660 Nulls: 0 Density: 3.7598e-006 Min: 2455471 Max: 2455502
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#3): LOGTIME(DATE)
AvgLen: 8.00 NDV: 379660 Nulls: 0 Density: 3.7598e-006 Min: 2455471 Max: 2455502
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Table: PAR_AUTOUPDOWN_LOG Alias: A
Card: Original: 746204 Rounded: 1313 Computed: 1312.78 Non Adjusted: 1312.78
Access Path: TableScan
Cost: 1692.91 Resp: 1692.91 Degree: 0
Cost_io: 1650.00 Cost_cpu: 249695308
Resp_io: 1650.00 Resp_cpu: 249695308
Access Path: index (RangeScan)
Index: IDX_AUTOUPDOWNLOG_LOGTIME
resc_io: 9177.00 resc_cpu: 56876023
ix_sel: 0.043982 ix_sel_with_filters: 0.043982
Cost: 9186.77 Resp: 9186.77 Degree: 1
Best:: AccessPath: TableScan
Cost: 1692.91 Degree: 1 Resp: 1692.91 Card: 1312.78 Bytes: 0
According to the <>,we know that the algorithm of “cost” by “index range scan” is like that:
Cost=LVLS+ ix_sel* LB(leaf block)+ ix_sel_with_filters* CLUF
So ,why “index range scan” is more inefficient than “table access full”.the answer is clus of the index.
So ,I suggest jianxia to recreate partition by ordering by the logtime column.and the culs will low down.
------------------------------------------------------------------
英文写的有点 不尽如人意 的地方,很多话没办法说清楚,故用中文补充点:
我们知道,以 logtime 的数据分布来说(包括列的信息和直方图的信息),是一定需要走索引的,那分析过后,为什么还没走索引呢?这时,其实我们就需要考虑到是否是 索引的 CLUF 过高导致的,当时我以为 执行计划 在执行和通过 explain plan 时是不一样的,所以,拼命地 通过 /*+ gather_plan_statistics*/ 去看真实的执行计划,结果还是一样。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14730395/viewspace-680577/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14730395/viewspace-680577/
本文探讨了一个特定SQL查询的执行计划问题。通过对表和索引的统计信息进行分析,揭示了为何Oracle选择全表扫描而非使用索引扫描。文章进一步提出了改进方案,包括重新创建分区以提高查询效率。
824

被折叠的 条评论
为什么被折叠?



