Re: Learning more about and reading 10053 trace files
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Sep 2006 14:16:36 -0500
To make things a little more interesting, I incorporated some ideas from
Breitling's "Tuning by Cardinality Feedback." Here is why I was hung up
about the "First K Rows" part - as you can see, the estimates are horrid for
the query without the distinct - I have to question where such a drastically
low cost came from:
DECODE(A.SQL ID OPERATION OPTIONS OBJECT_NAME ACTUAL_ROWS
ESTIMATED_ROWS
------------ -- ------------ --------------- ------------------ -----------
--------------
DISTINCT 1 HASH UNIQUE
99217 403084
DISTINCT 2 HASH JOIN
4766142 403084
DISTINCT 3 INDEX FULL SCAN PK_TABLE_A
96 96
DISTINCT 4 HASH JOIN
4812046 406680
DISTINCT 5 HASH JOIN
355265 52851
DISTINCT 6 INDEX FAST FULL SCAN TABLE_D_VIEW_INDEX
1118359 22433
DISTINCT 7 INDEX FAST FULL SCAN TABLE_C_KEY2_INDEX
231499 231436
DISTINCT 8 TABLE ACCESS FULL TABLE_B
763001 755874
DECODE(A.SQL ID OPERATION OPTIONS OBJECT_NAME ACTUAL_ROWS
ESTIMATED_ROWS
------------ -- ------------ --------------- ------------------ -----------
--------------
NON DISTINCT 1 NESTED LOOPS
4766142 10
NON DISTINCT 2 NESTED LOOPS
4812046 11
NON DISTINCT 3 NESTED LOOPS
355265 2
NON DISTINCT 4 INDEX FAST FULL SCAN TABLE_D_KEY_INDEX
1118359 22433
NON DISTINCT 5 INDEX RANGE SCAN TABLE_C_KEY2_INDEX
355265 2
NON DISTINCT 6 TABLE ACCESS BY INDEX ROWID TABLE_B
4812046 6
NON DISTINCT 7 INDEX RANGE SCAN PK_TABLE_B
4812046 6
NON DISTINCT 8 INDEX UNIQUE SCAN PK_TABLE_A
4766142 1
--
Charles Schultz
Other related posts:
