Using SQL Plan Management to Control SQL Execution Plans

SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in question are used more than once). SPM uses SQL plan baselines that are associated with individual SQL statements to control what execution plans they are permitted to use. It’s a simple but powerful idea that opens the door to the possibility of using SQL plan baselines in a more selective and reactive way: to influence the SQL execution plans of individual queries without having to modify application queries or change the application itself.

 The PL/SQL below will work in both Oracle Database 11g and Oracle Database 12c.

Consider the scenario where you have a SQL statement used by an application that’s got a sub-optimal plan and you need to do something about it. For the sake of argument, let’s assume that you know that there’s a hint you can use to achieve a better plan. I’m going to assume from now on that you want to apply a hint but the application code cannot be changed in any way.

Take a look at the following SQL execution plan. It’s an application query that filters SALES rows using an index:

SQL> SELECT *

  2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'f23qunrkxdgdt'));

PLAN_TABLE_OUTPUT

-----------------

SQL_ID  f23qunrkxdgdt, child number 2

-------------------------------------

select sum(num) from sales where id < :idv

Plan hash value: 2327341677

-------------------------------------------------------

| Id  | Operation                            | Name   |

-------------------------------------------------------

|   0 | SELECT STATEMENT                     |        |

|   1 |  SORT AGGREGATE                      |        |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES  |

|*  3 |    INDEX RANGE SCAN                  | SALESI |

-------------------------------------------------------

What if this plan isn’t optimal? It is optimal in this case, but for the sake of example I’m going to assume that I want the Oracle Optimizer to pick a full table scan instead.  All we need is the FULL hint:

PLAN_TABLE_OUTPUT

-----------------

SQL_ID  82x4tj3z2vg23, child number 0

-------------------------------------

select /*+ FULL(sales) */ sum(num) from sales where id < :idv

Plan hash value: 1047182207

------------------------------------

| Id  | Operation          | Name  |

------------------------------------

|   0 | SELECT STATEMENT   |       |

|   1 |  SORT AGGREGATE    |       |

|*  2 |   TABLE ACCESS FULL| SALES |

------------------------------------

The hinted test query gives us an example of the TABLE ACCESS FULL plan we want to use. At this point we are in a position to use SPM to associate our preferred plan with the application query.  Here are the steps:

Step zero acknowledges that we have a SQL statement with a plan we want to change. The remaining steps are:

1.   Create an initial disabled SQL plan baselines for the application query. I’m using the term “one or more” because a query might have more than one SQL execution plan.  We will create a SQL plan baseline for each plan used by the SQL statement, but only one is actually needed.

2.   Execute (or parse) a hinted test query to generate the preferred plan.

3.   Load the preferred plan into a SQL plan baseline created in step two (this time with enabled=’YES’).

The hinted statement’s text is of course different to the application statement’s text, but that’s just fine: we’re simply using the plan and not the SQL text. Our application query will use the plan as long as it can reproduce it and it’s valid. What do I mean by that? Here’s an example:

Imagine a CUSTOMERS query that happens to perform a FULL scan:

select sum(num) from CUSTOMERS;

If we use the plan for this query in an attempt to influence our SALES query, it’s not going to work.  We would be asking the SQL plan baseline to influence the SALES plan like this:

select /*+ FULL(customers) */ sum(num) from SALES where id < :idv

Under the covers, SQL plan baselines use a complete set of hints to control execution plans. So, for our SALES query, FULL(customers) is not a valid hint and is not going to yield the desired result! If you’ve got some time on your hands, you can try loading a plan for a CUSTOMERS query into a SQL plan baseline associated with a SALES query. There won’t be an error message, but you won’t be able to reproduce the plan you want either (unless it’s just by luck).

Worked Example

I’ve have uploaded an example procedure and a fully worked example to GitHub so you can see how the steps above can be implemented. Based on a comment below, I added this procedure too. It loads all existing plans in a disabled state and adds a new enabled SQL plan baseline (rather than replacing an existing one). You should adapt the procedures to meet your specific requirements. For example, you might not want to drop pre-existing SQL plan baselines.

I’ll be using the SQL IDs and plan hash value that I highlighted in bold, above. Here’s how to use my example procedures set_my_plan and add_my_plan (see proc.sql and proc2.sql in GitHub):

Executing the Procedures

Note that “SPB” stands for SQL plan baseline:

SQL> set serveroutput on

SQL> set linesize 200

SQL> exec set_my_plan('f23qunrkxdgdt','82x4tj3z2vg23',1047182207)

No existing SQL plan baselines to drop

Created 1 disabled SPBs for SQLID f23qunrkxdgdt

SPB Detail: SQL_PLAN_3yr9p97b3j5gbfaa7aab3 handle SQL_3f5d3549d63895eb

Associating plan SQLID/PHV 82x4tj3z2vg23/1047182207 with SPB SQL Handle SQL_3f5d3549d63895eb

Enabled SPB - Name: SQL_PLAN_3yr9p97b3j5gb35032dee SQL handle: SQL_3f5d3549d63895eb

SQL> set serveroutput off

Here’s the explain plan for the application query after the procedure was executed. The non-hinted SQL statement now uses the FULL scan and you can see from the Note section that the SQL plan baseline is being used.

SQL> SELECT *

  2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST'));

PLAN_TABLE_OUTPUT

-----------------

SQL_ID  f23qunrkxdgdt, child number 0

-------------------------------------

select sum(num) from sales where id < :idv

Plan hash value: 1047182207

------------------------------------

| Id  | Operation          | Name  |

------------------------------------

|   0 | SELECT STATEMENT   |       |

|   1 |  SORT AGGREGATE    |       |

|*  2 |   TABLE ACCESS FULL| SALES |

------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("ID"<:IDV)

Note

-----

   - SQL plan baseline SQL_PLAN_3yr9p97b3j5gb35032dee used for this statement

Usage Notes

I tested the scripts in Oracle Database 11g Release 2 and Oracle Database 12c Release 2. The only caveat is that in Oracle Database 11g DBMS_XPLAN may sometimes return ORA-01403 but the example still works.

SPM matches a SQL statement using a signature, not a SQL ID. The signature is generated from the normalized SQL text. For this reason, if there are multiple SQL IDs that have the same signature then they will all share the same SQL plan baseline. For example, the following queries have the same signature:

select sum(num) from sales where id < :idv

SELECT SUM(num) FROM sales WHERE id < :idv

select      sum(num) from sales where id < :idv

The procedure above will drop any pre-existing SQL plan baselines for SQL statements that have the same signature as the application SQL statement. This is a good thing. The hinted plan associated with the SQL plan baselines will be used by all queries that have the same signature.