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

Home -> Community -> Mailing Lists -> Oracle-L -> wrong expected execution plan

wrong expected execution plan

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Fri, 21 Sep 2007 00:01:39 +0700
Message-ID: <3edcb66e0709201001y3de28d58w1f5547b12106983c@mail.gmail.com>


all,

the COSTEDEVENT table is IOT and
and COSTEDEVENT_PK is IOT primary key index and COSTEDEVENT_UK1 is secondary of IOT index

my question:
why oracle assumption to take COSTEDEVENT_UK1 which is higher clustering factor but lesser in leaf blocks.

i guess oracle will assume that smaller leaf blocks is better than the higher one. from this 10053, we could see that resources for cpu & io is small than highr leaf blocks.

any thought?????

SELECT ......................

  FROM e
 WHERE ................

*******************************************
Peeked values of the binds in SQL statement

 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000 size=96 offset=0

   bfp=ffffffff7ba79f80 bln=22 avl=00 flg=05  bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000 size=0 offset=24

   bfp=ffffffff7ba79f98 bln=22 avl=00 flg=01  bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000 size=0 offset=48

   bfp=ffffffff7ba79fb0 bln=22 avl=00 flg=01  bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000 size=0 offset=72

   bfp=ffffffff7ba79fc8 bln=22 avl=00 flg=01



Column Usage Monitoring is ON: tracking level = 1

QUERY BLOCK SIGNATURE

qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0   fro(0): flg=0 objn=197782 hint_alias="COSTEDEVENT"@"SEL$1"

BASE STATISTICAL INFORMATION

Table stats Table: COSTEDEVENT Alias: COSTEDEVENT   (Using composite stats)
  TOTAL :: CDN: 296160700 NBLKS: 15040307 AVG_ROW_LEN: 261 Index stats
  Index: COSTEDEVENT_PK COL#: 1 2 3 4 5     USING COMPOSITE STATS
    TOTAL :: LVLS: 3 #LB: 15040307 #DK: 361175084 LB/K: 1 DB/K: 1 CLUF: 0   Index: COSTEDEVENT_UK1 COL#: 2 5
    USING COMPOSITE STATS
    TOTAL :: LVLS: 3 #LB: 2961679 #DK: 360905528 LB/K: 1 DB/K: 1  CLUF: 20447015
_OPTIMIZER_PERCENT_PARALLEL = 0

SINGLE TABLE ACCESS PATH
  COLUMN: ACCOUNT_NU(VARCHAR2) Col#: 1 Table: COSTEDEVENT Alias: COSTEDEVENT

    Size: 8 NDV: 47616 Nulls: 0 Density: 2.1001e-05   COLUMN: EVENT_TYPE(NUMBER) Col#: 4 Table: COSTEDEVENT Alias: COSTEDEVENT
    Size: 2 NDV: 18 Nulls: 0 Density: 5.5556e-02 Min: 1 Max: 29   COLUMN: EVENT_ATTR(VARCHAR2) Col#: 23 Table: COSTEDEVENT Alias: COSTEDEVENT

    Size: 14 NDV: 1827 Nulls: 402372 Density: 5.4735e-04   COLUMN: EVENT_SEQ(NUMBER) Col#: 2 Table: COSTEDEVENT Alias: COSTEDEVENT
    Size: 5 NDV: 5 Nulls: 0 Density: 2.0000e-01 Min: 70916001 Max: 71011001   TABLE: COSTEDEVENT Alias: COSTEDEVENT     Original Card: 296160700 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01   Access Path: index (index-ffs)
    Index: COSTEDEVENT_PK
    rsc_cpu: 107108643882 rsc_io: 2604638     ix_sel: 0.0000e+00 ix_sel_with_filters: 1.0000e+00   Access Path: index-ffs Resc: 2616210 Resp: 2616210   Access Path: index (no start/stop keys)     Index: COSTEDEVENT_PK
    rsc_cpu: 107112578517 rsc_io: 15040859     ix_sel: 1.0000e+00 ix_sel_with_filters: 5.8337e-08 OPTIMIZER PERCENT INDEX CACHING = 100
  Access Path: index (index-only)
    Index: COSTEDEVENT_UK1
    rsc_cpu: 1322405301 rsc_io: 0
    ix_sel: 9.0000e-03 ix_sel_with_filters: 9.0000e-03   BEST_CST: 22.43 PATH: 4 Degree: 1



OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Join order[1]: COSTEDEVENT[COSTEDEVENT]#0 Best so far: TABLE#: 0 CST: 22 CDN: 1 BYTES: 261 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 22 CDN: 1 RSC: 22 RSP: 22 BYTES: 261   IO-RSC: 1 IO-RSP: 1 CPU-RSC: 198360795 CPU-RSP: 198360795
-- 
regards
ujang
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 20 2007 - 12:01:39 CDT

Original text of this message

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