C H A P T E R  12

Execution Plan Optimization and Consistency

An execution plan describes how Oracle will retrieve the data to satisfy the results of a query. When you submit a SQL statement, the query optimizer quickly produces several execution plans and will determine which plan is most efficient. In most scenarios, the prior behavior results in a well-performing execution plan. However, you will encounter situations where you know additional details about your environment and need to adjust the optimizer's choice of an execution plan. Listed next are features you can use to influence the decision path the optimizer uses when selecting a plan:

• Initialization parameters
• Statistics
• Hints
• SQL profiles
• SQL plan management (plan baselines)
• Stored outlines (deprecated in favor of plan baselines)

It's critical you understand how these features affect the optimizer's choice of an execution plan. When troubleshooting SQL performance problems, you must determine which of the prior features are enabled and how they influence query behavior. The performance of a SQL statement can vary drastically depending on which feature is implemented and the impact of the various combinations of features.

Initialization parameters (that impact the optimizer) and statistics gathering are detailed in Chapter 13. Using hints is the emphasis of Chapter 14. The focus of this chapter is SQL profiles and plan baselines.

SQL profiles are optionally generated corrections and improvements to statistics. The recommendation (and code) to implement a SQL profile is manifested through the output of the SQL Tuning Advisor. You can manually enable SQL profiles or configure them to be automatically accepted. SQL profiles help the optimizer derive better execution plans.

SQL plan management allows you to store and manage execution plans within tables in the database. Plan baselines consist of one or more stored execution plans that have been accepted for a SQL query. When you run a query, and if a plan baseline exists for the query, the optimizer will give precedence to execution plans within the plan baseline. Plan history is the super set of both accepted and unaccepted execution plans for a query. You can manually change the state of an unaccepted plan to accepted (this moves it to the plan baseline). This is known as evolving a plan baseline.

Plan baselines help ensure that the optimizer consistently chooses the same execution plan, regardless of changes in the database environment. Plan baselines provide the following benefits:

• Preserving performance when upgrading from one database version to another; in other words, helping ensure that the same execution plan is used for a query before and after the upgrade
• Keeping performance stable and consistent when data changes, or statistics are updated, or new SQL profiles become available
• Providing a mechanism for accepting more efficient executions plans as they become available (like a new index is added or a SQL profile becomes available)

Figure 12-1 displays the flow of choices that the optimizer makes when choosing an execution plan. Please take a few minutes to analyze this diagram and ensure you grasp how the various features influence the optimizer's behavior. As you view the diagram, keep in mind the following:

• Hints are the only feature that requires a physical modification to the SQL query. All of the other techniques can be used to improve performance without changing the query.
• Initialization parameters, statistics, hints, SQL profiles, and plan baselines can all operate independently of each other. No one feature is dependent on the existence of another feature.
• The optimizer works fine with out-of-the-box settings. You don't need any of these features (hints, SQL profiles, and so on) to be explicitly enabled. However, to get the maximum performance from SQL queries, we highly recommend you know when and how to use these features to help the query optimizer make optimal decisions.

As you look at the skep-shaped diagram, to help understand how the optimizer chooses between the low-cost plan and a plan baseline plan, consider the general steps taken when formulating an execution plan:

1. The optimizer first considers initialization parameters, hints, and SQL profiles when choosing the lowest-cost plan.
2. Regardless of the plan arrived at in step 1, if a plan baseline exists for the query, the optimizer will choose the lowest-cost plan from the plan baseline. Additionally, the optimizer will give preference to plans that have a fixed state in the plan baseline.
3. If the accepted plans in the plan baseline are not reproducible (say an index has been dropped that all of the plan baseline plans depend on), then the optimizer chooses the lowest-cost plan generated in step 1. Lowest cost in this situation means using the least amount of database resources such as CPU, I/O, and memory.
4. If a plan baseline exists for a query, and if the low-cost plan (from step 1) has a lower cost than the plan from the plan baseline, then the low-cost plan is automatically added to the plan history for the query in an unaccepted state. You can choose to move plans from the plan history into the plan baseline so that the optimizer will consider them when choosing an execution plan. This provides you the flexibility to use better plans as they become available (evolving the plan).

Figure 12-1. Oracle database features influencing optimizer's choice of execution plan

Features such as initialization parameters and hints don't require an extra license and are available with all editions of the Oracle database. Other features such as SQL profiles require an extra license and ship only with the Enterprise Edition. Table 12-1 summarizes the characteristics of each query optimizer-influencing feature.

The first part of this chapter focuses on managing SQL profiles. The rest of the chapter deals with the implementation and use of plan baselines. We describe practical and real-world examples of the use of these tools. Where appropriate, we also have added instructions on how to use a given feature via Enterprise Manager.

12-1. Creating and Accepting a SQL Profile

Problem

You have a poorly performing query, and you want to get advice from the SQL Tuning Advisor. You realize that the SQL Tuning Advisor may recommend that a SQL profile be applied to the problem query as part of the tuning recommendation.

Solution

Run the SQL Tuning Advisor for the problem query. Keep in mind that the SQL Tuning Advisor may or may not recommend a SQL profile as a solution for performance issues. To run the SQL Tuning Advisor manually, perform the following steps:

1. Use DBMS_SQLTUNE to create a tuning task.
2. Execute the tuning task.
3. Generate the tuning advice report.
4. If SQL profile is part of the tuning advice output, then create and accept.

The following example follows the prior steps. In this scenario, the SQL Tuning Advisor recommends that a SQL profile be applied to the given query.

Step 1: Use DBMS_SQLTUNE to Create a Tuning Task

The first step is to create a tuning task that is associated with the problem SQL statement. In the following code, the SQL text is hard-coded as input to the tune_sql variable:

The prior code is placed in a file named sqltune.sql, and executed as follows:

If you need to later drop the tuning task, you can use the DBMS_SQLTUNE.DROP_TUNING_TASK procedure. Obviously don't drop the tuning task at this point because you'll need it for the next several steps.

Step 2: Execute the Tuning Task

This step runs the SQL Tuning Advisor to generate advice regarding any queries associated with the tuning task (created in step 1):

Step 3: Run Tuning Advice Report

Now use DBMS_SQLTUNE to extract any tuning advice generated in step 2:

For this example, the SQL Tuning Advisor recommends creating a SQL profile. Here is a snippet from the output that contains the recommendation and the code required to create the SQL profile:

Step 4: Create and Accept SQL Profile

To actually create the SQL profile, you need to run the code recommended by the SQL Tuning Advisor (from step 3)—for example:

When the prior code is run, it creates and enables the SQL profile. Now whenever the associated SQL query is executed, the SQL profile will be considered by the optimizer when formulating an execution plan.

How It Works

The only Oracle-supported method for creating a SQL profile is to run the SQL Tuning Advisor and if recommended, create a SQL profile using the Tuning Advisor's output. In other words, the SQL Tuning Advisor determines if a SQL profile will help, and if so generates the code required to create a SQL profile for a given query.

The “Solution” section detailed how to manually run the SQL Tuning Advisor. Keep in mind that as of Oracle Database 11g, this tuning task job automatically runs on a regularly scheduled basis. See Chapter 11 for details on automatic SQL tuning features. You can easily review the output of the automatic tuning job via this query:

We recommend that you review the output of the automatic tuning job on a regular basis. The SQL Tuning Advisor will provide the code to create and accept SQL profiles as part of the output.

As noted in the “Solution” section, a SQL profile is created and accepted via the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure. There are many options available when using this procedure (see Table 12-2 for details).

The FORCE_MATCH parameter of ACCEPT_SQL_PROFILE requires further explanation. Recall that a SQL profile is associated with a SQL statement. The SQL statement is identified via a hash function (SQL signature). The hash function is generated after converting the SQL text and removing extra white space. When setting FORCE_MATCH to TRUE, this additionally normalizes literal values into bind values. This is similar to the algorithm generated via the FORCE option of the CURSOR_SHARING database initialization parameter.

For example, with FORCE_MATCH set to TRUE, the following two SQL statements will generate the same SQL signature:

This allows SQL statements that use literal values to share the same SQL profile. If there is a combination of literal values and bind variables in a SQL statement, then literal values are not normalized.

SQL PROFILE VS. DATABASE PROFILE

12-2. Automatically Accepting SQL Profiles

Problem

You realize that the Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher). You determine that the automatic tuning job generates reasonable SQL profiles for problematic queries and now want to enable the automatic acceptance of SQL profiles generated by the automatic tuning job.

Solution

Use the DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure to enable the automatic acceptance of SQL profiles recommended by the Automatic SQL Tuning task—for example:

If you want to disable the automatic acceptance of SQL profiles, then do so as follows (using the FALSE parameter):

How It Works

In Oracle Database 11g or higher, an automatically configured job runs the SQL Tuning Advisor on a periodic basis (determined by a configured maintenance window). This job identifies high resource-consuming SQL statements from performance metrics contained in the AWR. When the automatic tuning job runs, it will occasionally recommend that a SQL profile be implemented for a poorly performing SQL statement. Oracle will automatically accept the profile if the following conditions are true:

• Automatic acceptance has been configured via DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER.
• With the SQL profile, the performance gain is determined (by the SQL Tuning Advisor) to be at least three times more (than without the profile).

You can report on the details of the automatic tuning task configuration via this query:

Here is some sample output:

You can also use Enterprise Manager to configure the automatic acceptance of SQL profiles. From the main database page, navigate to the Advisor Central page. Next, click the SQL Advisors link. Now click the Automatic SQL Tuning Results page. Next click the configure button of Automatic Implementation of SQL Profiles. You should see a page similar to Figure 12-2.

Figure 12-2. Configuring automatic acceptance of SQL profiles

From this screen, you can manage features such as the automatic acceptance of SQL profiles, maximum time for a tuning session, and so on.

12-3. Displaying SQL Profile Information

Problem

You have created and accepted several SQL profiles and now want to view information related to these database objects.

Solution

Use the DBA_SQL_PROFILES view to display information about SQL profiles. Here's an example that selects the most interesting columns:

Here is a snippet of the output:

For this database, there are several manually enabled SQL profiles (as shown in the prior output).

How It Works

Recall that a SQL profile contains improvements to existing statistics. The DBA_SQL_PROFILES view is the best source for viewing the SQL profile name, attributes, and associated SQL text.

To view the internal SQL profile hint-related information, you can additionally query the DBMSHSXP_SQL_PROFILE_ATTR view—for example:

Here is some sample output:

The prior output gives you an indication of the types of hints within a SQL profile. This information is used by the optimizer to better estimate the cardinality of each execution step. This data allows the optimizer to make better decisions when generating an execution plan.

You can also view this internal SQL profile information by querying the SQLOBJ$ and SQLOBJ$DATA views. The data in these views is in XML format, and therefore you must format the output with Oracle XML functions when querying—for example:

Here is a small sample of the output:

Again, these profile statistics don't force the optimizer to use a certain execution plan. Rather these statistics provide the optimizer the flexibility to choose a more efficient execution plan.

12-4. Disabling a SQL Profile

Problem

You think that a SQL profile is no longer required for a query. You want to manually disable (not drop) the SQL profile.

Solution

First verify the name of the SQL profile that you want to disable:

Here's a partial snippet of the output:

Now use the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure to modify the status of the profile to disabled:

How It Works

The status of a SQL profile is one of several modifiable attributes. You can also modify characteristics such as the name, description, and category. See Table 12-3 for a description of the modifiable attributes.

The category of a SQL profile has some interesting implications. A category allows you to control the use of a SQL profile through the setting of the SQLTUNE_CATEGORY initialization parameter (this parameter can be set at the session or system level). When a query is executed, if a SQL profile is available, the optimizer will check to ensure that the category assigned to the SQL profile is the same as the system- or session-level setting of SQLTUNE_CATEGORY. If the category of the SQL profile matches the setting of SQLTUNE_CATEGORY, then the optimizer will consider using the SQL profile.

The default category for a SQL profile is DEFAULT. Also the default value for SQLTUNE_CATEGORY is DEFAULT. Therefore, unless you alter the SQL profile category or modify the SQLTUNE_CATEGORY parameter, the SQL profile will be used as input by the optimizer.

You can alter the category to something other than DEFAULT. This means that only sessions that modify the initialization parameter of SQLTUNE_CATEGORY to the value of the category for the SQL profile will be able to use the profile. For example, say you modify the SQL profile to have a category of TEST1:

Now the only sessions that can see and use the profile are those that have SQLTUNE_CATEGORY set to TEST1:

This allows you to isolate a profile's use to only those sessions that have the SQLTUNE_CATEGORY set to match the category of the SQL profile. This allows you to test the impact of implementing a SQL profile and back it out quickly, simply by altering either the session-level or system-level setting of SQLTUNE_CATEGORY.

You can also manage many aspects of a SQL profile from Enterprise Manager. From the main database page, navigate to the Server tab. In the Query Optimizer section, click the SQL Plan Control tab. You should be presented with a screen similar to Figure 12-3.

Figure 12-3. Managing SQL profiles

From this screen, you can manage features such as enabling, disabling, changing the category, and dropping a SQL profile.

12-5. Dropping a SQL Profile

Problem

You've tested a query with and without a SQL profile attached to the query. You determine the query performance is not significantly better with the SQL profile. You want to drop the SQL profile so you're not cluttering up the data dictionary with unnecessary and obsolete information.

Solution

Use the DBMS_SQLTUNE.DROP_SQL_PROFILE procedure to drop a SQL profile. Pass in the name of the SQL profile you want to drop—for example:

If successful, you should see the following:

How It Works

It's fairly easy to drop a SQL profile. You might want to do this if you're cleaning up a database or if you want to remove profiles from a testing environment. If you're unsure of the SQL profile name, you can query DBA_SQL_PROFILES for more information (see Recipe 12-3 for details).

If you want to drop all profiles in a database, you can use PL/SQL to loop through all profiles and drop them:

12-6. Moving a SQL Profile

Problem

You have a test database and want to extract all of the SQL profiles from the test database and move them to a production database.

Solution

Listed next are the steps involved with transporting a SQL profile from one database to another:

1. Create a staging table.
2. Populate the staging table.
3. Move the table from the source database to the destination database (Data Pump or database link).
4. On the destination database, extract information from the staging table to populate the data dictionary with SQL profile information.

These steps are detailed in the following subsections.

Step 1: Create a Staging Table

Use the DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF procedure to create the staging table. This example creates a table named PROF_STAGE owned by the MV_MAINT user:

Step 2: Populate the Staging Table

Use the DBMS_SQLTUNE.PACK_STGTAB_SQLPROF procedure to populate the table created in step 1 with SQL profile information. This example populates the table with information regarding a specific SQL profile:

Step 3: Copy the Staging Table to the Destination Database

You can copy the table from one database to the other via Data Pump, the old exp/imp utilities, or by using a database link. This example creates a database link in the destination database and then copies the table from the source database:

Once the database link has been created, the table can be copied directly from the source with the CREATE TABLE...AS SELECT statement:

Step 4: Load the Contents of the Staging Table into the Destination Database

Now in the destination database, unpack the table to load profile information into the database:

If no profile name is specified, the default is the % wildcard character (meaning all profiles in the table will be loaded into the destination database).

How It Works

It's fairly easy to copy SQL profiles from one database to another. You simply have to create a special table to hold the profile information, then populate the table, copy the table to the destination database, and lastly unpack the table's contents. Table 12-4 describes all of the parameters for the profile packing procedure.

The DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF procedure takes the same parameters as the packing procedure with an additional REPLACE parameter. The REPLACE parameter specifies whether to replace profiles if they already exist (can be TRUE or FALSE).

12-7. Automatically Adding Plan Baselines

Problem

You want to automatically create plan baselines for every SQL query that repeatedly executes in your database.

Solution

Listed next are the steps for automatically creating plan baselines for SQL statements that execute more than once:

1. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE (either at the session or system level).
2. Execute two times or more the queries for which you want plan baselines captured.
3. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.

This next example illustrates the process for adding a plan baseline (for a query) using the prior steps. First set the specified initialization parameter at the session level:

Now a query is executed twice. Oracle will automatically create a plan baseline for a query that is run two or more times while the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE:

Now set the initialization parameter back to FALSE.

The query now should have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:

Here is a partial listing of the output:

How It Works

Enabling OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES allows you to automatically capture plan baselines for queries running repeatedly (more than once) in your database. The “Solution” section described how to use this feature at the session level. You can also set the parameter so that all repeating queries in the database have plan baselines generated—for example:

From this point, any query in the database that runs more than once will automatically have a plan baseline created for it. We wouldn't recommend that you do this in a production environment unless you have first carefully tested this feature and ensured that there will be no adverse side effects (from storing a plan baseline for every query). However, you may have a test environment where you want to purposely create a plan baseline for every SQL statement that is repeatedly run.

You can also manage the use of plan baselines from Enterprise Manager. From the main database page, navigate to the Server tab. In the Query Optimizer section, click the SQL Plan Control tab. Next, click the SQL Plan Baseline tab. You should see a screen similar to Figure 12-4.

Figure 12-4. Managing plan baselines

From this screen, you can manage tasks such as enabling, disabling, dropping, and evolving plan baselines.

12-8. Creating a Plan Baseline for One SQL Statement

Problem

You want to create a plan baseline for a specific SQL statement that you're currently executing.

Solution

The procedure for manually associating a plan baseline with a SQL statement is as follows:

1. Identify the SQL statement(s) for which you want plan baselines.
2. Provide an identifier such as the SQL_ID as input to the DBMS_SPM package to create a plan baseline for the SQL statement.

For example, suppose you have a SQL statement you've been working with such as the following:

Now query the V$SQL view to determine the SQL_ID for the query:

Here is a snippet of the output:

Now that the SQL_ID has been identified, use it as input to the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function to create a plan baseline for the given query—for example:

The query now should have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:

Here's a small snippet of the output:

How It Works

The “Solution” section described how to identify a single SQL statement for which you want to create a plan baseline (based on the SQL_ID) using a query in the cursor cache. There are many methods for creating a plan baseline for a query, such as using the SQL text, schema, module, and so on. For example, next a plan baseline is loaded based on a partial SQL string:

See Table 12-5 for details on input parameters available with the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function.

12-9. Creating Plan Baselines for SQL Contained in SQL Tuning Set

Problem

You have the following scenario:

• You're upgrading a database to a new version.
• You know from past experience that upgrading to newer versions of Oracle can sometimes cause SQL statements to perform poorly because the optimizer in the upgraded version of the database is choosing a less efficient (worse) execution plan than the optimizer from the prior version of the database.
• You want to ensure that SQL statements execute with acceptable performance after the upgrade.

In essence, you are upgrading and would prefer that the optimizer choose the same execution plans both before and after the upgrade. You don't want the upgrade to result in new plans that risk degrading performance.

Solution

To deal with this problem, use the most resource-intensive SQL queries in the AWR as candidates for the creation of plan baselines. This solution uses the technique of creating an AWR baseline. An AWR baseline is a snapshot of activity in the AWR designated by begin/end snapshot IDs. Listed next are the steps for creating and populating a SQL tuning set with high resource-consuming SQL statements found in an AWR baseline and then creating plan baselines for those queries:

