Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Why CBO choose the wrong plan?
Hi
I run the following queries on 10.2.0.2 DB. TBL_ACC_STAT_SUMM is partition on SETTLED_DT, and the index ACC_STAT_ACCOUNT_ID is local index on TBL_ACC_STAT_SUMM.ACCOUNT_ID .
select /*+ INDEX (TBL_FIN_ACC_STAT_SUMM ACC_STAT_ACCOUNT_ID )*/
sum(TBL_FIN_ACC_STAT_SUMM.AMOUNT)
from TBL_ACC_STAT_SUMM TBL_FIN_ACC_STAT_SUMM
where ACCOUNT_ID = 123456 and
TBL_FIN_ACC_STAT_SUMM.SETTLED_DT BETWEEN
to_date('20070601','yyyymmdd') AND to_date('20070602','yyyymmdd');
Elapsed: 00:00:01.81
Execution Plan
|* 4 | INDEX RANGE SCAN | ACC_STAT_ACCOUNT_ID | 1552 | | 1907 (1)| 1557 | 1558 | ----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 -
filter("TBL_FIN_ACC_STAT_SUMM"."SETTLED_DT"<=TO_DATE('2007-06-02
00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
4 - access("ACCOUNT_ID"=123456)
Statistics
1 recursive calls 0 db block gets 16 consistent gets 14 physical reads 0 redo size 538 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
select sum(TBL_FIN_ACC_STAT_SUMM.AMOUNT)
from ODS.TBL_ACC_STAT_SUMM TBL_FIN_ACC_STAT_SUMM
where ACCOUNT_ID = 1234567 and
TBL_FIN_ACC_STAT_SUMM.SETTLED_DT BETWEEN
to_date('20070601','yyyymmdd') AND to_date('20070602','yyyymmdd');
Elapsed: 00:00:54.98
Execution Plan
Predicate Information (identified by operation id):
3 - filter("ACCOUNT_ID"=123456 AND
"TBL_FIN_ACC_STAT_SUMM"."SETTLED_DT"<=TO_DATE('2007-06
-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Statistics
1 recursive calls 0 db block gets 88844 consistent gets 88115 physical reads 0 redo size 538 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Clearly, CBO choose the wrong plan. This is the stats of table and index.
index partition 1557,1558:
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
---------- ----------- ------------- ----------------------- ----------------- ---------- ----------- 2 5438 29746 23 708705 1725602 366479 2 6070 44599 17 782775 1915855 383171
table partition 1557,1558:
NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE ---------- ---------- ----------- -----------
1776880 38155 165 177688 1846835 41980 168 369367
ALL_TAB_COLUMNS and ACCOUNT_ID:
NUM_DISTINCT DENSITY NUM_BUCKETS SAMPLE_SIZE ------------ ---------- ----------- -----------
46301 .000429923 254 172722
I don't understand why CBO calculate 281K for index cost and only 17607 for full table scan. BTW, cpu costing isn't enable on this DB.
Kind Regards,
Bin
Received on Sat Jun 30 2007 - 08:56:25 CDT