SQL Plan Management

Tips.

 SQL plan management (SPM) ensures that runtime performance will not degrade due to execution plan changes. To guarantee this, only accepted execution plans are used; any plan evolution that does occur is tracked and evaluated at a later point in time, and only accepted if the new plan shows a noticeable improvement in runtime.

 SQL Plan Management has three main components:

 Plan Capture:
  • Creation of SQL plan baselines that store accepted execution plans for all relevant SQL statements. SQL plan baselines are stored in the SQL management base in the SYSAUX tablespace.

Plan Selection:
  • Ensures only accepted execution plans are used for statements with a SQL plan baseline and records any new execution plans found for a statement as unaccepted plans in the SQL plan baseline.

Plan Evolution: 
  • Evaluate all unaccepted execution plans for a given statement, with only plans that show a performance improvement becoming accepted plans in the SQL plan baseline
why Execution Plan has been changed:
-regathering optimizer statistics.
-changes to the optimizer parameters.

 SQL Management Base (SMB) in SYSAUX tablespace
  • SQL Plan History
  • SQL Plan Baselines 
  • SQL Statement Log
Plan Capture, the are two option:
  1. Automatically 
       OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (default FALSE).

    show parameter optimizer_capture_sql_plan_baselines
    alter system set optimizer_capture_sql_plan_baselines=true;
  1. Manually
    • dbms_spm
    • OEM
    • From SQL Tuning Set
      • (you can export SQL tuning sets from a database and import them into another)
      • for loading from STS to SPM (the plan will be automatically accepted) DBMS_SPM.LOAD_PLANS_FROM_SQLSET
    • From the course cache
      • By applying a filter on the SQL statement text, module name, SQL_ID or parsing schema
      • DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. 
    • From AWR (version 12.2)
      • Plans can be loaded between a specified begin and end AWR snapshot 
      • DBMS_SPM.LOAD_PLANS_FROM_AWR
    • Unpacked from a staging table
      • From one Database to another.
      • SQL plan baselines can be packed into a staging table.
      • DBMS_SPM.PACK_STGTAB_BASELINE.
      1. capture plan 
      2. pack base line
      3. export and import
      4. unpack
      5. use 
    • From existing stored outlines
      • DBMS_SPM.MIGRATE_STORED_OUTLINE

New Feature
Oracle Database 12c Release 2 adds the capability to limit which SQL statements are captured using filters 

 DBA_SQL_MANAGEMENT_CONFIG:

 To configure auto capture from schema
note: only 12c R2
exec dbms_spm.configure('AUTO_CAPTURE _PARSING_SCHEMA_NAME','SCOTT');

 col parameter_name format a30
select parameter_name, parameter_value from dba_sql_management_config;

 PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        10
PLAN_RETENTION_WEEKS                        53

 To change space retention 
dbms_spm.configure(parameter_name => 'plan_retention_weeks',parameter_value => 30);

 col parameter_name format a30
select parameter_name, parameter_value from dba_sql_management_config;

 PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                        53

SPM Report 

 DBMS_SPM.REPORT_EVOLVE_TASK 

Automatic Plan Evolution
SYS_AUTO_SPM_EVOLVE_TASK
it is operates during the nightly maintenance window and automatically   
LAST_VERIFIED
LAST_EXECUTED
to check the result 
DBMS_SPM.REPORT_AUTO_EVOLVE_TASK

Manual Plan Evolution

 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

 variable tname varchar2 (50)
variable exename varchar2 (50)
tname := dbms_spm.create_evolve_task(sql_handle => 'SQL_1234554456661');
exename:= dbms_spm.execute_evolve_task(task_name => :tname);

select dbms_spm.report_evolve_task(
task_name => :tname,
execute_name => :exename) as output
from dual;

to accept
execute dbms_spm.accept_sql_plan_baseline (task_name => :tname);

Managing and Monitoring SQL Plan Baselines

  • Oracle Enterprise Manager
  • DBMS_SPM 
  • DBMS_XPLAN 
  • DBA_SQL_PLAN_BASELINES
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES: Controls the automatic creation of new SQL plan baselines for repeatable SQL statements.

OPTIMIZER_USE_SQL_PLAN_BASELINES controls the use of SQL plan baselines


to change the SPM configure (e.g increase space more than 10%of sysaux between 1 & 50%)
DBMS_SPM.CONFIGURE

 to check configuration
select sql_text, sql_handle, plan_name, enable, accepted 
from dba_sql_plan_baseline;

 to shows the execution plan for the accepted plan 
select * from dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_1232244','SQL_PLAN_21233');

 we can join V$SQL AND DBA_SQL_PLAN_BASELINE

 SQL Plan Baselines and Adaptive Plans


e.g. manuality
col parameter_name format a30
select parameter_name, parameter_value from dba_sql_management_config;

 PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        10

PLAN_RETENTION_WEEKS                        53

 1# Query 
SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno

 2# Identified 
SELECT SQL_ID, hash_value, child_number, sql_text FROM v$sql
where sql_text LIKE '%&sql_text_like%';
e.g. (e.deptno = d.deptno) 


Posted by