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

 

1 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)       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 the Critical SQL

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:

·         Creating a SQL Tuning Set

·         Loading a SQL Tuning Set

·         Displaying the Contents of a SQL Tuning Set

·         Modifying a SQL Tuning Set

·         Transporting a SQL Tuning Set

·         Dropping a SQL Tuning Set

·         Additional Operations on SQL Tuning Sets

 

Reference:

http://blog.itpub.net/post/11835/474845

http://st-curriculum.oracle.com/obe/db/11g/r1/prod/manage/spm/spm.htm

http://gavinsoorma.com/2009/07/11g-optimizer-plan-stabilty-using-sql-plan-baselines/

http://www.dba-oracle.com/oracle10g_tuning/t_sql_tuning_sets.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#CHDBIGDF

 

12.3.5 Transporting 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 the CREATE_STGTAB_SQLSET procedure to create a staging table where the SQL Tuning Sets will be exported.

The following example shows how to create a staging table named staging_table.

BEGIN

  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'staging_table' );

END;

/

2.    Use the PACK_STGTAB_SQLSET procedure to export SQL Tuning Sets into the staging table.

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

BEGIN

  DBMS_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 the UNPACK_STGTAB_SQLSET procedure to import SQL Tuning Sets from the staging table.

The following example shows how to import SQL Tuning Sets contained in the staging table.

BEGIN

  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(

      sqlset_name  => '%',

      replace  => TRUE,

      staging_table_name => 'staging_table');

END;

/

 

干露露.jpg

干露露3.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26136400/viewspace-710026/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26136400/viewspace-710026/