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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org