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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 54 is less than 4 for CBO ?

RE: 54 is less than 4 for CBO ?

From: Laimutis Nedzinskas <Laimutis.Nedzinskas_at_landsbanki.is>
Date: Mon, 22 May 2006 13:15:37 -0000
Message-ID: <5A8896FB2AFC5445A7DCFC5903CCA6B02CA219@W03856.li01r1d.lais.net>


How many rows Oracle is considering for the first_rows optimizing goal? 30%?

Then 54/4 = 12 times penalty can be understandable.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Milen Kulev Sent: 22. ma 2006 10:04
To: oracle-l_at_freelists.org
Subject: 54 is less than 4 for CBO ?

Hi listers,
I have the following problem. When I run a SQL statement (see below) without hint , I am getting pretty bad performance. When I use FIRST_ROWS hint, I am getting a totally diffrent (and muuuuuch better) execution plan and performance. DB version is 9.2.0.6.0
OS is AIX 5.2

And now the details (sorry for the lenghty mail, but I want to give all the details at once and in the very beginning of the thread).

SQL Statement without hint:


explain plan for
SELECT t_fzg_sa.fahrgestellnr_7, t_fzg_sa.vertriebsschluessel,

       t_fzg_sa.historien_zaehler, t_fzg_sa.fahrzeugart, 
       t_fzg_sa.fzg_produktionsdatum, t_fzg_sa.sa_bestelltyp, 
       t_fzg_sa.letzte_aenderung, t_fzg_sa.aenderungsart FROM DWH.t_fzg_sa, VDWH_LOAD.tt_vdwh_fgnr WHERE t_fzg_sa.fahrgestellnr_7 = tt_vdwh_fgnr.fahrgestellnr_7 ; select * from table (dbms_xplan.display);

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

| Id | Operation | Name | Rows | Bytes | Cost |
Pstart| Pstop
|


| 0 | SELECT STATEMENT | | 916K| 39M| 32100 |
 |       |

| 1 | NESTED LOOPS | | 916K| 39M| 32100 |
| |
| 2 | PARTITION RANGE ALL| | | | |
1 | 14 |
| 3 | TABLE ACCESS FULL | T_FZG_SA | 303M| 10G| 32100 |
1 | 14 |
|* 4 | INDEX UNIQUE SCAN | SYS_C00146578 | 1 | 8 | |
|

|

Predicate Information (identified by operation id):


   4 - access("T_FZG_SA"."FAHRGESTELLNR_7"="TT_VDWH_FGNR"."FAHRGESTELLNR_7")

SQL Statement with FIRST_ROWS hint:



explain plan for
SELECT /*+ first_rows */ t_fzg_sa.fahrgestellnr_7, t_fzg_sa.vertriebsschluessel,
       t_fzg_sa.historien_zaehler, t_fzg_sa.fahrzeugart, 
       t_fzg_sa.fzg_produktionsdatum, t_fzg_sa.sa_bestelltyp, 
       t_fzg_sa.letzte_aenderung, t_fzg_sa.aenderungsart FROM DWH.t_fzg_sa, VDWH_LOAD.tt_vdwh_fgnr WHERE t_fzg_sa.fahrgestellnr_7 = tt_vdwh_fgnr.fahrgestellnr_7 ; select * from table (dbms_xplan.display);


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

| Id | Operation | Name | Rows | Bytes
| Cost | Pstart| Pstop
|


| 0 | SELECT STATEMENT | | 916K|
39M|   320K|       |       |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_FZG_SA | 29 | 1073
| 12 | ROWID | ROW L |
| 2 | NESTED LOOPS | | 916K|
39M| 320K| | |
| 3 | INDEX FULL SCAN | SYS_C00146578 | 31768 |
248K| 54 | | |
|* 4 | INDEX RANGE SCAN | PK_T_FZG_SA | 29 |
| 3 | |
|

Predicate Information (identified by operation id):


   4 - access("T_FZG_SA"."FAHRGESTELLNR_7"="TT_VDWH_FGNR"."FAHRGESTELLNR_7")

Note: cpu costing is off

Table stats (from 10053 trace file):


##########################################
 TABLE STATS
