April 4, 2013

What can impeach Adaptive Cursor Sharing kicking off?

I ended my last post about the interaction between ACS and SPM by the following observation

How could a creation of an extra index disturb the ACS behavior?

Well, it seems that there is a different combination which leads to this situation. Instead of jumping to a conclusion that might be wrong I prefer presenting my demo upon which I will make a proposition and let you (readers thanks in advance for that) criticizing what I tend to affirm.

For sake of simplicity, the following sql against v$sql will be referred to as is_bind_aware.sql.

Two child cursor(2 and 3) that are (a) shareable (b) bind sensitive and (c) bind aware so that ACS can associate each bind variable to it’s a corresponding child number and hence the execution plan that best fits each bind variable.

Up to this point, ACS is working very well in presence of a SPM baseline

I have two enabled and accepted sql plan baseline (one, SQL_PLAN …eb1f, for the index range scan and the other one, SQL_PLAN … acf47, for the table full scan). Now, I will create an extra index(i2) in addition to the existing i1 index and I will continue my selects

No way for the CBO to produce the INDEX RANGE SCAN Plan so that the SPM will select it. And why the CBO is unable to produce the INDEX RANGE SCAN plan? There might be two answers to that question (a) either the ACS is working well but it is producing a plan that is not in the SPM and hence it is constrained or (b) the ACS is not working and the CBO is always sharing the existing FULL TABLE SCAN until a hard parse occurs. Let see first if the ACS is working well

The post is becoming long and may be annoying so I will stop here not without mentioning that I did played with the demo setting the optimizer_capture_sql_plan_baselines to TRUE/FALSE and observing the behavior of ACS through its corresponding views and it seems that adding an extra index generates a new sql plan baseline that is not into the SPM and influence a little bit the work of ACS without knowing the exact reason.

0.0000000.000000

Advertisements

Share this:

Like this:

Related

> How could a creation of an extra index disturb the ACS behavior?
Before the index creation you have four child cursors only two of which are valid and shareable.
On index creation, children #2 and #3 are also invalidated.
This is expected, right?
When you execute the sql immediately after the sql creation, child #2 is revalidated and child#3 is cleaned up (but not #0 and #1).

The point being that you need the multiple executions to get the bind awareness. And if, for whatever reason, this information is lost (ages out or is invalidated for example sue to index creation) then you need to repeat the multiple executions to regain it.

Thanks for your comment. But see how many times I have executed the query with the index range bind variable value: 18 times without making the corresponding child cursor (n°2) at least bind sensitive .

I’ve got a feeling this reply could be the length of post in itself… sorry.

So, a quick recap for my own sake.

Bind sensitivity (is_bind_sensitive) is just a flag that indicates that information exists that means a statement could be bind sensitive
i.e. can be as simple as using binds and peeking @ binds, stats seem to be needed but not necessarily histograms.
There doesn’t even need to be an index present to offer an alternative access plan.

Bind awareness is then a step on from bind sensitivity which can indicate (but not always – let’s not go there) that the optimizer has different cursors for different sets of binds.

But, once bind sensitivity and bind awareness has aged out, you’ve shown that the baseline can have an influence – albeit in a different way from my original post which was just intended to show that a baselined plan contains nothing about bind sensitivity, etc.

So, looking at your illustration:

Immediately after the index creation you have some invalid, non-shareable cursors and some invalid, shareable cursors.

What happens next depends on which bind you run with first and whether you have the two plans – FTS and Index RS – baselined.

The critical point is that the i2 index now means that for bind := ‘N1’, the optimizer favours an index FFS rather than the FTS previously.

Regardless of baselines, the first execution of whichever bind results in:
1. The “clean up” of the invalid, shareable child cursors.

If you DO NOT have the two baselined plans in place, the first execution of whichever bind results in:
2. A new child cursor which IS bind sensitive but not bind aware
If the execution is with bind := ‘N1’ then it should be the “new” index FFS plan, for ‘Y1’ the previous index RS plan.

If you DO have the two baselined plans in place, if you execute the SQL with bind := ‘Y1’ then:
2. A new child cursor which IS bind sensitive but not bind aware

If you DO have the two baselined plans in place, if you execute the SQL with bind := ‘N1’ then:
2. A new child cursor which IS NOT bind sensitive (and therefore not bind aware either)

The influence of the baseline here is that for bind N1, the optimizer cannot pick the best cost generated plan with index FFS of i2 because
it is not baselined so the optimizer has to use an alternative baselined plan.

That seems to be the critical factor that means that bind sensitivity is not picked up until the ‘Y1’ execution happens.

There are some interesting observations to be made when running and rerunning the tests.

I’ve rerun these tests so many times and sometimes the results are not consistent – I’m always hopeful that that’s because I’ve done something in a slightly different order or missed a step but I’m not 100% convinced.
Even the number of times you have to initially run the ‘Y1’ execution to get the initial bind sensitivity can change.

In order to make things simple, I dropped all the baselines, dropped the index i2, set the optimizer_use_sql_plan_baseline to FALSE and played with my query: ACS works perfectly alternating between FULL TABLE SCAN and INDEX RANGE SCAN. Then I created the i2 index and again ACS works perfectly alternating between INDEX FAST FULL SCAN and INDEX RANGE SCAN. I dropped again the index and ACS works also perfectly as expected.

In a second step, I set the optimizer_use_sql_plan_baseline to TRUE, and let the optimizer to capture the baseline (in absence of the index) and I have evolved the FTS and the IRS baselines to be ACCEPETED AND ENABLED: and ACS works perfectly with SPM alternating between TABLE FULL SCAN and INDEX RANGE SCAN baselines.

Finally I created the index, run a query to produce the INDEX FAST FULL SCAN plan which I have immediately set into the baseline as ENABLED and ACCEPTED: ACS works perfectly with SPM alternating between INDEX FAST FULL SCAN and INDEX RANGE SCAN baselines.

As I have expected when the IFFS baseline is enabled and accepted the underneath index i2 ceases to disturb the good work of ACS.

Best regards

FootNote : When I dropped the 3 baselines I got some trouble using the INDEX FAST FULL SCAN baseline with the following error

I was obliged to set the optimizer_capture_sql_plan_baseline to TRUE in order to capture the SQL_PLAN_13w748wknkcwd495f4ddb plan and to avoid this error. Thought that I didn’t do this for the other two baselines despite they have been dropped. This is another story

[…] scan that are both shareable. Thanks to these two child cursors (until they are flushed out, or something disturbs their good working), the CBO will be alternating between the two executions plans giving […]

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.