Re: Why CBO choose the wrong plan?

wblxx wrote:
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
----------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 18 | 281K (1)| | |
| 1 | SORT AGGREGATE |
| 1 | 18 | | | |
| 2 | PARTITION RANGE ITERATOR | |
1146 | 20628 | 281K (1)| 1557 | 1558 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TBL_ACC_STAT_SUMM |
1146 | 20628 | 281K (1)| 1557 | 1558 |
|* 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
----------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18
| 17607 (1)| | |
| 1 | SORT AGGREGATE | | 1 | 18
| | | |
| 2 | PARTITION RANGE ITERATOR| | 1146 | 20628
| 17607 (1)| 1557 | 1558 |
|* 3 | TABLE ACCESS FULL | TBL_ACC_STAT_SUMM | 1146 | 20628
| 17607 (1)| 1557 | 1558 |
----------------------------------------------------------------------------------------------------

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

An excellent time to pick up a copy of Jonathan Lewis's
book: "Cost-Based Oracle Fundamentals." <g>
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages