Controlling Execution Plan Evolution Using SQL Plan Management

Purpose

This tutorial describes how you can manage your SQL execution plans in Oracle Database 11g.

Time to Complete

Approximately 30 minutes

Topics

This tutorial discusses the following:

 Overview
 Prerequisites
 Capturing Plans Automatically
 Capturing Plans in Bulk
 Managing Plans
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so, depending on your Internet connection, may result in a slow response time.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Potential performance risks can occur when the execution plan changes for a SQL statement. A plan change can occur due for a variety of reasons including but not limited to the following types of changes occurring in the system: optimizer version, optimizer statistics, optimizer parameters, schema/metadata definitions, system settings, as well as SQL profile creation. Sometimes a change in the system (e.g. drop an index) causes an irreversible plan change. Not being able to guarantee a plan will change for the good has caused some customers to freeze their execution plans or their optimizer statistics.

In Oracle Database 11g a new feature SQL Plan Management (SPM) is introduced, which provides controlled execution plan evolution. With SPM, the optimizer automatically manages execution plans and ensures only known or verified plans are used. When a new plan is found for a SQL statement it will not be used until it has been verified to have comparable or better performance than the current plan.

Back to Topic List

Prerequisites

Before starting this tutorial, you should first complete the following steps:

1.

Install Oracle Database 11g.

2.

Download and unzip the spm.zip file into a working directory.

Back to Topic List

Capturing Plans Automatically

Automatic plan capture can be switched on by setting the init.ora parameter optimizer_capture_sql_plan_baselines to true. When automatic plan capture is on the SPM repository will be automatically populated for any repeatable SQL statement. To identify repeatable SQL statements the optimizer will log the identity of each SQL statement into a statement log, the first time it is compiled. After a SQL statement.s identity has been logged, if it is processed again (i.e. executed or compiled) the presence of its identity in the statement log will signify it to be a repeatable statement. A SQL plan history will be created and the current or cost-base plan will be added as the first plan baseline. Perform the following steps:

1.

Open Enterprise Manager DB Control and login as the system user.

Move your mouse over this icon to see the image

 

2.

Select the Server tab.

Move your mouse over this icon to see the image

 

3.

Under Query Optimizer, select SQL Plan Control.

Move your mouse over this icon to see the image

 

4.

Select the SQL Plan Baseline tab.

Move your mouse over this icon to see the image

 

5.

Under Settings, select the link FALSE for Capture SQL Plan Baselines.

Move your mouse over this icon to see the image

 

6.

For the parameter optimizer_capture_sql_plan_baseline select TRUE for the value and click OK.

Move your mouse over this icon to see the image

 

7.

The parameter is now set to true.

Move your mouse over this icon to see the image

 

8.

Open a SQL*Plus session and login as SYSTEM. Since this is the first time the SQL statement has been run and it is not yet repeatable and there is no plan baseline yet, you need to execute the following query 2 times.

@load_auto_select.sql

Move your mouse over this icon to see the image

 

9.

Switch to Enterprise Manager and enter %LOAD_AUTO% in the SQL Text field and click Go.

Move your mouse over this icon to see the image

 

10.

Your plan is now in the list. Select the SQL Text link to view details.

Move your mouse over this icon to see the image

 

11.

The SQL Plan Baseline details are displayed. Notice that the Origin is set to AUTO_CAPTURE because you loaded the baselines automatically. Click Return.

Move your mouse over this icon to see the image

 

12.

Now you want to change or alter the optimizer mode, which triggers the SQL statement to execute with a different plan. Switch to SQL*Plus and change the optimizer mode. Execute the following command.

alter session set optimizer_mode = first_rows;

Move your mouse over this icon to see the image

 

13.

Rerun the following query.

@load_auto_select.sql

Move your mouse over this icon to see the image

 

14.

Since the SQL statement will have a new plan another plan baseline is automatically captured. You can confirm this by checking the plan baseline. Switch back to DB Control and click Go.

Move your mouse over this icon to see the image

 

15.

You now see two plan baselines for the SQL query but you notice that the second plan has not been accepted. This new plan needs to be validated before it is acceptable as a good plan to use. Select checkbox in front of the plan that was not accepted and click Evolve.

Move your mouse over this icon to see the image

 

16.

In this case we are not concerned if this new plan can perform as good as or better then the current SQL plan baseline so you want to select No for Verify Performance. This means that the plan will become an accepted SQL plan baseline regardless of its performance. Then Click OK.

Move your mouse over this icon to see the image

 

17.

The job was submitted. Click Go again to refresh the list.

Move your mouse over this icon to see the image

 

18.

The plan is now accepted. To review the explain plan, select the SQL Text link.

Move your mouse over this icon to see the image

 

19.

This plan performs a bitmap index full scan. Click Return.

Move your mouse over this icon to see the image

 

20.

You can now reset the optimizer mode to the default values and disable auto capture of plan baselines. Switch to your SQL*Plus session and run the following command.

alter session set optimizer_mode = all_rows;

Move your mouse over this icon to see the image

 

21.

Switch back to DB Control. Select the TRUE link for Capture SQL PLAN Baselines.

Move your mouse over this icon to see the image

 

22.

For the parameter optimizer_capture_sql_plan_baseline select FALSE for the value and click OK.

Move your mouse over this icon to see the image

 

23.

The parameter has been changed.

Move your mouse over this icon to see the image

 

24.

You can now drop the SQL baselines. Select the checkbox in front of each baseline and click Drop.

Move your mouse over this icon to see the image

 

25.

Click Yes to confirm the drop.

Move your mouse over this icon to see the image

 

26.

Your baselines have been dropped.

Move your mouse over this icon to see the image

 

Back to Topic List

Capturing Plans in Bulk

There are two ways to capture plans in bulk:

Using a SQL Tuning Set (STS)

 From the Cursor Cache

Back to Topic List

Using a SQL Tuning Set (STS)

You can use a SQL Tuning set to Capture plan details for a (critical) set of SQL Statements. This is one way to ensure there will be no unwanted plan changes when upgrading from 10g to 11g. Perform the following steps:

1.

The first task you need to perform is to check the explain plan. Switch to your SQL*Plus session and execute the following script:

@load_sts_explain_plan

Move your mouse over this icon to see the image

 

2.

Then execute the SQL Statement using the following script:

@load_sts_select

Move your mouse over this icon to see the image

 

3.

Now you can alter the optimizer mode by executing the following command:

alter session set optimizer_mode = first_rows;

Move your mouse over this icon to see the image

 

4.

Now re-execute the following scripts to get a different plan.

@load_sts_explain_plan
@load_sts_select

Move your mouse over this icon to see the image

 

5.

Then reset the optimizer mode by executing the following command:

alter session set optimizer_mode = all_rows;

Move your mouse over this icon to see the image

 

6.

At this point you are ready to create a SQL Tuning Set and load the plan. From your SQL*Plus session, execute the following script to create the SQL Tuning set.

@cr_sts

Move your mouse over this icon to see the image

 

7.

Now you can populate the SQL Tuning set with the SQL you just ran. Execute the following script to populate the SQL Tuning set:

@populate_sts

Move your mouse over this icon to see the image

 

8.

Switch to Enterprise Manager. Now you are ready to load the plan based on the SQL Tuning set you just created. From the SQL Control Plan > SQL Plan Baseline tab, click Load.

Move your mouse over this icon to see the image

 

9.

Select the flashlight for SQL Tuning Set.

Move your mouse over this icon to see the image

 

10.

Select the Tuning Set SPM_STS and click Select.

Move your mouse over this icon to see the image

 

11.

Click OK to create the baseline job.

Move your mouse over this icon to see the image

 

12.

The baseline was created. Click Refresh.

Move your mouse over this icon to see the image

 

13.

Enter %LOAD_STS% in the SQL Text field and click Go.

