使用spm固定sql执行计划

固定sql执行计划

var n number
begin
:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value, fixed =>'YES', enabled=>'YES');
end;
/

 

删除固定的执行计划：

To view all Plans associated with a particular handle:

select sql_handle, plan_name, accepted, fixed,optimizer_cost
from dba_sql_plan_baselines
where sql_handle='SYS_SQL_32814757f4b84f6b';

SQL_HANDLE                     PLAN_NAME                      ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
SYS_SQL_32814757f4b84f6b       SYS_SQL_PLAN_f4b84f6b94f2914b  YES NO  6
SYS_SQL_32814757f4b84f6b       SYS_SQL_PLAN_f4b84f6bbce35b3e  YES NO  6
 

Example 1: Drop all the plans associated with a particular sql_handle:
To drop all the plans associated with a sql_handle:

declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_name=>null);
END;
/

PL/SQL procedure successfully completed.
Note that there are now no plans:

 

SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost
from dba_sql_plan_baselines
where sql_handle='SYS_SQL_32814757f4b84f6b';

no rows selected

Example 2: Drop a baseline with a specific plan name
To drop only a specific baseline with specific plan_name, you can use the DROP_SQL_PLAN_BASELINE Function. This function drops a single plan, or all plans associated with a SQL statement. The syntax is :

DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL)
RETURN PLS_INTEGER;
To use this in the example above:

declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_name=>'SYS_SQL_PLAN_f4b84f6bbce35b3e');
END;
/

Example 3: Drop the entire SQL PLAN MANAGEMENT Repository
The following PL/SQL code will drop the entire SQL PLAN MANAGEMENT Repository.

 

connect / as sysdba
select count(*) from dba_sql_plan_baselines;

declare
pgn number;
sqlhdl varchar2(30);
cursor hdl_cur is
select distinct sql_handle from dba_sql_plan_baselines;
begin
open hdl_cur;

loop
fetch hdl_cur into sqlhdl;
exit when hdl_cur%NOTFOUND;

pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
end loop;

close hdl_cur;
commit;
end;
/

select count(*) from dba_sql_plan_baselines;