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@(protected)
Sent: 22. ma? 2006 10:04
To: oracle-l@(protected)
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
