RE: Really strange performance issue
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 Nov 2014 05:13:04 +0000
Couldn't resist the temptation to produce a simple example of how CF exhibit
the type of behaviour you've seen:
http://jonathanlewis.wordpress.com/2014/11/05/cardinality-feedback-2
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Jonathan Lewis
[jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>]
Sent: 31 October 2014 08:57
To: Andrew Kerber; Mark W. Farnham
Cc: Howard Latham; oracle-l
Subject: RE: Really strange performance issue
Looking at the code, I think Sayan's comment is the relevant one.
Waving my hands and guessing WILDLY - but I suspect I could create a data set
where this happens:
The optimizer is probably handling your subqueries as "unknown constant", which
gives you a range scan on unknown values which gives the optimizer a guess of
0.25% - hence the application of cardinality feedback.
On the first pass the optimizer drives off the created date - and discovers
that it does a lot more work than expected (more rows), so on the second pass
it reverses the join, which turns out to be a bad idea because the optimizer's
estimated cardinality of '88888' (which doesn't get modified by the first pass)
is badly wrong and/or the chosen access path back into wdata is much less
efficient than expected.
Other related posts:
