SQL Plan Management – Selective Automatic Plan Capture Now Available!

Over the years, Oracle has provided a number of techniques to help you control the execution plan for a SQL statement, such as Store Outlines and SQL Profiles but for me the only feature to truly give you plan stability is SQL Plan Management (SPM). It’s this true plan stability that has made me a big fan of SPM ever since it was introduced in Oracle Database 11g.

With SPM only known or accepted execution plans are used. That doesn’t mean Oracle won’t parse your SQL statements, it will. But before the execute plan generated at parse is used, we will confirm it is an accepted plan by comparing the PLAN_HASH_VALUE to that of the accepted plan. If they match, we go ahead and use that plan.

If they don’t match, that is to say, a new plan that is found. The new plan is tracked but not used. We use the information the SPM to reproduce the accepted plan. The new plan won’t be used until it has been proven to show a noticeable improvement in runtime.

So, how do I seed SPM with these “known” plans?

There are actually six different ways to populate plans into SPM:

  1. Automatic capture
  2. From a SQL Tuning Set
  3. From the cursor cache
  4. Unpacked from a staging table
  5. From existing stored outlines
  6. From the AWR repository (new to Oracle Database 12c Release 2)

In the past, I would  recommend you populate plans into SPM using options 2 through 5. I wouldn’t recommend automatic capture because it would result in a SQL plan baseline being created for every repeatable SQL statement executed on the system, including all monitoring and recursive SQL statements. On an extremely busy system this could potentially flood the SYSAUX tablespace with unnecessary SQL plan baselines.

But starting in Oracle Database 12c Release 2, it is now possible to limit which SQL statements are automatically captured using filters when you enable automatic plan capture. This enhancement now makes option 1 a very appealing approach especially if you have a system that is currently running well.

How does it work?

Before enabling automatic plan capture, you need to decide what SQL statements you want to capture SQL baseline plan for. Once you have an idea of what you want, you can use the DBMS_SPM.CONFIGURE procedure to set up filters that will control which SQL statements plans will be captured. You can filter on the following 4 things:

  1. Parsing Schema
  2. Action
  3. Module
  4. SQL_Text

For example, if you only wanted to capture plan from the COFFEESHOP schema you would use the following command:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'COFFEESHOP');
END;
/

Alternatively you can filter out a particular schema. For example if you don’t want to capture any plans from the HR schema you would use the following command:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'HR', 'FALSE');
END;
/

Note: you can configure multiple automatic capture parameters of different types but you cannot specify multiple values for the same parameter. Instead, the values specified for a particular parameter are combined. So if I wanted to capture plans for the both the HR and the SH schemas you would use the following:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'SH');
  DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'HR');
END;
/

Once your filters have been defined you can enable automatic plan capture by setting the init.ora parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (default FALSE). When enabled, a SQL plan baseline will be automatically created for any repeatable SQL statement provided it doesn’t already have one based on your criteria.

Repeatable statements are SQL statements that are executed more than once during the capture period. To identify repeatable SQL statements, the optimizer logs the SQL signature, of each SQL statement executed the first time it is compiled in the SQL statement log (sqllog$).

In case you are not familiar with it, a SQL signature is a unique SQL identifier generated from the normalized SQL text (uncased and with whitespaces removed). Although similar to a SQL_ID, it’s not the same but it is the mechanism used by SQL profiles and SQL patches.

If the SQL statement is executed again, the presence of its signature in the statement log will signify it to be a repeatable statement. A SQL plan baseline is created for the repeatable statements that meet your filter criteria. A SQL plan baseline includes all of the information needed by the optimizer to reproduce the current cost-based execution plan for the statement, such as the SQL text, outline, bind variable values, and compilation environment. This initial plan will be automatically marked as accepted.

Let’s take a look at all of this in action to help clarify the steps.

-- Start by setting the desired filters. In this case we only want
-- to capture plans for queries executed in the SH and HR schemas
BEGIN
  DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'SH');
  DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'HR');
END;
/
PL/SQL PROCEDURE successfully completed.
 
-- Next we need to enable automatic plan capture
ALTER system SET optimizer_capture_sql_plan_baselines = TRUE;
 
System altered.
 
-- Now we can begin executing our workload
 
conn sh/sh
Connected.
 
