请教：搞不懂oracle的CBO怎么工作？

显示全部楼层 · 2005-10-30 17:05:33

SQL> select di.index_name,di.table_name,di.blevel,di.leaf_blocks,di.num_rows,di.last_analyzed from dba_indexes di
2where di.index_name='AR_RE_N1';
INDEX_NAME
TABLE_NAME
BLEVEL LEAF_BLOCKS NUM_ROWS LAST_ANALY
------------------------------ ------------------------------ ---------- ----------- ---------- ----------
AR_RE_N1
AR_RE
21754 787546 23-6月 -04
SQL> set autotrace traceonly explain
SQL> select *
2from AR_CA ac,AR_REAM
3where AM.CASH_ID=AC.CASH_ID;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=18299 Card=785750 By

tes=241225250)
10 HASH JOIN (Cost=18299 Card=785750 Bytes=241225250)
21 TABLE ACCESS (FULL) OF 'AR_CA' (Cost=239

Card=108350 Bytes=13652100)
31 TABLE ACCESS (FULL) OF 'AR_RE'

(Cost=2280 Card=796630 Bytes=144190030)
SQL>
SQL> analyze index ar.AR_RE_N1 delete statistics;
索引已分析

SQL> set autotrace traceonly explain;
--可以看到没用全表扫描了
SQL> select *
2from AR_CA ac,AR_REAM
3where AM.CASH_ID=AC.CASH_ID;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=4251 Card=785750 Byt

es=241225250)
10 MERGE JOIN (Cost=4251 Card=785750 Bytes=241225250)
21 TABLE ACCESS (BY INDEX ROWID) OF 'AR_RE'

(Cost=826 Card=796630 Bytes=144190030)
32 INDEX (FULL SCAN) OF 'AR_RE_N1' (

NON-UNIQUE) (Cost=26 Card=796630)
41 SORT (JOIN) (Cost=3186 Card=108350 Bytes=13652100)
54 TABLE ACCESS (FULL) OF 'AR_CA' (Cost=23

9 Card=108350 Bytes=13652100)

SQL> set autotrace off
SQL> analyze index ar.AR_RE_N1 estimate statistics;
索引已分析
--又是全表扫描了
SQL> select di.index_name,di.table_name,di.blevel,di.leaf_blocks,di.num_rows,di.last_analyzed from dba_indexes di
2where di.index_name='AR_RE_N1';
INDEX_NAME
TABLE_NAME
BLEVEL LEAF_BLOCKS NUM_ROWS LAST_ANALY
------------------------------ ------------------------------ ---------- ----------- ---------- ----------
AR_RE_N1
AR_RE
21754 787546 23-6月 -04
SQL> set autotrace traceonly explain;
SQL> select *
2from AR_CA ac,AR_REAM
3where AM.CASH_ID=AC.CASH_ID;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=18299 Card=785750 By

tes=241225250)
10 HASH JOIN (Cost=18299 Card=785750 Bytes=241225250)
21 TABLE ACCESS (FULL) OF 'AR_CA' (Cost=239

Card=108350 Bytes=13652100)
31 TABLE ACCESS (FULL) OF 'AR_RE'

(Cost=2280 Card=796630 Bytes=144190030)
--用hits也没用全表扫描
SQL> select /*+FIRST_ROWS*/*
2from AR_CA ac,AR_REAM
3where AM.CASH_ID=AC.CASH_ID;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=325289 Car

d=785750 Bytes=241225250)
10 NESTED LOOPS (Cost=325289 Card=785750 Bytes=241225250)
21 TABLE ACCESS (FULL) OF 'AR_CA' (Cost=239

Card=108350 Bytes=13652100)
31 TABLE ACCESS (BY INDEX ROWID) OF 'AR_RE'

(Cost=3 Card=796630 Bytes=144190030)
43 INDEX (RANGE SCAN) OF 'AR_RE_N1'

(NON-UNIQUE) (Cost=2 Card=796630)

SQL> select /*+RULE*/*
2from AR_CA ac,AR_REAM
3where AM.CASH_ID=AC.CASH_ID;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=HINT: RULE
10 NESTED LOOPS
21 TABLE ACCESS (FULL) OF 'AR_RE'
31 TABLE ACCESS (BY INDEX ROWID) OF 'AR_CA'
43 INDEX (UNIQUE SCAN) OF 'AR_CA_U1' (UNIQUE)
为什么做了索引统计分析，CBO反而不用索引呢？
但是在另外的两张表中我做了同样的工作，不论用什么方式(删掉统计信息，用hits等）均采用全表扫描，而采用索引查询。
搞不懂CBO到底怎么做的？请高手能解答一下，谢谢。

千问 · 2005-10-30 17:05:33

CBO不用索引并不奇怪
如果使用索引的成本高，Oracle就可能放弃索引.
在HASH JOIN下，Oracle倾向于使用全表扫描或全索引扫描

千问 · 2005-10-30 17:05:33

但是这样访问表变得非常慢，可是如果我把索引统计去掉数据马上就出来了，有什么办法又有索引统计，但是又不用全表扫描呢（因为做了一个job做整个的schema的分析）？如果把某些表挑出来，太麻烦了。还有就是语句已经不可以更改了（就是说不可以加hits）。

千问 · 2005-10-30 17:05:33

最初由 eygle 发布
[B]CBO不用索引并不奇怪
如果使用索引的成本高，Oracle就可能放弃索引.
在HASH JOIN下，Oracle倾向于使用全表扫描或全索引扫描 [/B]
可以详细说说HASH JOIN等和RANGE SCAN等的工作原理吗？为什么HASH JOIN更倾向于全表扫描？

千问 · 2005-10-30 17:05:33

我注意到你使用的
analyze index ar.AR_RE_N1 estimate statistics;
你的两个表有多少数据?estimate statistics 默认分析1064 rows examples. 但是表AR_RE中有78万条数据,所以这个估计分析是不准确的.
尝试对表都进行计算分析，看看结果：
analyze table AR_RE compute statistics
for table
for all indexes
for all indexed columns;
analyze table AR_CA compute statistics
for table
for all indexes
for all indexed columns;

千问 · 2005-10-30 17:05:33

先谢谢两位。但是我试过了grassbell的方法，做了精确分析，但是结果还是一样：CBO用了全表扫描。AR_RE有78万行，AR_CA有109万行记录。按理这是很小的表呀。可是，用全表扫描的话用接近300秒才出结果，用hints/*+FIRST_ROWS*/是0.24秒。

千问 · 2005-10-30 17:05:33

最初由 jowvid 发布
[B]先谢谢两位。但是我试过了grassbell的方法，做了精确分析，但是结果还是一样：CBO用了全表扫描。AR_RE有78万行，AR_CA有109万行记录。按理这是很小的表呀。可是，用全表扫描的话用接近300秒才出结果，用hints/*+FIRST_ROWS*/是0.24秒。 [/B]
再次
SHow 一下你的SQL执行计划
???

千问 · 2005-10-30 17:05:33

看看eygle的这个帖子，
可能对你有些帮助。
http://www.itpub.net/showthread. ... B%B4%CE%B7%D6%CE%F6

千问 · 2005-10-30 17:05:33

CBO不一定永远都会选择最优的执行计划，某些时候CBO还不如RBO好用。