Move your mouse over this icon to see the image

 

14.

Select the SQL text link

Move your mouse over this icon to see the image

 

15 .

The explain plan is displayed. This time the Origin is set to MANUAL-LOAD because of the way the plans were created. Click Return.

Move your mouse over this icon to see the image

 

16.

You can now drop the SQL baselines. Select the checkbox in front of each baseline and click Drop.

Move your mouse over this icon to see the image

 

17.

Click Yes to confirm the drop.

Move your mouse over this icon to see the image

 

18.

Your baselines have been dropped.

Move your mouse over this icon to see the image

 

Back to Topic

From the Cursor Cache

You can load plans straight from the cursor cache using dbms_spm.load_plans_from_cursor_cache. It is possible to load plans for all of the statements in the cursor cache or you can apply a filter on the module name or schema name, etc. The next time these statements are executed their baseline plans will be used. Perform the following steps:

1.

The first task you need to perform is to check the explain plan. Switch to your SQL*Plus session and execute the following script:

@load_cc_explain_plan

Move your mouse over this icon to see the image

 

2.

Then execute the SQL Statement using the following script:

@load_cc_select

Move your mouse over this icon to see the image

 

3.

Now you can alter the optimizer mode by executing the following command:

alter session set optimizer_mode = first_rows;

Move your mouse over this icon to see the image

 

4.

Now re-execute the following scripts to get a different plan.

@load_cc_explain_plan
@load_cc_select

Move your mouse over this icon to see the image

 

5.

Then reset the optimizer mode by executing the following command:

alter session set optimizer_mode = all_rows;

Move your mouse over this icon to see the image

 

6.

Now that the cursor cache is populated you need to get the sql id for your SQL statement which will be used to filter the content of the cursor cache when you load the baselines. Execute the following script:

@load_cc_get_sql_id

Move your mouse over this icon to see the image

 

7.

Now, you can load the plan and create the baseline. Switch to Enterprise Manager DB Control. From the Server > SQL Plan Control > SQL Plan Baseline page, click Load.

Move your mouse over this icon to see the image

 

8.

Select the radio button Load plans from cursor cache and select the flashlight for SQL id.

Move your mouse over this icon to see the image

 

9.

Enter %LOAD_CC% for the SQL text and click Go.

Move your mouse over this icon to see the image

 

10.

Select the checkbox in front of the SQL id that you saw in the previous step when you queried the cursor cache in SQL*Plus and click Select.

Move your mouse over this icon to see the image

 

11.

Click OK.

Move your mouse over this icon to see the image

 

12.

The job was submitted. Enter %LOAD_CC% for SQL Text and click Go.

Move your mouse over this icon to see the image

 

13.

Select the link for your SQL statement.

Move your mouse over this icon to see the image

 

14.

The explain plan is shown. Click Return.

Move your mouse over this icon to see the image

 

15.

You can now drop the SQL baselines. Select the checkbox in front of each baseline and click Drop.

Move your mouse over this icon to see the image

 

16.

Click Yes to confirm the drop

Move your mouse over this icon to see the image

 

17.

Your baselines have been dropped.

Move your mouse over this icon to see the image

 

Back to Topic

Managing Plans

Each time a SQL Statement is compiled, the optimizer first uses the traditional cost-based search method to build a best-cost plan. If the initialization parameter OPTIMIZER_USE_PLAN_BASELINES is set to TRUE (default value) it will then try to find a patching plan in the SQL plan baseline. If a match is found then it proceeds as usual. Otherwise, it first adds the new plan to the plan history, and then it costs each of the accepted plans in the SQL plan baseline and picks the one with the lowest cost. Perform the following steps:

1.

The first task you need to perform is to check the explain plan. Switch to your SQL*Plus session and execute the following script:

@use_opm_explain_plan

Move your mouse over this icon to see the image

 

2.

Then execute the SQL Statement using the following script:

@use_opm_select

Move your mouse over this icon to see the image

 

