Re: Re: SQL performance in prod

Andy,
I agree to some extend.
We know:
the two plans are close in cost, otherwise the plan would not flip back and forth in reality the two plans are have a very different run time, therefore the both cost estimate must be incorrect since the cost estimate must the wrong, the cardinality is likely wrong too Therefore cardinality feedback will lead to right at source of the problem Conclusion: all you need are the actual rows e.g. from runtime stats or from sql monitor No witch doctors, no guesses, just facts Thanks
Lothar
----Ursprüngliche Nachricht----

Von : andysayer_at_gmail.com
Datum : 19/07/2018 - 09:27 (GMT)
An : oracle-l_at_freelists.org
Betreff : Re: SQL performance in prod
You’ve had a lot of suggestions, but personally I believe they’re either over kill or guess work they’ll just waste plenty of your time. I would just do it very simply:
Compare the two plans (by eye)
Find the pivotol line of the plan that “changes everything” - does like 13 have a tiny cardinality in a But not b? (Etc) Identify what went into that decision
Find out how that input changed between a and b (different peeked bind, different stat etc) I don’t see any need to dig into traces, look into system stats, check for Witch doctors nearby etc. If you shared the two complete plans, then I’m sure it wouldn’t take long to just point out where the issue is likely coming from and make some suggestions. Make sure you include the predicate section, peeked binds and notes - they are all packed full of important information Hope this helps,
Andrew

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 19 2018 - 09:50:06 CEST