oracle spm,Oracle 11g SPM Usage Guide

DBA Notes: 2011/11/01

Cheng Li

Oracle 11g SPM

Usage Guide

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

Capture Plan

using a SQL Tuning Set or SQL ID

1Using

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)Create a SQL Tuning Set and load the plan

SQL>exec

dbms_sqltune.create_sqlset(sqlset_name => ‘spm_sts’, sqlset_owner => ‘ORACLE’);

2)Populate the SQL Tuning set with theCriticalSQL

Use

dbms_sqltune.select_cursor_cache and dbms_sqltune.load_sqlset

2

Load from cursor

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.

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_SQL_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.

Maintain

Execution plan stability after migration

But

with this new 11g feature, if we have tried and trusted 10g execution plans in

place, we can ensure that the same plans will be used by the optimizer even

after the migration unless new 11g plans are found to offer better performance.

This can be achieved by capturing 10g plans in SQL tuning sets (STS) and

exporting those to the 11g database.

The Oracle

documentation describes the components of a SQL Tuning set as the following:

§A set of

SQL statements.

§Associated

execution context, such as user schema, application module name and action,

list of bind values, and the cursor compilation environment.

§Associated

basic execution statistics, such as elapsed time, CPU time, buffer gets, disk

reads, rows processed, cursor fetches, the number of executions, the number of

complete executions, optimizer cost, and the command type.

All

together, an STS encapsulates a set of SQL statements and generates SQL

profiles that allow implementation of any recommended changes to the SQL

execution plan. The following section shows how to create a SQL tuning set.

This

section covers the following topics:

Reference:

12.3.5Transporting a SQL Tuning Set

SQL

Tuning Sets can be transported to another system by first exporting the STS

from one system to a staging table, then importing the STS from the staging

table into another system.

To

transport a SQL Tuning Set:

1.Use theCREATE_STGTAB_SQLSETprocedure to

create a staging table where the SQL Tuning Sets will be exported.

The following example shows how to create a staging table namedstaging_table.

BEGINDBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'staging_table' );END;/

2.Use thePACK_STGTAB_SQLSETprocedure to

export SQL Tuning Sets into the staging table.

The following example shows how to export a SQL Tuning Set namedmy_ststo the staging table.

BEGINDBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name=> 'my_sts',staging_table_name => 'staging_table');END;/

3.Move the staging table to the system where the SQL Tuning Sets

will be imported using the mechanism of choice (such as datapump or database

link).

4.On the system where the SQL Tuning Sets will be imported, use theUNPACK_STGTAB_SQLSETprocedure to

import SQL Tuning Sets from the staging table.

The following example shows how to import SQL Tuning Sets

contained in the staging table.

BEGINDBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name=> '%',replace=> TRUE,staging_table_name => 'staging_table');END;/