Do you ever experience performance regressions because an execution plan has changed for the worse? If you have, then we have an elegant solution for you called SQL Plan Management (SPM). The next four posts on our blog will cover SPM in detail. Let's begin by reviewing the primary causes for plan changes.
Execution plan changes occur due to various system changes. For example, you might have (manually or automatically) updated statistics for some objects, or changed a few optimizer-related parameters. A more dramatic change is a database upgrade (say from 11g to 12c). All of these changes have the potential to cause new execution plans to be generated for many of your SQL statements. Most new plans are obviously improvements because they are tailored to the new system environment, but some might be worse leading to performance regressions. It is the latter that cause sleepless nights for many DBAs.
DBAs have several options for addressing these regressions. However, what most DBAs want is simple: plans should only change when they will result in performance gains. In other words, the optimizer should not pick bad plans, period.
This first post in our series, describes the concepts of SQL Plan Management and how to create SQL plan baselines. The second part will describe how and when these SQL plan baselines are used. The third part will discuss evolution, the process of adding new and improved plans to SQL plan baselines. Finally, the fourth part will describe user interfaces and interactions with other Oracle objects (like stored outlines).
SQL Plan Management (SPM) allows database users to maintain stable yet optimal performance for a set of SQL statements. SPM incorporates the positive attributes of plan adaptability and plan stability, while simultaneously avoiding their shortcomings. It has two main objectives:
A managed SQL statement is one for which SPM has been enabled. SPM can be configured to work automatically or it can be manually controlled either wholly or partially (described later). SPM helps prevent performance regressions by enabling the detection of plan changes for managed SQL statements. For this purpose, SPM maintains, on disk, a plan history consisting of different execution plans generated for each managed SQL statement. An enhanced version of the Oracle optimizer, called SPM aware optimizer, accesses, uses, and manages this information which is stored in a repository called the SQL Management Base (SMB).
The plan history enables the SPM aware optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. A brand new plan represents a plan change that has potential to cause performance regression. For this reason, the SPM aware optimizer does not choose a brand new best-cost plan. Instead, it chooses from a set of accepted plans. An accepted plan is one that has been either verified to not cause performance regression or designated to have good performance. A set of accepted plans is called a SQL plan baseline, which represents a subset of the plan history.
A brand new plan is added to the plan history as a non-accepted plan. Later, an SPM utility verifies its performance, and keeps it as a non-accepted plan if it will cause a performance regression, or changes it to an accepted plan if it will provide a performance improvement. The plan performance verification process ensures both plan stability and plan adaptability.
The figure below shows the SMB containing the plan history for three SQL statements. Each plan history contains some accepted plans (the SQL plan baseline) and some non-accepted plans.
(Click on the image for a larger view.)
You can create a SQL plan baseline in several ways: using a SQL Tuning Set (STS); from the cursor cache; exporting from one database and importing into another; and automatically for every statement. Let's look at each in turn. The examples in this blog entry use the Oracle Database Sample Schemas so you can try them yourself.
SQL> variable pls number; SQL> exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'MY_STS', - > basic_filter => 'sql_text like ''select%p.prod_name%''');
This will create SQL plan baselines for all statements that match the specified filter.
SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( - > attribute_name => 'SQL_TEXT', - > attribute_value => 'select%p.prod_name%');
This will create SQL plan baselines for all statements whose text matches the specified string. Several overloaded variations of this function allow you to filter on other cursor attributes.
If you already have SQL plan baselines (say on an 11g test system), you can export them to another system (a production system for instance).
First, on the test system, create a staging table and pack the SQL plan baselines you want to export:
SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'MY_STGTAB', - > table_owner => 'SH'); PL/SQL procedure successfully completed. SQL> exec :pls := dbms_spm.pack_stgtab_baseline( - > table_name => 'MY_STGTAB', - > table_owner => 'SH', - > sql_text => 'select%p.prod_name%');
This will pack all SQL plan baselines for statements that match the specified filter. The staging table, MY_STGTAB, is a regular table that you should export to the production system using Datapump Export.
On the production system, you can now unpack the staging table to create the SQL plan baselines:
SQL> exec :pls := dbms_spm.unpack_stgtab_baseline( - > table_name => 'MY_STGTAB', - > table_owner => 'SH', - > sql_text => 'select%p.prod_name%');
This will unpack the staging table and create SQL plan baselines. Note that the filter for unpacking the staging table is optional and may be different than the one used during packing. This means that you can pack several SQL plan baselines into a staging table and selectively unpack only a subset of them on the target system.
SQL> alter session set optimizer_capture_sql_plan_baselines = true; Session altered. SQL> var pid number SQL> exec :pid := 100; PL/SQL procedure successfully completed. SQL> select p.prod_name, s.amount_sold, t.calendar_year 2 from sales s, products p, times t 3 where s.prod_id = p.prod_id 4 and s.time_id = t.time_id 5 and p.prod_id < :pid; PROD_NAME AMOUNT_SOLD CALENDAR_YEAR --------- ----------- ------------- ... 9 rows selected. SQL> select p.prod_name, s.amount_sold, t.calendar_year 2 from sales s, products p, times t 3 where s.prod_id = p.prod_id 4 and s.time_id = t.time_id 5 and p.prod_id < :pid; PROD_NAME AMOUNT_SOLD CALENDAR_YEAR --------- ----------- ------------- ... 9 rows selected. SQL> alter session set optimizer_capture_sql_plan_baselines = false; Session altered
Automatic plan capture will not occur for a statement if a stored outline exists for it and is enabled and the parameter use_stored_outlines is TRUE. In this case, turn on incremental capture of plans into an STS using the function capture_cursor_cache_sqlset() in the DBMS_SQLTUNE package. After you have collected the plans for your workload into the STS, manually create SQL plan baselines using the method described earlier. Then, disable the stored outlines or set use_stored_outlines to FALSE. From now on, SPM will manage your workload and stored outlines will not be used for those statements.
In this article, we have seen how to create SQL plan baselines. In the next, we will describe the SPM aware optimizer and how it uses SQL plan baselines.