Introduction
This post covers the basics of SQL plan management. If you want to know more about automatic SQL plan management, it’s covered in this post. If you already know the basics, you may find the cheat sheet helpful.
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 changed some optimizer-related parameters or upgraded the database. These changes have the potential to cause new execution plans to be generated for many of your SQL statements. Most new plans should be improvements because they are tailored to the new system environment, but some might be worse, leading to performance regressions.
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
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:
- prevent performance regressions in the face of database system changes
- offer performance improvements by gracefully adapting to database system changes
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. The Oracle Optimizer is SPM-aware and accesses, uses, and manages this information, which is stored in a repository called the SQL Management Base (SMB).
The plan history enables the optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. A new plan represents a plan change that has the potential to cause performance regression. For this reason, the 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.
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.
Creating SQL plan baselines from STS
If you are upgrading you might already have an STS containing some or all of your SQL statements. This STS might contain plans that perform satisfactorily. Let’s call this STS MY_STS. You can create a SQL plan baseline from this STS as follows:
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.
Creating SQL plan baselines from cursor cache
You can automatically create SQL plan baselines for any cursor that is currently in the cache as follows:
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.
Creating SQL plan baselines using a staging table
If you already have SQL plan baselines, you can export them to another system.
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.
Creating SQL plan baselines automatically
You can create SQL plan baselines for all repeatable statements automatically by setting the parameter optimizer_capture_sql_plan_baselines to TRUE (default is FALSE). The first plan captured for any statement is automatically accepted and becomes part of the SQL plan baseline, so enable this parameter only when you are sure that the default plans are performing well.
You can use the automatic plan capture mode when you have upgraded from a previous database version. Set optimizer_features_enable to the earlier version and execute your workload. Every repeatable statement will have its plan captured thus creating SQL plan baselines. You can reset optimizer_features_enable to its default value after you are sure that all statements in your workload have had a chance to execute.
Note that this automatic plan capture occurs only for repeatable statements, that is, statements that are executed at least twice. Statements that are only executed once will not benefit from SQL plan baselines since accepted plans are only used in subsequent hard parses.
The following example shows a plan being captured automatically when the same statement is executed twice:
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
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.
Links
For more: