Re: Learning more about and reading 10053 trace files

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Tue, 5 Sep 2006 14:16:36 -0500
Message-ID: <7b8774110609051216p6efd705fh1ddcfbff56a2b72a@mail.gmail.com>

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 05 2006 - 14:16:36 CDT