1. Create an AWR baseline.
2. Create a SQL tuning set object.
3. Populate the SQL tuning set with the queries found in the AWR baseline.
4. Use the tuning set as input to DBMS_SPM to create a plan baseline for each query contained in the SQL tuning set.

Step 1: Create an AWR Baseline

The first step is to create an AWR baseline. For example, suppose you knew you had high-load queries running between two snapshots in your database. The following creates an AWR baseline using two snapshot IDs:

If you're unsure of the available snapshots in your database, you can run an AWR report or select the SNAP_ID from DBA_HIST_SNAPSHOTS:

Step 2: Create a SQL Tuning Set Object

Now create a SQL tuning set. This next bit of code creates a tuning set named test1:

Step 3: Populate the SQL Tuning Set with High-Resource Queries Found in AWR Baseline

Now the SQL tuning set (created in step 2) is populated with any queries found within the AWR baseline (created in step 1):

In the prior lines of a code, the AWR baseline name is passed to the DBMS_SQLTUNE package. The queries within the baseline are select by the elapsed time, and the top 15 are specified.

Step 4: Use the Tuning Set As Input to DBMS_SPM to Create Plan Baselines for Each Query Contained in the SQL Tuning Set

Now the tuning set (populated in step 3) is provided as input to the DBMS_SPM package:

Any queries contained in the tuning set should now have entries in the DBA_SQL_PLAN_BASELINES view.

How It Works

The technique shown in the “Solution” section is a very powerful method for creating plan baselines for the most resource-consuming queries running in your database. The key to this recipe is understanding that you can use as input (to the DBMS_SPM package) queries contained in a SQL tuning set. A SQL tuning set can be populated from high resource-consuming statements found in the AWR and memory. This allows you to easily create plan baselines for the most problematic queries.

Having plan baselines in place for resource-intensive queries helps ensure that the same execution plan is used after there are changes to your system, such as a database upgrades, changes in statistics, different data sets, and so on.

Keep in mind that it's possible to have more than one accepted execution plan within the plan baseline. If you have a specific plan that you want the optimizer to always use, then consider altering the plan to a FIXED state. See Recipe 12-10 for details on altering a plan baseline to a FIXED state.

12-10. Altering a Plan Baseline

Problem

You have several accepted plan baseline execution plans for one query. You want to specifically instruct the optimizer to give preference to one of the accepted plans.

Solution

The optimizer will give preference to plan baselines with a FIXED state. Use the DBMS_SPM package and ALTER_SQL_PLAN_BASELINE function to alter a current plan baseline execution plan to FIXED. Here's an example:

You can query the FIXED column of DBA_SQL_PLAN_BASELINES to verify that it is now baseline-neutered. Listed next is such a query:

Here is some sample output:

How It Works

You can think of fixing a plan baseline as a way of establishing a preference hierarchy for how the optimizer chooses a plan baseline. The optimizer will give first priority to any accepted and fixed plan baselines. If none is available, then accepted non-fixed plan baselines are considered. Execution plans that are added to a plan baseline that already contains a fixed plan baseline will be considered secondary (unless you add them as fixed).

Table 12-6 describes the parameters available with ALTER_SQL_PLAN_BASELINE. You can specify either the SQL_HANDLE or PLAN_NAME or both. If the SQL_HANDLE is NULL, then a PLAN_NAME must be specified and vice versa.

The ATTRIBUTE_NAME and ATTRIBUTE_VALUE parameters consist of a name/value pairing that can be used to alter various attributes of a plan baseline. See Table 12-7 for a complete description of the possible pairings.

12-11. Determining If Plan Baselines Exist

Problem

You recently implemented a plan baseline for a query. You want to verify the configuration of a plan baseline.

Solution

Run the following query to view details regarding any plan baselines that have been configured:

The output from the prior query is very wide and has been modified to fit within the page width:

