Re: Optimizer issue - cost of full table scans
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: brad_peek@xxxxxxxxx
- Date: Fri, 10 Sep 2010 13:12:48 -0700
Best approach is to search for root cause by using the 5 Whys
(http://en.wikipedia.org/wiki/5_Whys)
1) Performance got worse. Why?
2) Plan changed from HJ to NLJ. Why?
3) Optimizer costs a NLJ to be cheaper than a HJ. Why?
4) The stats on the table used as input for the costing model caused
the drop in costing. Why?
5) Something about the table stats has changed. Why?
So, what has changed about the stats and why has it done so? I
suspect if you use the Active SQL Monitor Reports found in dbconsole
for these statements (or you can do it via SQL*Plus and the package
DBMS_SQLTUNE.REPORT_SQL_MONITOR) you will want to look at any
significant differences between "Estimated Rows" and "Actual Rows". I
suspect therein lies the issue.
If you get the stats to be representative I suspect that you should
solve your issue.
On Fri, Sep 10, 2010 at 12:06 PM, Brad Peek <brad_peek@xxxxxxxxx> wrote:
> Listers – Is there a way to “inform” the optimizer of the true cost of the
> full scans? I think there is, and I fully intend to RTFM, but I’m hoping
> for a quick answer….
>
> Background:
>
> Our company has been in production on an Exadata V2 quarter-rack (SATA
> drives) for several months now and have been generally pleased with
> performance. However, over the past few days we have had a number of
> queries that switched from hash joins to nested loop joins and the
> performance of these queries is MUCH worse.
>
> I’ve just started looking at this, but I have begun to wonder if part of the
> problem is that the optimizer is over-estimating the cost of full table
> scans. Right from the beginning (i.e. before this current problem) I
> noticed that the time estimates in the execution plans were significantly
> higher than our actual response times. My theory is that as our tables
> have grown we have reached a tipping point that has now caused the optimizer
> to favor nested loops, and that the inflated cost of the full scans is a
> contributing factor.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
