RE: 54 is less than 4 for CBO ?

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Milen Kulev
Sent: 22. maí 2006 10:04
To: oracle-l@xxxxxxxxxxxxx
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
################################################

-- 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: 3  cdn: 31768  rcz: 8  resp:  3
  Inner table: T_FZG_SA
    Access path: tsc  Resc: 32100
   Join:  resc: 920414267  resp: 920414267

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

...


--- join2
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: 32100  cdn: 303367558  rcz: 37  resp:  32100
  Inner table: TT_VDWH_FGNR
    Access path: tsc  Resc: 3

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 ;(


-- join2 

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 für 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


Other related posts: