“The Correct Plan”

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

A while ago Doug Burns was talking about getting developers to ignore Cost.

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.

SQL Tuning Set to Baseline to Advisor

In my previous post “Advisor to Baseline – am I missing something?”, the answer was an inevitable “Yes”.

Just as a reminder, what I had tried to do was:

  1. Create a Tuning Set from some statements in AWR
  2. Create and execute a Tuning Task based on the Tuning Set
  3. Accept the simple profile recommendations from the Advisor
  4. Create a SQL Plan Baseline from the Tuning Set and with it all the new profiles

What happened was that I ended up with a bunch of standalone profiles from the Advisor – see DBA_SQL_PROFILES and/or SQLOBJ$ of object type 1, looking at hints in COMP_DATA of SQLOBJ$DATA or packing to staging table and inspecting there.

And I ended up with a bunch of SQL Plan Baselines with hints from the old plans for statements that I had run through the Advisor because they were rubbish (DBA_SQL_PLAN_BASELINES and SQLOBJ$ of object type 2, looking at hints in COMP_DATA of SQLOBJ$DATA or packing to staging table and inspecting there.)

Quick question – what happens if you have some SQL Plan Baselines with some “bad” hints whilst there also exist some standalone sql profiles with some “good” hints?

From my observations, the Baselines will win. The bad plans will be used. However, because when baselines are being used, on hard parse the optimizer will generate a plan anyway and record any differences in plans generated. So when generating the plan anyway, the standalone sql profiles kick in and so the baseline will contain unaccepted “better” plans ready to be evolved for subsequent executions (unaccepted depending on whether you’re runing with automatic evolution or not).

And back to what I should have done initially and that’s:

  1. Create a Tuning Set from some statements in AWR
  2. Create a SQL Plan Baseline from the Tuning Set
  3. Create and execute a Tuning Task based on the Tuning Set
  4. Accept the simpler, non-parallel profile recommendations from the Advisor

This way the profiles get created not as standalone profiles but part of SQL Plan Baselines – SQLOB$ object type 2 – and accepted and enabled in DBA_SQL_PLAN_BASELINES (FLAGS=11 in SQLOBJ$).

I’ll back this all up with some proof and isolated examples one day.
At the minute, everything’s just too manic, manic trying to get this stuff to work, manic fighting management FUD about just about any new feature since Oracle 9…

SQL Tuning by Formatting

In a recent post on the oracle-l mailing list, Tim Gorman made a neat summary about a very simple starting approach to SQL tuning, an approach which I’ve often found invaluable despite its apparent old-fashionedness and “ludditity”:

Everyone’s method varies, but instead of using a automatic formatting program like TOAD, consider simply re-formatting and “pretty-fying” the SQL statement by hand, in an editor (I like “vi”). By the time you finish re-arranging the SQL text to format the way you like, you’ll be intimately familiar with the logic of the SQL statement and able to start making an intelligent and focused investigation for solutions. I’ve had folks who watch me do this editing accuse me of being anal-retentive and behind the times (and I have no argument with either), but when I finish editing (i.e. big SQL statements sometimes take an hour or more) and I start discussing what the SQL statement is doing versus what the developer probably intended, the jokesters usually shut up and start listening. And, at the end of the process, it is easier to begin testing different solutions as the logic of the SQL is fresh and familiar.


Get every new post delivered to your Inbox.

Join 62 other followers