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.

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 (               'testset1',                ' 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            SelectValue (p) fromTable (Dbms_sqlture.select_workload_repository ('Peak Baseline',NULL,NULL,'Elapsed_time',NULL,NULL,NULL, -) ) 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:'testset1');    End;     /

(2) Export SQL plan from cursor cache

declare  Test_plans pls_integer; begin  Test_plans:= Dbms_spm.load_plans_from_cursor_cache (   ' 123456789999 ' )  return/

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 (        = sys_sql_122222222',         'sys_sql_plan_b5429522ee05ab0e',        'accepted-status ' ,         ' YES ');

To view the baseline of a SQL:

Select * from  '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:

Select Parameter_name,parameter_value    2   from Dba_sql_management_config; Parameter_name                 parameter_value---------------------------------------------space_budget_percent                         Ten Plan_retention_weeks                          - SQL

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',+); SQL> Exec dbms_spm.configure ('plan_retention_weeks');

#手动清空SMB

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

To view SMB configuration information:

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                        plan_retention _weeks                        -SQL

11g new features-sql Plan Management