How does SQL Plan Management match SQL statements to SQL plan baselines?

https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management

 

 

How does SQL Plan Management match SQL statements to SQL plan baselines?

By Maria Colgan-Oracle on Jan 09, 2013

Happy New Year to all of our readers!

As more and more of your systems migrate to Oracle Database 11g, SQL Plan Management (SPM) is becoming increasingly popular as a mechanism to maintain plan stability and subsequently system performance. However, one of the key frustrations folks encounter when using SPM is that the SQL plan baseline they painstakingly created is not used. So, how does Oracle decide whether or not to use an existing SQL plan baseline?

SQL statements are matched to SQL plan baselines using the signature of the SQL statement. A signature is a unique SQL identifier generated from the normalized SQL text (uncased and with whitespaces removed). This is the same technique used by SQL profiles and SQL patches. This means, if you issue identical SQL statements from two different schemas they would resolve to the same SQL plan baseline. Now, you might be thinking that this approach could lead to all sorts of performance problems because you may not want the same plan used by each schema. But remember a SQL plan baseline can have multiple plans for the same SQL statement and SPM records the parsing schema along with each plan.

Let’s take a look at this in action to fully understand why it is not a problem using the following SQL text,

Select count(s.quantity_sold) total_sales

From Sales s

Where s.prod_id=30;

We begin in the SH sample schema and execute the statement to see the cost-based plan.

Let’s create a SQL plan baseline for this SQL statement. We can do this by capturing the plan directly from the cursor cache into a SQL plan baseline, using the DBMS_SPM package. But first we need the SQL_ID for our statement, which we can get from V$SQL.

We can confirm our SQL plan baseline exists and our preferred plan is both enabled and accepted by querying DBA_SQL_PLAN_BASELINES.

By re-executing the query we can see that the SQL plan baseline is now being used even when we change the case and white spaces in our statement.

Let’s now connect as the sample schema SCOTT. We have created an identical copy of the sales table in the SCOTT schema but it does not have the IND_SALES_PROD_QTY_SOLD index. If we execute the identical SQL statement from the SCOTT schema should it use the existing SQL plan baseline or not?

Well, we didn’t get the same plan or a note to say the SQL plan baseline was used. The plan used in this case was a fast full scan of the bitmap index SALES_PROD_BIX. So does this mean the SQL statement wasn’t matched to our existing SQL plan baseline?

No, the SQL statement was in fact matched to the existing SQL plan baseline but the accepted plan in the SQL plan baseline couldn’t be reproduced in the SCOTT schema.

Here is exactly what happened. When the statement was issued in the SCOTT schema Oracle determined the best cost-based plan for the SQL statement to be a fast full index scan on the SALES_PROD_BIX index. Before executing this plan we checked to see if the SQL statement matched an existing SQL plan baseline using its SQL signature. We found a corresponding SQL plan baseline, so we checked to see if the cost-based plan we came up with matches an accepted plan in the SQL plan baseline. It does not, so our cost-based plan got added to the SQL plan baseline as a non-accepted plan and we tried to use the existing accepted plan. However, the existing plan requires a b-tree index on the prod_id and quantity_sold, which doesn’t exist in the SCOTT schema. Since we can’t reproduce the accepted plan, we use the cost-based plan that the Optimizer came up with at parse. If we check the SQL plan baseline we will see that this new plan using the SALES_PROD_BIX index has been added to the SQL plan baseline but it is not accepted.

If the new plan is accepted using DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE procedure, we will see that it is used by the identical SQL statement in the SCOTT schema.

Now, that the new plan is accepted let try running the SQL statement again from the SCOTT schema.

So, as you can see the same SQL plan baseline is being used for an identical SQL statement issued from two different schemas. This is not a problem since a SQL plan baseline can have multiple accepted execution plans. You just need to manage the plans in the SQL plan baseline to ensure your preferred plan for each schema is an accepted plan.

You can get a copy of the script I used to generate this post here.