Adaptive Cursor Sharing with SQL Plan Baselines

Maybe you, like me, have read documentation that says SQL Plan Baselines work together Adaptive Cursor Sharing and wondered what this means? This is something lower down the priority list that I’ve been meaning to take a closer look at for some time.

Executive Summary
ACS and baselines do work together.

But I know when I read the documentation, I came away with some questions that I wanted to look at. I wondered if they did more together than they do.

For example, did baselines preserve some of the ACS not-so-secret sauce such that if the ACS information aged or was flushed out of the cache, we didn’t have to repeat the multiple executions to get the bind awareness back?

1. The bind awareness will kick back in eventually.
2. The baseline mechanism will allow the optimizer to use the ACS feedback and consider both plans in the baseline.
3. But we need to repeat the executions to get back to the bind awareness i.e. three runs to have one bind aware plan, four runs to have the two bind aware plans that we preserved initially.

Is that a big deal?

We’ve had to do one extra execution each of the statement but if that initial execution with the “wrong” plan was a big enough performance problem, then the implications could be significant.

And the two mechanisms that I thought might be designed to work together to address it, don’t unless you can guarantee that the information will not age out of the cache.

More Detail
Let me show you what I looked at.

Recap on ACS

Adaptive Cursor Sharing refers to cursors being bind sensitive and then bind aware.

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
– The optimizer has peeked at the bind values to generate selectivity estimates.
– A histogram exists on the column containing the bind value
….
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values.

We’re back where we started. We’re using one of our baseline plans but we’ve lost our bind-awareness.

What do you mean we’ve lost our bind-awareness?

Well, previously we did all that hard work with multiple executions to get our bind-awareness. The baselines have preserved and are enforcing our ACCEPTED plans but we have to repeat the multiple executions to get back the bind-awareness.

Maybe it helps if we remind ourselves what the optimizer does when baseline plans are present.

At hard parse time, the optimizer will peek at the binds and generate a best cost plan anyway, regardless of the presence of a baselined plan.

If the best cost plan matches one that’s in a baseline, then we get a note in an optimizer trace file along the lines of:

SPM: cost-based plan found in the plan baseline, planId = 2239163167

If we don’t have the best cost plan in the baseline then we get a line in the optimizer trace along the lines of:

and a new plan is added to the baseline as UNACCEPTED and stored for future evolution whilst a baseline is then used to generate the parsed plan.

So, whilst we do have our two baselined plans, the initial hard parse peeks at the binds and gets ‘N1′, generates the best cost plan which matches one of the baselines – job done. (And if the initial hard parse had peeked and found value ‘Y1′ we would have had our baselined index plan).

The subsequent execution of the cursor with a different value falls into the standard shareable SQL scenario – the initial cursor was parsed with peeked binds, the values of which do not suit our subsequent execution until ACS kicks in as previously.

If we return to our sql statement above and re-execute with the same bind that should be using an index, we see the switch from baseline plan SQL_PLAN_13w748wknkcwd616acf47 to SQL_PLAN_13w748wknkcwd8576eb1f:

In summary, from the documentation, you may get the impression that there’s more to the combination of ACS and baselines than there really is.

ACS and baselines work alongside each other but somewhat independently.
ACS can provide SPM with bind-sensitive plans to evolve.
Whilst SPM can store the multiple plans that ACS requires.
But ACS attributes are not stored in SPM.

Even with multiple plans in the a baseline, if your ACS information is flushed or ages out of the cache, you’re going to have to repeat the relevant executions required to get that ACS information back. Baselines can’t act as a shortcut to bringing back in that ACS feedback.

BOTTOM LINE: if you’re using binds when you should be using literals, there’s no silver bullet

I’ve made no mention here of views:

V$SQL_CS_HISTOGRAM

V$SQL_CS_SELECTIVITY

V$SQL_CS_STATISTICS

but these are worthy of further observation if you want to investigate ACS. However that was not the point of this post.

I’m pretty sure you’ve proved my point in your post – i.e. we need to do all the multiple executions again to get back the bind awareness, etc.

It was difficult in this long post to put across the point I was trying to make. I think you’re probably right that the documentation says that ACS is in memory only and so all I’ve really done is shown that that is the case.

Once pinned sql baselines are bind sensitive and optimizer knows whish SPM to use for with bind variable. The only way to get real information is from trace and event 1053 level 1. Once SMB is set we can’t rely on cursor cache. Here is my example

I’m interested to see your example particulary as I think I’ve shown above that the bind sensitivity is in memory (in the shared pool) and as soon as you lose that, you lose the sensitivity until you execute enough times with the appropriate bind sets to regain it. SPM contains nothing about bind sensitivity.

If you put your example in the previous comment then it didn’t make it through. Please try again or otherwise email to me – dombrooks@hotmail.com – and I’ll get it in.

Interesting test case but it doesn’t show what you think it shows. The cursor cache does not lie.
The initial cursor with bind 50000 is invalidated, bind 25000 does a further hard parse, both hard parses peek at their relevant bind variables and therefore get the appropriate plan.

Hi Dominic,
I’ve spent some more time testing this. You are absolutely right. More then likely cursor aged out before I captured it. This is true for first execution after flushing shared pool and with baseline created.
Thanks for clarifying this,
Lazar

I played around with this a little more and as long as I have column statistics in user_tab_columns then I get bind sensitivity, even if the partition I am hitting has no stats. If I gather stats at the partition level only so user_tab_columns is empty then IS_BIND_SENSITVE=N.

So this is pleasing as I’m unlikely to have no global stats on any tables and I’ll get ACS when I most need it – when new partitions are added.