high cluf make the execute plan do not use index

最新推荐文章于 2021-04-06 02:55:06 发布

转载最新推荐文章于 2021-04-06 02:55:06 发布 · 231 阅读

本文探讨了一个特定SQL查询的执行计划问题。通过对表和索引的统计信息进行分析，揭示了为何Oracle选择全表扫描而非使用索引扫描。文章进一步提出了改进方案，包括重新创建分区以提高查询效率。

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

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------

| 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

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/