In the output, there are two key columns: the SQL_HANDLE and PLAN_NAME. Each query has an associated SQL_HANDLE that is an identifier for a query. Each execution plan has a unique PLAN_NAME. The PLAN_NAME will be unique within DBA_SQL_PLAN_BASELINES, whereas there could be multiple rows with the same SQL_HANDLE (but different PLAN_NAME).

How It Works

The DBA_SQL_PLAN_BASELINES view provides a quick and easy way to determine if plan baselines exist and are in use. If a plan is enabled and accepted, then the query has a plan baseline in use.

If you have doubts about whether a plan baseline is being considered by the optimizer, then set AUTOTRACE on and view the output—for example:

Here is a partial listing of the output indicating that a SQL plan baseline execution plan is used for this query:

12-12. Displaying Plan Baseline Execution Plans

Problem

You want to quickly view details regarding an existing plan baseline, such as the associated execution plan.

Solution

Use the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function to display the execution plan and corresponding plan baseline details. This example reports details for a specific plan:

Here is some sample output:

How It Works

The DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function allows you to display one or more execution plans in a plan baseline. The return type for this function is a PL/SQL table type. This function takes three parameters (described in Table 12-8).

If you want to display all plans for a SQL statement, then use as input the SQL_HANDLE parameter—for example:

Here is a partial listing of the output showing there are multiple plans in the plan baseline for this SQL query:

12-13. Adding a New Plan to Plan Baseline (Evolving)

Problem

You have the following scenario:

• You have an existing plan baseline for the query.
• You have recently added an index that the query can use.
• The optimizer determines a new lower-cost plan is now available for the query and adds the new plan to the plan history in an unaccepted state.
• You notice the new plan either from a recommendation by the SQL Tuning Advisor or by querying the DBA_SQL_PLAN_BASELINES view.
• You have examined the new execution plan, have run the query in a test environment, and are confident that the new plan will result in better performance.

You want to evolve the low-cost plan in the history so that it's moved to an accepted plan in the baseline. You realize that once the plan is accepted in the baseline, the optimizer will use it (if it's the lowest-cost plan in the baseline).

Solution

First verify that there are plans in the unaccepted state for the query in question (see Recipes 12-11 and 12-12 for more details). Here's a quick example:

Here is the output indicating there are two plans, one unaccepted but with a much lower cost:

Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to move a plan from the history to the baseline (evolve the plan). In this example, the SQL handle (unique SQL string associated with a SQL statement) is used to evolve a plan:

If Oracle determines that there is an unaccepted plan with a lower cost, then you'll see output similar to this indicating that the plan has been moved to the accepted state (evolved):

You can quickly verify that the new plan baseline is now in use by setting AUTOTRACE on and running the query—for example:

Here's a small snippet of the output indicating the new plan baseline is in use:

How It Works

One key feature of SQL plan management is that when a new low-cost plan is generated by the query optimizer, if the new low-cost plan has a lower cost than the accepted plan(s) in the plan baseline, the new low-cost plan will automatically be added to the query's plan history in an unaccepted state.

You can choose to accept this new low-cost plan, which then moves it into the plan baseline as accepted. Moving an unaccepted execution plan from the plan history to the plan baseline (ENABLED and ACCEPTED) is known as evolving the plan baseline.

Why would a new plan ever be generated by the optimizer? There are several factors that would cause the optimizer to create a new execution plan that doesn't match an existing one in the plan baseline:

• New statistics are available.
• A new SQL profile has been assigned to the query.
• An index has been added or dropped.

This gives you a powerful technique to manage and use new plans as they become available. You can use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function in the following modes:

• Specify the name of the plan to evolve.
• Provide a list of plans to evolve.
• Run it with no value, meaning that Oracle will evolve all non-accepted plans contained within the plan baseline repository.

Table 12-9 describes the parameters used in the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function.

12-14. Disabling Plan Baselines

Problem

You're working with a test database that has many SQL statements with associated plan baselines. You want to determine what the performance difference would be without the plan baselines enabled and therefore want to temporarily disable the use of plan baselines.

