RE: Need some 10053 Guidance to help me solve a puzzler
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <niall.litchfield@xxxxxxxxx>
- Date: Thu, 11 Oct 2012 13:24:34 -0500
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@xxxxxxxxx]
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@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto: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<mailto: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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
