SPM Plan: non reproducibility circumstances and CBO interaction

 1.   Introduction When a critical report which used to complete very quickly starts performing poorly, there is a big chance that the generic reason for this time response degradation is due to a change in an execution plan. If you want to avoid this flip-flop on plan and execution time you have at your disposal a feature called SQL Plan

 1.   Introduction

When a critical report which used to complete very quickly starts performing poorly, there is a big chance that the generic reason for this time response degradation is due to a change in an execution plan. If you want to avoid this flip-flop on plan and execution time you have at your disposal a feature called SQL Plan Management (SPM) which, starting from Oracle 11g release and up, allows you to freeze the ‘’good” plan stabilizing as such your report execution time. In this article I’ll explain the basic concepts of SPM.

I said that you can fix the ‘’good’’ plan into a SPM baseline to avoid surprising response time during the report execution time. This is true provided the plan stored into a SPM baseline is still reproducible during the report execution time and there are many reasons that make an SPM plan non reproducible which  I’ll tell you about in the second part of this article.

Inevitably we are not going to present all of the ‘’disturbing’’ situations which might lead to a non-reproducible SPM plan but we will certainly open a door for other possible investigations in how the Cost Based Optimizer (CBO) manage to reproduce a SPM plan; particularly when it comes to the CBO and to the NLS environment parameters used during the SPM plan capture and the ones used during the report execution time.

2.   The model as preamble

This is the table and the index I will be using to demonstrate the interaction between the CBO and a SPM baseline

I have engineered a partitioned table with a composite index against which I issued a query and captured its execution plan into a SPM baseline, so that if executed again, it will, provided nothing has changed in between, be used as a known and fixed plan.

3.   CBO and SPM plan interaction

Figure1 here below presents the interaction between the CBO and a SPM plan.


It shows that the CBO will, in the presence or the absence of a SPM baseline, start by optimizing an execution plan for the current SQL query. In reality the 10053 CBO trace file reveals that the CBO will immediately check if the current SQL statement is in an SPM plan or not by means of the following message in the corresponding trace file.

Having said that, it is only when the plan optimization common task is accomplished that the CBO will embark on the SPM plan reproducibility. If an SPM plan doesn’t exist then the generated plan is used. If an SPM plan does exist the CBO will:

(a)   verify if the two execution plans (CBO and SPM plan hash value 2) match

(b)   ensure that the SPM plan is reproducible

If the cost-based generated plan matches one of the existing SPM plans, the CBO will use the SPM plan without trying to reproduce it.  We can see this kind of comment in the corresponding 10053 trace file

If the cost-based generated plan doesn’t match one of the existing SPM plans, the CBO will reproduce all enabled and accepted SPM plans and compare their costs. The SPM reproduced plan having the best cost will be the one selected for use. We can see this kind of comment in the corresponding 10053 trace file:

The CBO is signaling that it is aware of the existence of two plans in the SPM baseline and that it is going to reproduce and to re-cost each of them before choosing the best “costed” one.

This is the reproduction and the re-costing of the first plan:

And this is the reproduction and the re-costing of the second plan:

Since both plans have been reproduced, the CBO will use the second one because of its lower cost (272)

That is, reduced to the bare minimum, how the CBO interacts with the presence of one or several SPM plan baselines. You have noticed that to guarantee plan stability you might pay in return a parsing time penalty. This is particularly true when you have multiple accepted and enabled baselines which enter in a cost competition when the CBO comes up with a different execution plan.

4.   SPM plan reproducibility

We saw in the previous section that when the CBO comes up with a plan that doesn’t exist into the SPM baseline this plan will be constrained provided the base lined plans are still reproducible. This is why I am going to tell you few of those reasons that might lead to the non-reproducibility of a baselined plan. I will skip the obvious object absence like dropping an index that has been used during the baseline capture. Consider, for example, the following query on the partitioned table presented in the preamble section:

This query is already constrained and protected against any plan instability by the following “indexed” SPM baseline (see Note below in the code).

We can get detailed execution plan of the above plan baseline using the very useful Oracle dbms_xplan package:

The most important information in this SPM plan is the Plan id. We will see later in this article how crucial this is for the CBO to decide whether to use the plan it comes up with or the plan in the SPM baseline. Put simply, anything that changes the baselined Plan id will make the baseline plan non-reproducible and will make the CBO use its generated plan instead.

