Oracle 使用sql plan baseline 固定执行计划

         SQL 计划基线是一个旨在使指定 SQL 语句的性能可以接受的执行计划。

        从11g 开始，oracle 提供了执行计划管理SPM功能，方便DBA通过执行计划基线修正sql 错误的执行计划,避免sql 执行性能退化。

--create sql_plan_baseline from sql_tuning_task
begin
dbms_sqltune.create_sql_plan_baseline(task_name =>
             '&sql_id_tuning_task', owner_name => 'SYS', plan_hash_value
             => '&plan_hash_value');
end;
/

--crete sql_plan_baseline from cursor
declare
i integer ;
begin
i:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id',plan_hash_value=>'&plan_hash_value');
dbms_output.put_line('导入完成!' );
end;
/

--crete sql_plan_baseline from awr
declare
i integer ;
begin
i:=dbms_spm.load_plans_from_awr(begin_snap => &begin_snap, end_snap => &begin_snap);
dbms_output.put_line('导入完成!' );
end;
/
--create sql_plan_baseline from sql tuning set
declare
i integer ;
begin
i := dbms_spm.load_plans_from_sqlset(
     sqlset_name => 'sts_&sql_id'
     );
dbms_output.put_line('导入完成!' );
end;
/

--automatic plan Capture by system optimizer

alter system optimizer_capture_sql_plan_baselines=true;

--drop sql_plan_baseline
set serveroutput on
declare
   v_text  pls_integer;
begin
   v_text := dbms_spm.drop_sql_plan_baseline(sql_handle => '&sql_handle',plan_name  => null);
   dbms_output.put_line(v_text);
end;
/
--alert sql_plan_baseline (fix sql paln)
set serveroutput on
declare
v_text pls_integer;
begin
v_text := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle',plan_name => 'sys_sql_plan_&sql_id',
    attribute_name => 'fixed',attribute_value => 'yes');
  dbms_output.put_line('plans altered: ' ||v_text );
end;
/
--evolve sql_plan_baseline(confirm and add accepted  plan to baseline)
set serveroutput on
declare
  l_plans_altered  clob;
begin
l_plans_altered := dbms_spm.evolve_sql_plan_baseline(
sql_handle      => '&sql_handle',
plan_name       => 'sys_sql_plan_&sql_id',
verify           =>'no',
commit        =>'yes');
dbms_output.put_line('plans altered: ' || l_plans_altered);
end;
/

--query sql_plan_baseline

select t.sql_handle,t.sql_text,t.plan_name,t.enabled,t.accepted,t.fixed,t.origin from dba_sql_plan_baselines t;