SELECT /*LOAD_AUTO*/ *
FROM sh.sales
WHERE quantity_sold > 40
ORDER BY prod_id;
 
   PROD_ID    CUST_ID TIME_ID	C   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- - ---------- ------------- -----------
       185	29790 22-JUN-98 S	9999		44	  1716
       970	11320 11-DEC-99 P	9999		44	  1716
      1195     158960 11-SEP-00 S	  51		47	2918.7
      1240	43910 14-MAY-99 C	9999		46	  3634
 
conn hr/hr
Connected.
 
SELECT /*LOAD_AUTO*/ *
FROM   hr.regions;
 
 REGION_ID REGION_NAME
---------- -------------------------
	 1 Europe
	 2 Americas
	 3 Asia
	 4 Middle East AND Africa
 
conn oe/oe
Connected.
 
SELECT /*LOAD_AUTO*/ i.product_id, i.quantity
FROM  oe.orders o, oe.order_items i
WHERE o.order_id = i.order_id
AND   o.sales_rep_id = 160;
 
PRODUCT_ID   QUANTITY
---------- ----------
      2870	   10
      3106	  150
      3106	  110
 
-- As this is the first time we have seen these SQL statements, they are not yet
-- repeatable, so no SQL plan baseline have been created for them. In order to confirm this
-- we can check the view dba_sql_plan_baselines.
 
SQL> SELECT sql_handle, sql_text, plan_name,
  2  	    origin, enabled, accepted
  3  FROM dba_sql_plan_baselines
  4  WHERE sql_text LIKE 'select /*LOAD_AUTO*/%';
 
no rows selected
 
-- So, there are no baselines but if we check the statement log we do some SQL signatures were recorded
SQL> SELECT * FROM sys.sqllog$;
 
 SIGNATURE     BATCH#
---------- ----------
3.1614E+18	    1
8.0622E+18	    1
8.6816E+18	    1
 
--  So lets re-execute queries and check if the baselines were created after the second execution
 
conn sh/sh
Connected.
 
SELECT /*LOAD_AUTO*/ *
FROM sh.sales
WHERE quantity_sold > 40
ORDER BY prod_id;
 
   PROD_ID    CUST_ID TIME_ID	C   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- - ---------- ------------- -----------
       185	29790 22-JUN-98 S	9999		44	  1716
       970	11320 11-DEC-99 P	9999		44	  1716
      1195     158960 11-SEP-00 S	  51		47	2918.7
      1240	43910 14-MAY-99 C	9999		46	  3634
 
conn hr/hr
Connected.
 
SELECT /*LOAD_AUTO*/ *
FROM   hr.regions;
 
 REGION_ID REGION_NAME
---------- -------------------------
	 1 Europe
	 2 Americas
	 3 Asia
	 4 Middle East AND Africa
 
conn oe/oe
Connected.
 
SELECT /*LOAD_AUTO*/ i.product_id, i.quantity
FROM  oe.orders o, oe.order_items i
WHERE o.order_id = i.order_id
AND   o.sales_rep_id = 160;
 
PRODUCT_ID   QUANTITY
---------- ----------
      2870	   10
      3106	  150
      3106	  110
 
 SELECT sql_handle,sql_text, plan_name,
  2  	    origin, enabled, accepted
  3  FROM dba_sql_plan_baselines
  4  WHERE sql_text LIKE 'select /*LOAD_AUTO*/%';
 
SQL_HANDLE		       SQL_TEXT 	    PLAN_NAME		   ORIGIN	 ENA ACC
------------------------------ -------------------- ---------------------- ------------- --- ---
SQL_6fe28d438dfc352f	       SELECT /*LOAD_AUTO*/ SQL_PLAN_6zsnd8f6zsd9g AUTO-CAPTURE  YES YES
				*		    54bc8843
			       FROM sh.sales
			       WHERE quantity_sold
			       > 40
			       ORDER BY prod_id
 
SQL_787b46133c9b0064	       SELECT /*LOAD_AUTO*/ SQL_PLAN_7hyu62cy9q034 AUTO-CAPTURE  YES YES
				*		    36cb9897
			       FROM   hr.regions

As you can see from the example above, even though we had three repeatable SQL statements only two SQL plan baselines were created. The SQL statement executed in the OE schema did not have a SQL plan baseline automatically created for it because it was not one of the schema we told SPM that we wanted to automatically capture SQL plan baselines for.

By selecting only the schemas that we are really interested in, we can keep the number of SQL plan baselines to a reasonable amount, making it easier to manage them or move them between dev / test and production.

Finally if you want to remove any of the filters, you can simple set them to null.

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', '');
END;
/
This entry was posted in Optimizer, Oracle Database 12c New Features, SQL Plan Management, Uncategorized and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *