一条语句不走索引！

alexanderr

这个clustering_factor有临界值吗?
就是在多少以上CBO会选择FTS，在多少以下又会走index？
还是说这个选择本身就很模糊，对不同表或者语句有不同的标准？
谢谢

alexanderr

这两种修改都是对全局的吗？
如果是这样，我想我就不会做修改了

谢谢

alexanderr

这是10053的trace文件

请帮忙分析，谢谢

[ 本帖最后由 alexanderr 于 2010-9-8 16:16 编辑 ]

Yong Huang

According to the 10053 trace in ora_23595.txt, the SQL
select * from SFISM4.R_SN_DETAIL_T WHERE IN_STATION_TIME <= SYSDATE - 30
chooses FTS because
(1) your table has 842467 blocks, Oracle uses its internal algorithm for multiblock reads to reach a cost of 187494.82 for FTS (which means the multiblock read factor is about 4.5);
(2) index range scan followed by table scan has a cost of 3274969, which is about 2 + 36015*0.41925 + 7775560*0.41925, i.e. index blevel + index leaf blocks*index selectivity + clustering factor*table selectivity (ix_sel_with_filters).

You can see the index access path is about 17 times higher. If the table and index stats are fairly accurate, you can't blame CBO. One thing you can easily verify is number of rows and blocks. If they are close to what you see in select count(*) ... and dba_segments, then check index selectivity. If you think you should only get 25% of the rows, then the selectivity of 0.41925 is way too high. Can you check again to see if the stats are up-to-date? If yes, are the data on IN_STATION_TIME somewhat skewed? If yes, try collecting a histogram with 254 buckets.

Yong Huang

alexanderr

execute 'select count(*) from table' ,the number of rows is 12688271,
i can see that the table was gathered statistics on Sep 2ed and the number of rows is 13130285 from the ORACLE EM.
the blocks number is exactly the same.
is this table stats accurate enough?


which means the multiblock read factor is about 4.5);
i don't understand this,can you tell me how the result 4.5 is calculated?

i.e. index blevel + index leaf blocks*index selectivity + clustering factor*table selectivity (ix_sel_with_filters).
i am so interested in where i can learn the above fomula and even more comprehensive
i will appreciate it if u can give me some links to study

Yong Huang

13130285 is close enough to 12688271 in my opinion.

You can find answers to your latter two questions in Woflgang Breitling's articles at www.centrexcc.com and Jonathan Lewis's book "Cost Based Optimizer".

Yong Huang

janeson_lin

修改clustering_factor只是给表本身一个假的统计信息，将它调小，在计算成本的时候，使采用索引的时候成本变低，可以观察执行计划，当clustering_factor小到一定程度的时候，采用索引的成本将比采用全部扫描的成本要低的时候，它就采用索引方式，这只对用到这张表的这个索引的相关语句有影响，并不是全局的。

alexanderr

谢谢

alexanderr

thank u , master

eagle_fan

在返回结果集大的情况下，全表扫描未必比走索引慢