##########################################
Table stats Table: T_FZG_SA Alias: T_FZG_SA   (Using composite stats)
  TOTAL :: CDN: 303367558 NBLKS: 831184 AVG_ROW_LEN: 37 -- Index stats
  INDEX NAME: I02_T_FZG_SA COL#: 4 2 3
    USING COMPOSITE STATS
    TOTAL :: LVLS: 2 #LB: 28945 #DK: 535 LB/K: 54 DB/K: 115 CLUF: 61824
  INDEX NAME: I03_T_FZG_SA COL#: 7
    USING COMPOSITE STATS
    TOTAL :: LVLS: 2 #LB: 374004 #DK: 1279 LB/K: 292 DB/K: 940 CLUF: 1203394
  INDEX NAME: PK_T_FZG_SA COL#: 1 2
    TOTAL :: LVLS: 3 #LB: 901665 #DK: 310351221 LB/K: 1 DB/K: 1 CLUF: 86903490
_OPTIMIZER_PERCENT_PARALLEL = 0 Table stats Table: TT_VDWH_FGNR Alias: TT_VDWH_FGNR   TOTAL :: CDN: 31768 NBLKS: 31 AVG_ROW_LEN: 8
Column: FAHRGESTEL  Col#: 1      Table: TT_VDWH_FGNR   Alias: TT_VDWH_FGNR
    NDV: 31768     NULLS: 0         DENS: 3.1478e-05
    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
  INDEX NAME: SYS_C00146578 COL#: 1
    TOTAL :: LVLS: 1 #LB: 53 #DK: 31768 LB/K: 1 DB/K: 1 CLUF: 2317 SINGLE TABLE ACCESS PATH
  TABLE: TT_VDWH_FGNR ORIG CDN: 31768 ROUNDED CDN: 31768 CMPTD CDN: 31768
  Access path: tsc Resc: 3 Resp: 3
  Access path: index (iff)

      Index: SYS_C00146578
  TABLE: TT_VDWH_FGNR
      RSC_CPU: 0 RSC_IO: 4
  IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00   Access path: iff Resc: 4 Resp: 4
  Access path: index (no sta/stp keys)

      Index: SYS_C00146578
  TABLE: TT_VDWH_FGNR
      RSC_CPU: 0 RSC_IO: 54
  IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00   BEST_CST: 3.00 PATH: 2 Degree: 1

join info:


################################################
 JOINS
################################################

  Best NL cost: 381219 resp: 381219
Join cardinality: 916480 = outer (31768) * inner (303367558) * sel (9.5096e-08) [flag=0]

...

Best NL cost: 32100 resp: 32100
Join cardinality: 916480 = outer (303367558) * inner (31768) * sel (9.5096e-08) [flag=0]
...

And now the fun part. When I issue the SQL statement woth FIRST_ROWS hint:

explain plan for
SELECT /*+ first_rows */ t_fzg_sa.fahrgestellnr_7, t_fzg_sa.vertriebsschluessel,

       t_fzg_sa.historien_zaehler, t_fzg_sa.fahrzeugart, 
       t_fzg_sa.fzg_produktionsdatum, t_fzg_sa.sa_bestelltyp, 
       t_fzg_sa.letzte_aenderung, t_fzg_sa.aenderungsart FROM DWH.t_fzg_sa, VDWH_LOAD.tt_vdwh_fgnr WHERE t_fzg_sa.fahrgestellnr_7 = tt_vdwh_fgnr.fahrgestellnr_7 ; select * from table (dbms_xplan.display);


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

| Id | Operation | Name | Rows | Bytes
| Cost | Pstart| Pstop
|


| 0 | SELECT STATEMENT | | 916K|
39M|   320K|       |       |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_FZG_SA | 29 | 1073
| 12 | ROWID | ROW L |
| 2 | NESTED LOOPS | | 916K|
39M| 320K| | |
| 3 | INDEX FULL SCAN | SYS_C00146578 | 31768 |
248K| 54 | | |
|* 4 | INDEX RANGE SCAN | PK_T_FZG_SA | 29 |
| 3 | |
|

I am seeing the following in the 10053 event trace file :

  1. Tables stats are same as above
  2. Access costs and paths are very different (the lines marked with <-----!!!)

Access paths



