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