Intermediate SQL Color Coded SQL, UNIX and Database Essays

ORACLE 11g SQL Plan Management: The Dark Side of SPM. Part 4

With SQL Plan Management being relatively new, it is inevitable that many people run into problems using it.

Most of those problems are caused by our (relative) ignorance: SPM does change the way how ORACLE runs SQL statements and it simply takes time to get used to how it works. Yet, some of the consequences of using SPM are truly bizarre and will surprise many people (including, probably, some ORACLE developers).

In this post I will describe the 3 scenarios where we have recently run into problems using SPM and you can judge for yourself …

This is essentially the problem that I described in the first post, so here I will provide a simplified description of what happened.

Imagine that you have a large table in your schema, say the one that is created by the statement below:

You set up implicit SPM capture and run your workload.

After a while, all of your major SQLs are implicitly captured and SPM baselines are created for them.

Since baselines are the first to be created, they are ACCEPTED and are now being used for your statements.

Somewhere along the line you realize that the FULL TABLE SCAN that this SQLs is running is NOT very efficient. An INDEX might help things a lot! So, you create the index:

Since the index is UNIQUE and your data distribution is uniform, the above query should switch to using the index, right ? It seems obvious, but let’s find out.

Well, the reality is – it didn’t – the query is still running FULL TABLE SCAN and performing ~ 250k logical reads while at that.

What happened here ? Why is our query NOT using the index ?

In a word, what happened is – SPM. Since ACCEPTED SPM Baseline already exists for this statement, even if the statement generates a NEW execution plan (and it DOES, because it can now use the index), the new plan/baseline, although created, is NOT ACCEPTED and hence cannot be used.

Let’s see for ourselves:

Notice the new plan for our statement. It comes with a much better cost and yet, it cannot be used because it is NOT ACCEPTED (yet).

I’ll repeat it again in a slightly more dramatic fashion:

Even though it feels weird, I believe this is a correct behavior. You wanted plan stability and you GOT IT (remember, this is what SPM is really about).

But still, running FULL TABLE SCANs for this statement is obviously inefficient … So, how can we make SPM accept the new (and, in our opinion, better) execution plan ?

Simple – we should prove it to ORACLE that it is better! We can do it by “evolving” the baseline (while creating baselines does not require special permissions, to “evolve” one you need to have ADMINISTER SQL MANAGEMENT OBJECT privilege).

The “evolution” process runs pretty long and when you look at ORACLE you see that both FULL TABLE SCAN (currently ACCEPTED) and INDEX UNIQUE ACCESS (REQUESTED) paths are tried.

At the end you see the following report and YES, your new plan has been tried, deemed MUCH BETTER than the original one and ACCEPTED.

So, now when you run the statement again, it is using a new, much more efficient SPM Baseline:

You might wonder, what happened to the original baseline ? Did it get demoted and changed its status to NOT ACCEPTED ?

Strangely, NO, both old and new baselines are now ACCEPTED (and, according to ORACLE, a more efficient baseline will be chosen in this case).

Despite this little odd thing, I hope that you can see now that SPM behavior is very logical and SPM is actually working as advertised to enforce plan stability.

The previous scenario displayed things about SPM that were pretty weird, but let me tell you – what you are about to see is much MUCH weirder …

All the setup from the previous scenario remains the same, except we have dropped the index and removed all collected SPM baselines to provide the clean slate:

We still have a big table, now with no indexes and we would like to collect some SPM baselines for the same FULL TABLE SCAN SQL that we ran in Scenario 1.

To this point we ran all our statements under the same database user: TEST. For the purpose of this exercise, we will now create a separate database user and give it some basic database permissions:

We now connect to TEST2 and create table T with exactly the same structure as in our original user: TEST. In addition to that, we also create a UNIQUE INDEX on column N. The table, by the way, does not need to be of the same size, so we will create it slightly smaller (but still large enough to see the point).

Now for the interesting part. Let’s run the same SQL in TEST2 that we ran in TEST. Since we have index in place, we should expect that our query should be very efficient.

A full table scan and a baseline! All I can say here is: WOW!

ORACLE is using SPM baseline that was collected for a different schema (and obviously different object) and is applying it to this new statement.

Unbelievable!

