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

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

54 is less than 4 for CBO ?

From: Milen Kulev <makulev_at_gmx.net>
Date: Mon, 22 May 2006 12:03:59 +0200 (MEST)
Message-ID: <10973.1148292239@www020.gmx.net> 





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 für GMX Partner: http://www.gmx.net/de/go/partner
--
http://www.freelists.org/webpage/oracle-l


Received on Mon May 22 2006 - 05:03:59 CDT












Original text of this message













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