RE: Need some 10053 Guidance to help me solve a puzzler
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Oct 2012 12:36:30 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Thursday, October 11, 2012 12:26 PM
To: oracle-l@xxxxxxxxxxxxx
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 -----
--
//www.freelists.org/webpage/oracle-l
Other related posts:
