11g new features-sql Plan Management

Prior to the 11g version, the stored outlines (SQL Profile) feature was provided to save the SQL execution plan.

In 11g, a new feature SQL plan management (SQL Management) feature was introduced to save SQL performance.

The database automatically controls the evolution of the SQL execution plan, with SQL plan baselines. The SPM periodically captures and evaluates the SQL execution plan and then builds a SQL plan baselines that contains only an efficient execution plan. SQL plan baselines will only contain execution plans that do not cause SQL performance degradation.

When the system encounters the following changes, you can use the SPM to save the SQL performance information:

-Database Upgrade

-New Optimizer version

-Optimizer parameter modification

-System Setup Changes

-schema information and meta-data changes

-Deploy a new application module

SQL Plan baselines

SQL Plan baseline is a collection of historical execution plans that the database maintains for each repeatable SQL statement. These execution plans contain only an acceptable execution plan for the database.

1. Automatic capture of SQL plan baselines
Automatic capture requires setting the value of the parameter optimizer_capture_sql_plan_baselines to True, which is false by default.

Sql> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE Scope=spfile;

2. Manually load SQL plan to SQL plan baselines
SQL plan baselines can be created from the SQL Tuning Set (STS) and Cursor cache. "When upgrading the database, beware of performance degradation after upgrade, you can export SQL plan before upgrading, and then import"
(1) Export SQL Plan from SQL tuning set
#创建空的sql调优集

     Begin          Dbms_sqltune.create_sqlset (               sqlset_name = ' Testset1 ',               description = ' Test SQL tuning set to Capture AWR data '          );     End;     /

#使用load_sqlset将sql plan is loaded into the newly created SQL tuning set

    DECLARE        test_cursor1 dbms_sqltune.sqlset_cursor;    Begin        Open Baseline_cursor for            Select Value (P) from table (Dbms_sqlture.select_workload_repository (' Peak Baseline ', Null,null, ' Elapsed_time ', null,null,null,20)) p;        Dbms_sqlset.load_sqlset (sqlset_name = ' Testset1 ', populate_cursor = Test_cursor1);    End;    /

#将sql data in tuning set import SQL plan baselines

    DECLARE        Test_plans Pls_integer;    Begin        Test_plans: = dbms_spm.load_plans_from_sqlset (sqlset_name = ' testset1 ');    End;    /

(2) Export SQL plan from cursor cache

DECLARE  Test_plans Pls_integer; begin  Test_plans: = Dbms_spm.load_plans_from_cursor_cache (  sql_id = > ' 123456789999 ')  return pls_integer; end;

Manage SQL plan baselines using the DBMS_SPM package provided by Oracle.

To view the properties of SQL plan baselines

Select Sql_handle,       sql_text,       plan_name,       origin,       enabled,       accepted,       fixed,       autopurge From  Dba_sql_plan_baselines;

Modify a SQL plan to accept State

sql> exec dbms_spm.alter_sql_plan_baselines (        sql_handle = sys_sql_122222222 ',        plan_name = ' SYS_ sql_plan_b5429522ee05ab0e ',        attribute_name = ' accepted-status ',        attribute_value = ' YES ');

To view the baseline of a SQL:

SELECT * FROM  table (dbms_xplan.display_sql_plan_baseline (sql_handle = ' sys_sql_ba5e12ccae97040f '),                                                  Format     = ' basic ');

SQL Management Base
SQL plan management stores the SQL plan baselines information in a new data dictionary--sql management Base (SMB).
SMB is stored in the Sysaux table space.

Configuring SQL Management Base (SMB)
Configuring SMB requires the following two parameters to be set:

Sql> Select Parameter_name,parameter_value   2 from  dba_sql_management_config; Parameter_name                 parameter_value---------------------------------------------space_budget_percent                        10PLAN _retention_weeks                        

The parameter space_budget_percent indicates that SMB can occupy the scale of the Sysaux table space. Default is 10%

sql> exec dbms_spm.configure (' space_budget_percent ', 40); sql> exec dbms_spm.configure (' Plan_retention_weeks ', 105);

#手动清空SMB

sql> exec dbms_spm.purge_sql_plan_baseline (' sys_sql_plan_b5429522ee05ab0e ');

To view SMB configuration information:

Sql> Select Parameter_name,parameter_value,last_modified,modified_by  2 from  dba_sql_management_config; Parameter_name                 parameter_value last_modified        modified_by----------------------------------------------- --------------------------------------space_budget_percent                        10plan_retention_weeks                        

11g new features-sql Plan Management