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.

SPM: statement found in SMB

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:

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:

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:

select * from t_range where id = :n1 and c1 = :n2;

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

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 Planid 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:

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.

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:

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?

Mohamed Houri has a PhD in Fluid Mechanics (Scientific Computing) from the University of Aix-Marseille II, preceded by an engineer diploma in Aeronautics. He has been working around the Oracle database for more than 14 years for different Europeans customers as an independent Oracle Consultant specialized in Tuning and Trouble-shooting Oracle performance problems. Mohamed has also worked with the Naval Architect Society of Japan on the analysis of tsunamis and breaking waves using a powerful signal analysis called Wavelet Transform. He maintains an Oracle blog and is active in the Oracle Worldwide forum and in the French equivalent. He tweets about Oracle stuff at @MohamedHouri.

Ahmed,What is new for SPM in 12c?
The Oracle optimizer group has mentioned in this article that any constrained plan that has not been used by the CBO and that has been put into the SQL Management Base for future evolution is, starting from 12c, subject to an automatic evolution via the SYS_AUTO_SPM_EVOLVE_TASK. This 12c plan automatic evolution is among one of the reasons Jonathan Lewis has mentioned in his article about query random slowdown.

I have also mentioned in this blog article that, in contrast to the preceding release, the execution plan in the SQL plan baseline can still be displayed (via dbms_xplan.display_sql_plan_baseline API) even if we drop an object that was used during the capture of the execution plan. Dominic Brooks has used the correct word to explain this new change in 12c: ’’DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE can show the plan which it’s meant to reproduce’’
Best Regards

Houri,
"Execute Known Baseline plan" - If there are more than one plan baselined for a SQL_ID and if CBO's generated plan peeked for a bind value, then you will hit a performance issue.
1.Two plans (FTS with bind value N1 and Index Range Scan with bind value Y1 ) baselined
2. CBO chose FTS for bind value N1 and this is peeked and found the same plan in SPM and then SPM plan is used
3. change bind value to Y1 and execute and now CBO chose FTS again as ACS is not yet warmed-up. Though you have Index Range Scan plan in SPM , this execution would still use FTS baselined plan.. Performance issue here....
Thanks

Cary Millsap’s latest book

The Method R Guide to Mastering Oracle Trace Data, Second Edition contains the richest description of Oracle extended SQL trace data that you’ll ever find, and over 100 pages of worked examples, using the software tools built by Cary’s Method R Corporation.