Home -> Mailing Lists -> Oracle-L -> RE: Need some 10053 Guidance to help me solve a puzzler

RE: Need some 10053 Guidance to help me solve a puzzler

From: <Christopher.Taylor2_at_parallon.net>
Date: Thu, 11 Oct 2012 13:24:34 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851607C1F8_at_NADCWPMSGCMS10.hca.corpad.net> 





Well I just gathered 100% stats on this table and indexes last night with method_opt=>'FOR ALL COLUMNS SIZE AUTO'
This table has 0 predicates as far as the xplan indicates:



The query joins to this table, but apparently those don't apply as predicates?  (If I understand correctly that FILTERS are the same as PREDICATES?)



The only join to this table is on:


MAPCS.MON_ACCT_PAYER_CALC_SUMMARY_ID and that column has:



9920697 (current count)


compared to


9820620 (listed in 10053)



Chris




From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com]
Sent: Thursday, October 11, 2012 1:06 PM
To: Taylor Christopher - Nashville


Cc: Jonathan Lewis; ORACLE-L


Subject: RE: Need some 10053 Guidance to help me solve a puzzler




Chris,



Can you run some count(distinct) queries on the predicates used in the problem section Jonathan describes and compare to num_distinct, prior to 11 the second most common cause of poor estimates for the cbo is the NDV stat. Often its accurate enough but sometimes its way, way, way off. If this does turn out to be your issue you can always (oracle answer) upgrade to a current release or (more immediate) set the column stats.
On Oct 11, 2012 6:38 PM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote:
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


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> [mailto: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<mailto: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






--



http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 11 2012 - 20:24:34 CEST












Original text of this message