When the CBO realizes that the query it’s optimizing is constrained by a SPM plan, it has to ensure that the SPM plan is still reproducible before using it – in case the CBO plan is not found into the SPM baseline of course. For that it will compare the plan_hash_value_2 (phv2) of the reproduced plan with the plan id of the plan in the baseline. If they match then the SPM plan is used. If not then the CBO plan will be used.

Using the script phv2.sql shown below we can get the phv2 of our query which is identified by its sql_id (ahufs9gr5x2pm)

We can point out from the output of the phv2.sql script that our query has one child cursor with the same PHV2 value (2943285262) which is equal to the Plan Id (2943285262) of the SPM plan. And this is why our query has been honored via the stable and known base lined plan.

What would break this situation? A simple answer is: anything that will end up by associating a PHV2 value that is not the same as the Plan Id in the SPM baseline. And this is what I am going to show in the next section

 4.1 Renaming the index

If we look at the content of the SPM baseline, we will see that we have only once accepted and enabled plan covering our query (or those with the same force matching signature) as shown below:

I am going now to rename the index T_R_I1 to  T_R_I2 and execute again the same query.

Spot how the SPM plan is not anymore used. A new enabled and not accepted plan has been queued into the SPM Baseline for future verification:

The new PHV2 (1703391970) of the CBO produced plan is not equal to the SPM plan Id (2943285262). This explains why our query isn’t being honored anymore via the stored SPM plan thanks to the index name change. That’s pretty strange because when reproducing the plan, Oracle is not using the index name but the index column as it is clearly shown in the SPM plan details obtained using the OUTLINE option:

Despite the fact that the name of the index is not used in the SMB outline, renaming it impeaches the CBO to generate the same Plan Id (PHV2) and hence ceases to use the SPM plan.

   4.2 Changing the index type

There are several index types and changing them might influence the reproducibility of the SPM plan. Now, I’ll change my original index type from a simple b-tree index to a locally prefixed index (index that does include the partition key) and re-execute the original query.

Again changing the index type from a b-tree to a locally partitioned index breaks the SPM Plan Id generation.

There are nevertheless exceptions or particular cases where changing the index type seems to have no influence on the use of a SPM plan. I am going to illustrate two of these exceptions.

     4.2.1. Function based index with the same starting columns

Suppose that I will change my initial index from a b-tree index to a function based index by adding a third column at the end of this index as shown below

This type of change doesn’t impact the base lined plan to be used. This is true only when the indexed columns that serve the SPM plan creation (id, c1) are (a) still in the new index, (b) at the same position and (c) always provided the new index name remain the same as the original one.

     4.2.2. Reverse Index

The second particular case of index type change that seems to have no influence on the reproducibility of an indexed SPM plan is the action of reversing an index.

There are situations when reversing an index becomes a possible solution or a workaround for a problematic issue. I am not a fan of this operation because, while solving an index block contention in a highly multi-concurrent insert application, reversing an index might introduce several execution time penalties when range scanning it because the indexed values will be dispersed all over the index leaf blocks. This is without mentioning that distributed transactions are unable to use reversed indexes.

The good news (or bad news, it depends) is that reversing an index will not pre-empt an SPM plan using the original non reversed index, to be chosen by the CBO.

   4.3 Changing the order of the index columns

We all know how very important the starting columns of an index are. We should always pay a particular attention to place the columns on which an equality predicate is applied at the leading edge of the index.  The index column order obviously plays a crucial role into the reproducibility of a SPM plan. Consider the following change in my original index (the index being function based or b-tree) where I added a new column at the beginning of the index:

The base lined plan is no longer reproducible.


5.   Conclusion

Although I haven’t exhausted all the possible situations that might affect whether an SPM plan is reproducible, I have shown enough information to take into account before changing an object (index, table) used during the SPM plan capture so that you shouldn’t be surprised to see your critical report performing badly while you think that you’ve definitely constrained it with a stable and accurate plan. In the next article I will show two other aspects that influences the reproducibility of a base lined plan which are the CBO and the NLS parameters. In the next article I will also try to answer questions like: Which optimizer parameters will be used during the report execution? Are they the optimizer parameters stored during the baseline capture time or the optimizer parameters of the current execution environment?