SINGLE TABLE ACCESS PATH
  TABLE: TT_VDWH_FGNR ORIG CDN: 31768 ROUNDED CDN: 31768 CMPTD CDN: 31768
  Access path: tsc Resc: 3 Resp: 3
  Access path: index (iff)

      Index: SYS_C00146578
  TABLE: TT_VDWH_FGNR
      RSC_CPU: 0 RSC_IO: 4 <-----!!!
  IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00   Access path: iff Resc: 4 Resp: 4
  Access path: index (no sta/stp keys)

      Index: SYS_C00146578
  TABLE: TT_VDWH_FGNR
      RSC_CPU: 0 RSC_IO: 54 <------!!!!!   IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00   BEST_CST: 54.00 PATH: 4 Degree: 1 ?!!!! 54 < 4 ?! only because of FIRST_ROWS hint?      

---join1

Join order[1]:  TT_VDWH_FGNR[TT_VDWH_FGNR]#0  T_FZG_SA[T_FZG_SA]#1 Now joining: T_FZG_SA[T_FZG_SA]#1 ******* NL Join
  Outer table: cost: 54  cdn: 31768  rcz: 8  resp:  54  <---! the outer table is more expensive than without FR hint
  Inner table: T_FZG_SA

    Access path: tsc Resc: 32100
    Join: Resc: 1019752854 Resp: 1019752854   Access path: index (scan)

      Index: PK_T_FZG_SA
  TABLE: T_FZG_SA
      RSC_CPU: 0 RSC_IO: 12 <----!!! Suddenly CBO "sees" PK_T_FZG_SA ?!
  IX_SEL: 9.5096e-08 TB_SEL: 9.5096e-08     Join (ordered NL): resc: 320819 resp: 320819 <---- !!! Shortcut for index join. The cost is high, though ;(

Joins:



---join1
Join order[1]:  TT_VDWH_FGNR[TT_VDWH_FGNR]#0  T_FZG_SA[T_FZG_SA]#1 Now joining: T_FZG_SA[T_FZG_SA]#1 ******* NL Join
  Outer table: cost: 54  cdn: 31768  rcz: 8  resp:  54  <---! the outer table is more expensive than without FR hint
  Inner table: T_FZG_SA

    Access path: tsc Resc: 32100
    Join: Resc: 1019752854 Resp: 1019752854   Access path: index (scan)

      Index: PK_T_FZG_SA
  TABLE: T_FZG_SA
      RSC_CPU: 0 RSC_IO: 12 <----!!! Suddenly CBO "sees" PK_T_FZG_SA ?!
  IX_SEL: 9.5096e-08 TB_SEL: 9.5096e-08     Join (ordered NL): resc: 320819 resp: 320819 <---- !!! Shortcut for index join.

    																														The cost is high, though ;(


Join order[2]:  T_FZG_SA[T_FZG_SA]#1  TT_VDWH_FGNR[TT_VDWH_FGNR]#0 Now joining: TT_VDWH_FGNR[TT_VDWH_FGNR]#0 ******* NL Join
  Outer table: cost: 1660983  cdn: 303367558  rcz: 37  resp:  1660983
  Inner table: TT_VDWH_FGNR

    Access path: tsc Resc: 3
    Join: Resc: 911763657 Resp: 911763657 <----!Monster cost

And now the question:



Why the CBO is suddenly thinking that the access on TT_VDWH_FGNR table over  SYS_C00146578 index is better, when the costs are telling me a totally differnet story ?

FTS on TT_VDWH_FGNR -> Cost = 4
Index access on SYS_C00146578 -> Cost 54

BEST_CST: 54.00 PATH: 4 Degree: 1 <------!!! index wins!

The fact that CBO with FIRST_ROWS is favouring NL access path (effectively ignoring HASH and MEGRE join paths) is undestandable. The fact that CBO is opening new access paths with NL access (index only), with the idea of table prefetching , is also imaginable for me.

But how is is possible to ignore the fact that cost = 54 (index acces) is less than cost = 4 ? Or there are some peculiarities hard-coded in the logic of CBO that are roughly saying : "If you have FIRST_ROWS as hint or optimizer_mode then use the index at (almost) any cost".

Any comments/suggestions/explanations are highly appreciated.

Best Regrads. Milen

--
GMX Produkte empfehlen und ganz einfach Geld verdienen!
Satte Provisionen fr GMX Partner: http://www.gmx.net/de/go/partner
--
http://www.freelists.org/webpage/oracle-l


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 22 2006 - 08:15:37 CDT

Original text of this message

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