RE: Really strange performance issue
Date: Thu, 6 Nov 2014 05:13:04 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E5BEB3_at_exmbx06.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Jonathan Lewis [jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>] 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 06 2014 - 06:13:04 CET