本文介绍了如何使用PL/SQL编写存储过程以删除Oracle数据库中的SQL Plan Baseline，包括静态游标和动态游标两种方法，并提供了性能对比。
很久没写过PL/SQL存储过程了--删除SQL Plan Baseline的存储过程
很久没写过PL/SQL了，今天需要，写了一个用于删除Oracle数据库系统自动捕获的 SQL Plan Baseline的PL/SQL块和存储过程。
关闭系统自动捕获 SQL Plan Baseline 的参数 alter system/session set optimizer_capture_sql_plan_baselines=false;
PL/SQL程序块使用静态游标以及游标一次fetch记录集全部数据，而存储过程 p_drop_sql_bl 使用动态游标以及使用 fetch..bulk collect into..limit n，一次提取记录集n条数据，以提高程序代码和数据库的性能，以下会有两者的性能对比。
删除 SQL Plan Baseline的PL/SQL块程序如下：
删除 SQL Plan Baseline的存储过程 p_drop_sql_bl 代码如下：
Procedure created.
检验环境：调用删除 SQL Plan Baseline 的存储过程 p_drop_sql_bl 前，还是存在 SQL Plan Baseline 的：
关闭系统自动捕获 SQL Plan Baseline 的参数 alter system/session set optimizer_capture_sql_plan_baselines=false;
PL/SQL程序块使用静态游标以及游标一次fetch记录集全部数据，而存储过程 p_drop_sql_bl 使用动态游标以及使用 fetch..bulk collect into..limit n，一次提取记录集n条数据，以提高程序代码和数据库的性能，以下会有两者的性能对比。
删除 SQL Plan Baseline的PL/SQL块程序如下：
19:11:30 SYS@orcl*SQL> declare --删除 SQL Plan Baseline 的PL/SQL程序块
19:11:32 2 v_sql_handle varchar2(100);
19:11:32 3 v_plan_name varchar2(100);
19:11:32 4 v_out binary_integer;
19:11:32 5
19:11:32 6 cursor c_1 is select sql_handle,plan_name --定义静态游标，SQL固定。
19:11:32 7 from dba_sql_plan_baselines;
19:11:32 8
19:11:32 9 begin
19:11:32 10 open c_1;
19:11:32 11
19:11:32 12 loop
19:11:32 13 fetch c_1 into v_sql_handle,v_plan_name;
19:11:32 14 exit when c_1%notfound;
19:11:32 15
19:11:32 16 v_out := dbms_spm.drop_sql_plan_baseline(sql_handle=>v_sql_handle,plan_name=>v_plan_name);
19:11:33 17
19:11:33 18 if v_out=1 then
19:11:33 19 dbms_output.put_line('The SQL Plan Baseline: '||v_sql_handle||' was deleted.');
19:11:33 20 else
19:11:33 21 dbms_output.put_line('Operation error: Please check...');
19:11:33 22 end if;
19:11:33 23
19:11:33 24 end loop;
19:11:33 25
19:11:33 26 close c_1;
19:11:33 27 end;
19:11:34 28 /
The SQL Plan Baseline: SYS_SQL_02a86218930bbb20 was deleted.
The SQL Plan Baseline: SYS_SQL_127f1215d03d8cb1 was deleted.
The SQL Plan Baseline: SYS_SQL_470d1594e3064af9 was deleted.
The SQL Plan Baseline: SYS_SQL_5452ff90a6ad4f91 was deleted.
The SQL Plan Baseline: SYS_SQL_85372e07e425b213 was deleted.
The SQL Plan Baseline: SYS_SQL_9091bbf1623118dd was deleted.
The SQL Plan Baseline: SYS_SQL_9c0d7998b1d28680 was deleted.
The SQL Plan Baseline: SYS_SQL_bd31e1aedaa7ee00 was deleted.
The SQL Plan Baseline: SYS_SQL_d5d1c6b1633e200a was deleted.
The SQL Plan Baseline: SYS_SQL_d6da38a64663e444 was deleted.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
19:11:35 SYS@orcl*SQL>
19:11:36 SYS@orcl*SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text,last_verified from dba_sql_plan_baselines; --SQL Plan Baseline已经全部删除
no rows selected
Elapsed: 00:00:00.04
删除 SQL Plan Baseline的存储过程 p_drop_sql_bl 代码如下：
19:14:19 SYS@orcl*SQL> create or replace procedure p_drop_sql_bl(i_rows in number) --删除 SQL Plan Baseline 的存储过程
19:14:20 2 is
19:14:20 3 type v_rec is record(
19:14:20 4 v_sql_handle sys.dba_sql_plan_baselines.sql_handle%type,
19:14:20 5 v_plan_name sys.dba_sql_plan_baselines.plan_name%type
19:14:20 6 );
19:14:20 7
19:14:20 8 type v_array is table of v_rec index by binary_integer; --定义基于v_rec记录类型的数组
19:14:20 9 v_arr v_array;
19:14:20 10
19:14:20 11 type c_1 is ref cursor; --定义动态游标
19:14:20 12 v_c1 c_1;
19:14:20 13
19:14:20 14 v_sql varchar2(1000) := 'select sql_handle,plan_name from sys.dba_sql_plan_baselines';
19:14:20 15
19:14:20 16 v_out number;
19:14:20 17
19:14:20 18 begin
19:14:20 19 open v_c1 for v_sql; --打开动态游标，SQL可变
19:14:20 20
19:14:20 21 loop
19:14:20 22 fetch v_c1 bulk collect into v_arr limit i_rows; --传入的游标每次提取多少行记录的参数 i_rows，以降低数据库性能
19:14:20 23
19:14:20 24 for i in 1..v_arr.count loop
19:14:20 25 v_out := dbms_spm.drop_sql_plan_baseline(sql_handle=>v_arr(i).v_sql_handle,plan_name=>v_arr(i).v_plan_name); --使用数组元素
19:14:20 26 --dbms_output.put_line(v_arr(i).v_sql_handle||' '||v_arr(i).v_plan_name);
19:14:20 27
19:14:20 28 if v_out=1 then
19:14:20 29 dbms_output.put_line('The SQL Plan Baseline: '||v_arr(i).v_plan_name||' was deleted.');
19:14:20 30 else
19:14:20 31 dbms_output.put_line('Operation error: Please check...');
19:14:20 32 end if;
19:14:20 33
19:14:20 34 end loop;
19:14:20 35
19:14:20 36 exit when v_c1%notfound;
19:14:20 37
19:14:20 38 end loop;
19:14:20 39
19:14:20 40 close v_c1;
19:14:20 41 end p_drop_sql_bl;
19:14:21 42 /
检验环境：调用删除 SQL Plan Baseline 的存储过程 p_drop_sql_bl 前，还是存在 SQL Plan Baseline 的：
19:13:52 SYS@orcl*SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text,last_verified from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN ENABLE ACCEPT SQL_TEXT LAST_VERIFIED
----------------------------- ----------------------------------- ---------------------- ------ -------------------------------------------------------------------------------- ---------------------------------------------------------------------------
SYS_SQL_127f1215d03d8cb1 SQL_PLAN_14zsk2r83v35j29d4be67 AUTO-CAPTURE YES YES select * from t1 where rownum<3
no rows selected
19:14:28 SYS@orcl*SQL> oradebug setmypid;
Statement processed.
19:14:33 SYS@orcl*SQL> oradebug event 10046 trace name context forever,level 12; --打开10046事件，查看使用fetch..bulk collect into..limit i_rows的存储过程的优化性能的执行计划，普通SQL使用set autotrace on或explain plan for查看执行计划，PL/SQL程序需要使用10046事件查看执行性能和执行计划
Statement processed.
19:14:47 SYS@orcl*SQL> oradebug tracefile_name; --查看当前用户进程的tracefile跟踪文件路径
/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18742.trc
19:14:59 SYS@orcl*SQL>
调用 p_drop_sql_bl 存储过程，删除 SQL Plan Baseline：
19:14:59 SYS@orcl*SQL> var v_out number;
19:15:02 SYS@orcl*SQL> exec :v_out := 5;
19:15:26 SYS@orcl*SQL> exec p_drop_sql_bl(:v_out); --调用刚才创建的 p_drop_sql_bl 删除 SQL Plan Baseline的存储过程，传入每次提取游标行数参数为5行
The SQL Plan Baseline: SQL_PLAN_14zsk2r83v35j29d4be67 was deleted.
The SQL Plan Baseline: SQL_PLAN_4f38pkmjhckrtb860bcf2 was deleted.
The SQL Plan Baseline: SQL_PLAN_58nrzk2maumwj14e803f5 was deleted.
The SQL Plan Baseline: SQL_PLAN_8mdtzgk8v36yv16c41d59 was deleted.
The SQL Plan Baseline: SQL_PLAN_914dvy5j3266xb860bcf2 was deleted.
The SQL Plan Baseline: SQL_PLAN_b9jw5ckf7jd7b6afbe2b3 was deleted.
The SQL Plan Baseline: SQL_PLAN_bucg1pvdagvh09e49877c was deleted.
The SQL Plan Baseline: SQL_PLAN_dbnf6q5jmw80a9e49877c was deleted.
The SQL Plan Baseline: SQL_PLAN_ddqjsnt367t24bb992955 was deleted.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
SQL Plan Baseline已经全部删除：
19:15:50 SYS@orcl*SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text,last_verified from dba_sql_plan_baselines;
删除 SQL Plan Baseline 的使用静态游标和一次fetch记录集所有数据的 PL/SQL块和使用动态游标、使用 fetch..bullk collect into..limit n一次fetch记录集n条记录的性能对比如下：
declare
v_sql_handle varchar2(100);
v_plan_name varchar2(100);
v_out binary_integer;
cursor c_1 is select sql_handle,plan_name
from dba_sql_plan_baselines;
begin
open c_1;
loop
fetch c_1 into v_sql_handle,v_plan_name;
exit when c_1%notfound;
v_out := dbms_spm.drop_sql_plan_baseline(sql_handle=>v_sql_handle,plan_name=>v_plan_name);
if v_out=1 then
dbms_output.put_line('The SQL Plan Baseline: '||v_sql_handle||' was deleted.');
else
dbms_output.put_line('Operation error: Please check...');
end if;
end loop;
close c_1;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 0 0 0
Execute 1 0.04 0.03 10 40 10 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.04 0.10 10 40 10 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.02 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: fvya7vmwhpz2g
Plan Hash: 0
BEGIN p_drop_sql_bl(:v_out); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 9 36 9 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 9 36 9 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.02 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
可以看到，PL/SQL程序块使用的cpu资源，一致读块数和当前读块数都比使用fetch..bulk collect into..limit n的存储过程 p_drop_sql_bl 高，说明使用游标fetch..bulk
collect into..limit n 提取记录的性能比一次性fetch所有数据的性能要高。
扫一扫
826
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
