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;/