But there is more! Let’s exchange bind variable N with bind variable M (everybody who studied mathematics in college would recall that in most cases M >> N )

Again, unbelievable and confirms our worst suspicions – SPM “match” is done entirely by text of the query without reference to the specific object!

But wait, there is even more … Let’s modify our new table by adding some additional columns:

And now let’s repeat our original statement:

And, yet again, even though we have table with a different size and STRUCTURE our query is matched (simply by text) to the same “external” (that is – created for different schema) SPM baseline.

Notice that neither creating the baselines in the original schema nor “assigning” them in the target schema required any special database permissions. And thus, if nothing more, ORACLE gave us a new major tool for practical jokes in the database.

You want to screw up database performance for your neighbor ? There is nothing easier than that: Create similar objects in your own schema (remember, they do not even have to be the same size or structure), run there your neighbor’s SQLs (which are, of course, available in v$SQL view), collect baselines and voila – your neighbor’s SQLs are suffering (and she probably does not have a clue what is going on …).

I consider it a major bug as I do not see any reasonable explanation why SPM works this way. Just to repeat:

One can probably argue that SPM is mostly useful for OLTP systems. Typical OLTP queries are designed for sub second response times and changes in their behavior and timing will be highlighted immediately (and therefore keeping execution plans stable is a good technique there).

One thing that everyone knows about OLTP queries is that the vast majority of them MUST use bind variables. A common wisdom (not to mention multiple horror stories) says that if binds are NOT used, the applications will likely run into serious performance and serialization problems.

And yet, many OLTP systems are still suffering from the literal SQL problem (if this was NOT true, articles about using binds would NOT be SO popular … )

While literals in OLTP queries are BAD for many reasons, SPM introduced yet another dimension into this equation. Consider, for example, what would happen if implicit SPM capture is turned ON (which I would argue is generally a GOOD thing for OLTP system) and the system is generating A LOT of literal SQLs.

At the first glance, you could probably guess that nothing BAD would happen. Since:

1. Only REPEATABLE SQLs are captured and converted into baselines
2. Literal SQLs are UNIQUE by definition

Therefore, SPM baselines would NEVER be created for literal SQLs.

But this is a bit naive.

First of all, literal SQLs are rarely truly unique. In many systems, the number of distinct literal SQLs is limited by the number of records in the target table (which might be large, but certainly not infinite).

Second, most applications have a very specific (and rather small) “active data window”, which further encourages literal SQLs to be repeated, somewhat …

And lastly, SQLs do not need to be repeated a lot, remember that TWICE is sufficient for a baseline to be created.

As such, over time OLTP systems with many literal SQLs are likely to generate and store a LOT of SPM baselines. And the thing that I would like to focus here is: storage.

The use of SPM baselines does consume additional space as baselines record additional information about (in this case, individual) SQLs. This usage is fairly significant: baselines “for real production SQLs” created in one of our QA systems averaged ~ 4 Kb each, not including indexes (your mileage may vary, of course).

Hence, on a busy system, these literal baseline ‘space leaks’ may add up fast and will have to be eventually (or in some cases, rather quickly) dealt with.

The bottom line is that:

Isn’t it funny that the tool that was designed to reduce nasty surprises in application performance comes loaded with (some would say even nastier) surprises of its own ?

Well, at least it is not entirely unexpected … ORACLE 11g SQL Plan Management is a new feature and as anything new it needs to go through some growing pains. Granted, some of these “pains” may look pretty ugly right now (as we’ve seen in this post), but this should NOT keep us from using it.

Or should it ? …

Let’s look at the big picture: It is fair to say that everybody hates performance surprises caused by SQL plan “mood swings”.

End users hate them because they disrupt their workflow.

Developers hate them because they make it much more difficult to design well performing applications.

DBAs hate them because it’s no fun working in the middle of the night scrambling for solutions.

And CIOs … Well, CIOs hate those developers and DBAs that cannot get performance surprises under control …

In other words, being able to provide a stable performance for database applications is not only a worthy goal by itself but it also directly affects a quality of life (not to mention job security) for many people.

And,

Yes, SPM is somewhat unorthodox and yes, it requires some extra effort to learn how it works and avoid certain pitfalls, but would I make that effort given all that was said before ?

Hell, yeah! …

And I’m sure that my developers, my users and my CIO will thank me for that.