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 -- //www.freelists.org/webpage/oracle-l Fyrirvari/Disclaimer http://www.landsbanki.is/disclaimer -- //www.freelists.org/webpage/oracle-l