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 -> Re: Why CBO choose the wrong plan?

Re: Why CBO choose the wrong plan?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 30 Jun 2007 19:25:07 -0700
Message-ID: <1183256707.752638.287380@u2g2000hsc.googlegroups.com>


On Jun 30, 9:56 am, wblxx <wangbin..._at_gmail.com> 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

The database version is 10.2.0.2 - how did you disable CPU costing (experimenting with hidden parameters such as _OPTIMIZER_COST_MODEL)? Oracle 9i will use CPU costing when the system statistics exist, but 10g will always use CPU costing, synthesizing statistics if none are in place.

Let's assume that MBRC from the SYS.AUX_STATS$ view does not show a value of 128 or higher, and OPTIMIZER_INDEX_COST_ADJ is not set to 500 or higher. A guess would be that the clustering factor of the index is so high (708,705 out of a possible maximum of 1,725,602 and a possible minimum of 5,438) - that Oracle believes that the index access would be very expensive in order to perform an index range scan and then retrieve the table rows - assuming each read is a physical read.

To examine the decisions made by the cost based optimizer, you will need to examine a 10053 trace file for the SQL statement. 10053 trace files can be hard to read at first, so a book, such as the excellent "Cost-Based Oracle Fundamentals", will be very helpful.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jun 30 2007 - 21:25:07 CDT

Original text of this message

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