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

Select the Server tab.

Under Query Optimizer, select SQL Plan Control.

Select the SQL Plan Baseline tab.

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

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

The parameter is now set to true.

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

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

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

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

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;

Rerun the following query.

@load_auto_select.sql

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.

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.

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.

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

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

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

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;

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

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

The parameter has been changed.

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

Click Yes to confirm the drop.

Your baselines have been dropped.

Using a SQL Tuning Set (STS)

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

Then execute the SQL Statement using the following script:

@load_sts_select

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

alter session set optimizer_mode = first_rows;

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

@load_sts_explain_plan
@load_sts_select

Then reset the optimizer mode by executing the following command:

alter session set optimizer_mode = all_rows;

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

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

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.

Select the flashlight for SQL Tuning Set.

Select the Tuning Set SPM_STS and click Select.

Click OK to create the baseline job.

The baseline was created. Click Refresh.

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

Select the SQL text link

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

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

Click Yes to confirm the drop.

Your baselines have been dropped.

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

Then execute the SQL Statement using the following script:

@load_cc_select

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

alter session set optimizer_mode = first_rows;

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

@load_cc_explain_plan
@load_cc_select

Then reset the optimizer mode by executing the following command:

alter session set optimizer_mode = all_rows;

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

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.

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

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

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.

Click OK.

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

Select the link for your SQL statement.

The explain plan is shown. Click Return.

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

Click Yes to confirm the drop

Your baselines have been dropped.

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

Then execute the SQL Statement using the following script:

@use_opm_select

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

alter session set optimizer_mode = first_rows;

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

@use_opm_explain_plan
@use_opm_select

Then reset the optimizer mode by executing the following command:

alter session set optimizer_mode = all_rows;

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

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.

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

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

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.

Click OK.

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

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

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.

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;

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

@use_opm_explain_plan2

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

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.

The plan has been disabled.

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

@use_opm_explain_plan2

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.

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.

Both plans are now disabled.

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

@use_opm_explain_plan2

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.

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

Click Yes to confirm the drop

Your baselines have been dropped.