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 execution 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:
- Automatic capture
- From a SQL Tuning Set
- From the cursor cache
- Unpacked from a staging table
- From existing stored outlines
- 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:
- Parsing Schema
- Action
- Module
- 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 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 schemas 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 simply set them to null.
BEGIN DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', ''); END; /
Thank you for sharing this note ..
Request you to kindly help with clarifying the following as per your convenience ..
This creates a sql baseline or creates and entry in the plan history which , then , has to be evolved into an accepted plan in the baseline .. Would it be possible to provide a little bit of details to make this a complete note for reference and practical use ..