Why CBO doesn't choose the low cost plan

From: eagle fan <eagle.f_at_gmail.com>
Date: Wed, 19 Jul 2006 16:14:51 +0800
Message-ID: <4415a5ed0607190114i54991378tc86a0eb5c081a4af@mail.gmail.com>

hi:

My oracle version is 9205 and optimizer_features_enable is set to 9.2.0.

We have a query choose a bad plan.

The query is like this:

select /*+ index(e) */
max(event_id), sysdate from ANONYMOUS e where event_type = :1 and event_id between :2 and :3 and creation_date < sysdate - :4 /

(I substitute the real table name with ANONYMOUS because of the policy)

The table has two indexes can be used for this query:

           Index Name                      Column Name
 -------------------------------                   -------------------------
---
 ANONYMOUS_CE_IDX                CREATION_DATE               1

EVENT_TYPE                  2
 ANONYMOUS_EEC_IDX              EVENT_TYPE                  1

EVENT_ID                    2

CREATION_DATE               3

The query only returns one row, and obviously the second index ANONYMOUS_EEC_IDX is more effecient.

But CBO chooses the first index and do a huge index range scan and table block scan.

First I think maybe the statistics is not correct, so CBO choose the wrong index.

But actually, CBO knows that using the second index will have the lower cost.

SQL> explain plan for select /*+ index(e,ANONYMOUS_EEC_IDX,ANONYMOUS_CE_IDX) */
2 max(event_id), sysdate from ANONYMOUS e where 3 event_type = 'xxxxxxxx' and event_id between 3059913 and 3059914 4 and creation_date < sysdate - 1/100;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

| Id  | Operation                           |  Name                   |

|   0 | SELECT STATEMENT                    |                         |
1 |    39 |  2771 |       |       |
|   1 |  SORT AGGREGATE                     |                         |
1 |    39 |       |       |       |
|   2 |   PARTITION RANGE ALL               |
|       |       |       |     1 |    13 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| ANONYMOUS         |     1 |
39 |  2771 |     1 |    13 |
|*  4 |     INDEX RANGE SCAN                | ANONYMOUS_CE_IDX  |
119K|       |  1412 |     1 |    13 |


---------------------------------------------------------------------------------------------------------------

SQL> explain plan for select /*+ index(e,ANONYMOUS_EEC_IDX) */ 2 max(event_id), sysdate from ANONYMOUS e where 3 event_type = 'USER.BILLING_INFO.UPDATE' and event_id between 3059913 and 3059914
4 and creation_date < sysdate - 1/100;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

| Id  | Operation            |  Name                    | Rows  | Bytes |

Cost | Pstart| Pstop |

|   0 | SELECT STATEMENT     |                          |     1 |    39 |
40 |       |       |
|   1 |  SORT AGGREGATE      |                          |     1 |    39
|       |       |       |
|   2 |   PARTITION RANGE ALL|                          |       |
|       |     1 |    13 |
|*  3 |    INDEX RANGE SCAN  | ANONYMOUS_EEC_IDX  |     1 |    39 |    40
|     1 |    13 |


-------------------------------------------------------------------------------------------------

From plan, the cost of using second index is only 40 and the cost of using first index is 2771. But CBO chooses the first one!! Why?

I did a 10053 trace on this sql and still can't understand the CBO behavior

BASE STATISTICAL INFORMATION

Table stats Table: ANONYMOUS Alias: E   (Using composite stats)
  TOTAL :: CDN: 13276900 NBLKS: 612378 AVG_ROW_LEN: 156 -- Index stats
  INDEX NAME: ANONYMOUS_CE_IDX COL#: 5 2     USING COMPOSITE STATS
    TOTAL :: LVLS: 3 #LB: 152450 #DK: 207428 LB/K: 1 DB/K: 5 CLUF: 1056950
  INDEX NAME: ANONYMOUS_EEC_IDX COL#: 2 1 5     USING COMPOSITE STATS
    TOTAL :: LVLS: 3 #LB: 181250 #DK: 13035150 LB/K: 1 DB/K: 1 CLUF: 1647350
  INDEX NAME: ANONYMOUS_PK COL#: 2 1 3
    USING COMPOSITE STATS
    TOTAL :: LVLS: 3 #LB: 167950 #DK: 13351700 LB/K: 1 DB/K: 1 CLUF: 1665200
  INDEX NAME: ANONYMOUS_R_IDX COL#: 7
    USING COMPOSITE STATS
    TOTAL :: LVLS: 2 #LB: 84700 #DK: 201870 LB/K: 1 DB/K: 46 CLUF: 9445950
_OPTIMIZER_PERCENT_PARALLEL = 0

SINGLE TABLE ACCESS PATH

Column: EVENT_TYPE  Col#: 2      Table: ANONYMOUS   Alias:  E
    NDV: 7         NULLS: 0         DENS: 0.0000e+00
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:   EVENT_ID  Col#: 1      Table: ANONYMOUS   Alias:  E
    NDV: 7429352   NULLS: 0         DENS: 0.0000e+00 LO:  91465  HI:

11861688

NO HISTOGRAM: #BKT: 1 #VAL: 2

Column: CREATION_D  Col#: 5      Table: ANONYMOUS   Alias:  E
    NDV: 604325    NULLS: 0         DENS: 0.0000e+00 LO:  2453787  HI:

2453878

NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: ANONYMOUS ORIG CDN: 13276900 ROUNDED CDN: 1 CMPTD CDN: 0 Access path: index (scan)

      Index: ANONYMOUS_CE_IDX
  TABLE: ANONYMOUS
      RSC_CPU: 0 RSC_IO: 2771
  IX_SEL: 9.0000e-03 TB_SEL: 1.2857e-03   Access path: index (index-only)

      Index: ANONYMOUS_EEC_IDX
  TABLE: ANONYMOUS
      RSC_CPU: 0 RSC_IO: 40
  IX_SEL: 1.2137e-08 TB_SEL: 1.0923e-10

  BEST_CST: 2771.00  PATH: 4  Degree:  1                 -- why CBO chooses

the high cost plan here?

OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Join order[1]: ANONYMOUS[E]#0
Best so far: TABLE#: 0 CST: 2771 CDN: 1 BYTES: 39 prefetching is on for ANONYMOUS_CE_IDX
Final - All Rows Plan:
JOIN ORDER: 1
CST: 2771 CDN: 1 RSC: 2771 RSP: 2771 BYTES: 39 IO-RSC: 2771 IO-RSP: 2771 CPU-RSC: 0 CPU-RSP: 0 *** 2006-07-19 00:44:40.994

What do you think?

-- 
Eagle Fan

Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 19 2006 - 03:14:51 CDT