Solution

To disable the use of any SQL plan baselines within the database, set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to FALSE:

The prior line disables the use of the plan baselines at the SYSTEM level and records the value in memory and in the server parameter file. To re-enable the use of plan baselines, set the value back to TRUE.

You can also set the OPTIMIZER_USE_SQL_PLAN_BASELINES at the session level. This disables the use of plan baselines for the duration of the session for the currently connected user:

How It Works

The default value for OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE, which means by default, if plan baselines are available, they will be used. When enabled, the optimizer will look for a valid plan baseline execution plan for the given SQL query and choose the one with the lowest cost. This gives you a quick and easy way to disable/enable the use of plan baselines within your entire database or specific to a session.

If you want to disable the use of one specific plan baseline, then alter its state to DISABLED:

12-15. Removing Plan Baseline Information

Problem

You have several plan baselines that you no longer want to use and therefore want to remove them.

Solution

You can drop a single plan baseline. This removes a single plan baseline using the PLAN_NAME parameter:

You can also drop all plans associated with a SQL statement. This example removes all plans associated with a SQL statement using the SQL_HANDLE parameter:

How It Works

You may occasionally want to remove SQL plan baselines for the following reasons:

• You have old plans that aren't used anymore because more efficient plans (evolved) are available for a SQL statement.
• You have plans that were never accepted and now want to remove them.
• You have plans that were created for testing environments that are no longer needed.

As shown in the “Solution” section, you can remove a specific plan baseline via the PLAN_NAME parameter. This will remove one specific plan. If you have several plans associated with one SQL statement, you can remove all plan baselines for that SQL statement via the SQL_HANDLE parameter.

If you have a database where you want to clear out all plans, then you can encapsulate the call DBMS_SPM.DROP_SQL_PLAN_BASELINE within a PL/SQL block that drops all plans by looping through any plan found in DBA_SQL_PLAN_BASELINES:

12-16. Transporting Plan Baselines

Problem

You have a test environment, and you want to ensure that all of the plan baselines in the test system are moved to a production database.

Solution

Follow these steps to transport plan baselines:

1. Create a table using the DBMS_SPM package and CREATE_STGTAB_BASELINE procedure.
2. Populate the table with plan baselines using the DBMS_SPM.PACK_STGTAB_BASELINE function.
3. Copy the staging table to the destination database using a database link or Data Pump.
4. Import the plan baseline information using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.

This example first uses the DBMS_SPM package to create a table named EXP_PB:

Next the EXP_PB table is populated with plan baselines created by the database user MV_MAINT:

The prior code populates the table with all plan baselines created by a user. You can also populate the table by PLAN_NAME, SQL_HANDLE, SQL_TEXT, or various other criteria. The only mandatory parameter is the name of the table to be populated.

Now copy the staging table to the destination database. You can use a database link, Data Pump, or the old exp/imp utilities to accomplish this.

Lastly, on the destination database, use the DBMS_SPM.UNPACK_STGTAB_BASELINE function to take the contents of the EXP_PB table and create plan baselines:

You should now have all of the plan baselines transferred to your target database. You can query DBA_SQL_PLAN_BASELINES to verify this.

How It Works

It's a fairly easy process to create a table, populate it with plan baseline information, copy the table, and the import its contents into the destination database. As shown in step 2 of the “Solution” section of this recipe, the PACK_STGTAB_BASELINE function is used (see Table 12-10). This function allows quite a bit of flexibility in what types of plan baselines you want exported. You can limit the plan baselines extracted to a specific user, or enabled, or accepted, and so on.

Likewise, the DBMS_SPM.UNPACK_STGTAB_BASELINE function allows you a great deal of flexibility on what types of plan baselines are extracted from the staging table and loaded into the destination database. The input parameters for UNPACK_STGTAB_BASELINE are the same as the parameters used for PACK_STGTAB_BASELINE (described in Table 12-10).