使用spm固定sql执行计划

朱雀 2022-11-22 12:52 75阅读 0赞

固定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;