RE: Need some 10053 Guidance to help me solve a puzzler
Date: Thu, 11 Oct 2012 12:36:30 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851607C08B_at_NADCWPMSGCMS10.hca.corpad.net>
Ok, so now I'm confused a bit and I think I'm probably several mental steps behind you on this one so bear with me:
First:
db_file_multiblock_read_count=16
Systems Stats have been gathered (always one of the first things I do when taking over a system)
Second:
I ran the non-indexes query multiple times - if the table was nearly completely cached, wouldn't the access times improve on the non-indexed query as well? (There's probably some other considerations here that I'm missing/not thinking about)
Third:
Why doesn't Oracle choose the Index and Drive the NL to get the 159K rows on its own?
Regards,
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, October 11, 2012 12:26 PM
To: oracle-l_at_freelists.org
Subject: Re: Need some 10053 Guidance to help me solve a puzzler
You don't need to look at the 10053 to answer your question - the answer you need is in the execution plan.
The indexed access path shows Oracle estimating 159K rows at line 3
(driving the NL).
This gives an index access cost of 2 for each row (branch + leaf) plus two
more for the table (2 random rows for each driving row). 159K * 4 = 636K,
which the CBO treats as assumed random disk reads (there's a fairly obvious
algorithm error there relating to index caching, and a less obvious defect
relating to table caching).
The table scan path shows a cost of 165K which (combined with the reads of 682K) make me think the table is probably about 682K blocks and largely uncached, and that your system stats and db_file_multiblock_read_count are left at default.
The dramatic difference in cost between the 165K and the 636K is enough to overwhelm any other factor in the costing; and the fact that the table is nearly completely cached for the indexed access path gives you the vastly better time compared to the estimate.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- -- http://www.freelists.org/webpage/oracle-l