3.

Now you can alter the optimizer mode by executing the following command:

alter session set optimizer_mode = first_rows;

Move your mouse over this icon to see the image

 

4.

Now re-execute the following scripts to get a different plan.

@use_opm_explain_plan
@use_opm_select

Move your mouse over this icon to see the image

 

5.

Then reset the optimizer mode by executing the following command:

alter session set optimizer_mode = all_rows;

Move your mouse over this icon to see the image

 

6.

Now that the cursor cache is populated you need to get the sql id for your SQL statement which will be used to filter the content of the cursor cache when you load the baselines. Execute the following script:

@use_opm_get_sql_id

Move your mouse over this icon to see the image

 

7.

Now, you can load the plan and create the baseline. Switch to Enterprise Manager DB Control. From the Server > SQL Plan Control > SQL Plan Baseline page, click Load.

Move your mouse over this icon to see the image

 

8.

Select the radio button Load plans from cursor cache and select the flashlight for SQL id.

Move your mouse over this icon to see the image

 

9.

Enter %USE_OPM% for the SQL text and click Go.

Move your mouse over this icon to see the image

 

10.

Select the checkbox in front of the SQL id that you saw in the previous step when you queried the cursor cache in SQL*Plus and click Select.

Move your mouse over this icon to see the image

 

11.

Click OK.

Move your mouse over this icon to see the image

 

12.

The job was submitted. Enter %USE_OPM% for SQL Text and click Go. The SQL Plan Baselines are shown.

Move your mouse over this icon to see the image

 

13.

Now that the plans are loaded you want to check that it is being used. Switch to your SQL*Plus session and execute the following script:

@use_opm_explain_plan2

Move your mouse over this icon to see the image

The note at the end of the explain output indicates that a baseline is being used. From the execution plan you see that the first baseline, a full table scan, is being used.

 

14.

You can switch to use the other baseline by change the optimizer mode back to first_rows. Execute the following command:

alter session set optimizer_mode = first_rows;

Move your mouse over this icon to see the image

 

15.

Rerun the explain plan again to see the other baseline. Execute the following script:

@use_opm_explain_plan2

Move your mouse over this icon to see the image

From the execution plan you see that the second baseline, a bitmap index full scan, is being used.

 

16.

At this point, you want to disable that plan baseline, and check that it will use other plan baseline. Switch to Enterprise Manager and select the checkbox in front of the plan that was shown in the explain plan from the previous step. The click Disable.

Move your mouse over this icon to see the image

 

17.

The plan has been disabled.

Move your mouse over this icon to see the image

 

18.

Switch to your SQL*Plus session and execute the following script again:

@use_opm_explain_plan2

Move your mouse over this icon to see the image

From the execution plan, you see the plan being used is the index scan instead of the full table scan so this is the second baseline.

 

19.

The last task you want to perform is to disable this other plan baseline. The optimizer will then fall back to the cost-based approach. Switch to Enterprise Manager and select the checkbox in front of the plan that was shown in the explain plan from the previous step. The click Disable.

Move your mouse over this icon to see the image

 

20.

Both plans are now disabled.

Move your mouse over this icon to see the image

 

21.

Switch to your SQL*Plus session and execute the following script one last time:

@use_opm_explain_plan2

Move your mouse over this icon to see the image

The optimizer is now using the default cost based approach as there is no note at the end of the plan stating a baseline was used.

 

22.

You can now drop the SQL baselines. Select the checkbox in front of each baseline and click Drop.

Move your mouse over this icon to see the image

 

23.

Click Yes to confirm the drop

Move your mouse over this icon to see the image

 

24.

Your baselines have been dropped.

Move your mouse over this icon to see the image

 

Back to Topic List

Summary

In this tutorial, you've learned how to:

 Capture plans automatically
 Capture plans using a SQL Tuning Set or SQL ID
 Select a plan to use

Back to Topic List

 Move your mouse over this icon to hide all screenshot

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document