April 7, 2011 3 Comments
Part I – What is “The Correct Plan”?
How many SQL statements are genuinely straightforward enough that you can look at it and instantly say what “The Correct Plan” is?
When you make this judgement on “The Correct Plan”, do you take into account index clustering factors, single and multiblock read times, etc, etc? Should you?
What about new features, new access paths, new join methods, does “The Correct Plan” take these into account?
Or perhaps you think it is independent of them!?!?
What about if you’ve got a plan that you’re happy with on production? Is that “The Correct Plan”?
So maybe this is just semantics?
What people mean when they say “The Correct Plan” and “The Wrong Plan/s” is probably “An Acceptable Plan” and “An Unacceptable Plan”.
Maybe I need to get over the terminology, move on.
There might be something better, there might be something worse, but this plan is what they’re happy with at the moment.
This plan is acceptable.
Part II – “Hi, my name is Bob and I’m addicted to hinting.”
Why am I going on about this?
It’s a follow-up to my previous post on some frustrations on a SQL Tuning gig.
One of the reasons I blog is that it has carthartic properties. But it’s not carthartic enough – I can’t stop going on and on about the same stuff which drives me to distraction day in, day out.
I’m in a team that is addicted to hinting.
Developers addicted to hinting.
Managers addicted to developers hinting.
In fact, I can’t recall being somewhere with a greater affliction of hinting addiction.
And they are insistent on me hinting, all day, every day.
I’ve tried but they won’t give it up. They’ve got to want to give it up.
It is “The Silver Bullet” and “The Comfort Blanket”, whilst in reality being neither.
And hinting begets more hinting. See Rules for Hinting.
For a big statement, have you ever tried hinting it manually to get “The Correct Plan” consistently? There might be shortcuts by nicking the full set of hints from an outline or a profile or other_xml depending on version, but it’s hideous, it’s long winded, and at some point it’s likely to “go bad”.
I’ve tried to get the team to look at baselines as a way to lock in “The Correct Plan” but they have huge – quite possibly insurmountable (in terms of willingness to surmount them) – FUD about how these will fit into the development lifecycle, the release management process, change control.
The first nail in the coffin of getting them to experiment with baselines was a performance bug with recursive merge statements into the sqlobj$* tables. Mentioned that before. Several times.
That now having been fixed, the second nail in the coffin happened today.
There’s a lot of Pro*C code. I managed to get them to compile some of it with common_parser=yes so that, for the SQL that is inline in the Pro*C and not in a stored proc, we could use some of these new-fangled features that have come out since something like Oracle 8, you know like scalar subqueries, WITH, analytics even LISTAGG for example.
But this has had the nasty side-effect of materially changing some of the SQL statements – particularly around removing unnecessary parentheses around predicates – such that both the force and exact matching signatures of certain SQL statements changed. So, the few statements we were trying with baselines failed to use the baselined plans and “went bad”.
Not necessarily a problem in itself but adding to the insurmountable FUD…
Part III – Ignore the Plan
Sometimes – not all the time, but most of the time – I would go further than that – “Ignore the plan”.
Obviously I don’t really mean “Ignore the plan”, at least not the whole plan.
When, for example, a statement performs acceptably or better than acceptably (which will then instantly become the new “acceptably”) – who cares about the plan at all (until it performs badly).
However, when performance problems set in, maybe there’s a tendency sometimes to get too hung up on whether the starting point should be a nested loop driven by an index range scan of index_1, etc?
A lot of the time, I bet that your idea of “The Correct Plan” is based on heuristics, a set of rules, a bit like the RBO but probably not as effective.
And there’s a reason why those similar heuristics in the RBO have been discarded.
So maybe Ignore the Plan?
Focus on the row estimates, the cardinalities.
If these are accurate, at least nine times out of ten, you’ll get an appropriate plan, “An Acceptable Plan”.
And if they’re not accurate, before looking at anything else, review the SQL.
Is that the best way to express the logic? There’s a good chance it’s not.
In the 9i to 11gR2 upgrade that I’m currently involved with, most of the SQL with the biggest performance problems and which deviate from “The Correct Plan”, can be rewritten, expressed in a slightly different, sometimes better, sometimes more natural, logical, set-based way.
And often, that’s enough.