Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Why CBO choose the wrong plan?

Why CBO choose the wrong plan?

From: wblxx <wangbinlxx_at_gmail.com>
Date: Sat, 30 Jun 2007 06:56:25 -0700
Message-ID: <1183211785.803130.64450@x35g2000prf.googlegroups.com>


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 Received on Sat Jun 30 2007 - 08:56:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US