即日起，正式踏上PL/SQL语言的征程。

在此，留贴为证！

create or replace package xplan_monitor as
procedure init_tabs;
procedure comp_proc;
procedure update_last_tab;
procedure clear_result_tab;
END xplan_monitor;
/
create or replace package body xplan_monitor is
procedure init_tabs is
BEGIN
update_last_tab;
BEGIN
execute immediate 'drop table sql_plan_changed purge';
EXCEPTION
when others then
dbms_output.put_line('cannot drop table sql_plan_changed!');
dbms_output.put_line(sqlerrm);
END;
BEGIN
execute immediate 'create table sql_plan_changed as
(select new.sql_id, old.plan_hash_value as plan_hash_value_old,
new.plan_hash_value as plan_hash_value_new,
old.cpu_time as cpu_time_old, new.cpu_time as cpu_time_new,
old.elapsed_time as elapsed_time_old,
new.elapsed_time as elapsed_time_new,
sysdate as insert_time
from vdollor_sqlarea old, v$sqlarea new
where old.sql_id = new.sql_id and rownum < 1)';
EXCEPTION
when others then
dbms_output.put_line('cannot create table sql_plan_changed!');
dbms_output.put_line(sqlerrm);
END;
END init_tabs;
procedure comp_proc is
plan_curr number;
plan_last number;
sqlid varchar2(13);
/*
cursor cur_sqlstat( v_para number ) is select * from dba_hist_sqlstat where snap_id = v_para;
*/
/* get TOP 10 SQL on cpu_time */
cursor cur_sql_id is select * from
(select sql_text, sql_id, round(cpu_time/1000000) cpu_time,
round(elapsed_time/1000000) elapsed_time,
disk_reads, buffer_gets,
rows_processed, plan_hash_value
from v$sqlarea order by cpu_time desc)
where rownum < 11;
BEGIN
clear_result_tab;
FOR cur_row in cur_sql_id LOOP
select plan_hash_value into plan_curr from v$sqlarea where sql_id = cur_row.sql_id;
BEGIN
execute immediate 'select plan_hash_value into plan_last from vdollor_sqlarea where sql_id = cur_row.sql_id';
IF plan_last != plan_curr THEN
BEGIN
execute immediate 'insert into sql_plan_changed (select new.sql_id, old.plan_hash_value as plan_hash_value_old,
new.plan_hash_value as plan_hash_value_new,
old.cpu_time as cpu_time_old, new.cpu_time as cpu_time_new,
old.elapsed_time as elapsed_time_old,
new.elapsed_time as elapsed_time_new
sysdate
from vdollor_sqlarea old, v$sqlarea new
where old.sql_id = cur_row.sql_id and new.sql_id = cur_row.sql_id)';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Insert table sql_plan_changed failed.');
dbms_output.put_line(sqlerrm);
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('no data for sql_id:'||cur_row.sql_id||' in vdollor_sqlare.');
dbms_output.put_line(sqlerrm);
END;
END LOOP;
COMMIT;
update_last_tab;
END comp_proc;
procedure update_last_tab is
/* because of size of v$sqlarea, truncate and insert is not better than drop and create. */
BEGIN
BEGIN
execute immediate q'{drop table vdollor_sqlarea purge}';
EXCEPTION
when others then
dbms_output.put_line('cannot drop table vdollor_sqlarea!');
dbms_output.put_line(sqlerrm);
END;
BEGIN
execute immediate q'{create table vdollor_sqlarea as (select * from v$sqlarea)}';
EXCEPTION
when others then
dbms_output.put_line('cannot create table vdollor_sqlarea!');
dbms_output.put_line(sqlerrm);
END;
END update_last_tab;
procedure clear_result_tab is
BEGIN
execute immediate 'truncate table sql_plan_changed';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('truncate table sql_plan_changed failed!');
dbms_output.put_line(sqlerrm);
END clear_result_tab;
END xplan_monitor;
/

如果你想保护自己的劳动成果，可以使用Oracle自带工具加密语句：
oracle $ wrap iname=xplan_monitor.pkg [oname=xplan_monitor.plb];
SQL> @xplan_monitor.plb;

2016.07.29 Jackson Chen

ITPub博客

Oracle 数据库监控案例（PL/SQL开发处女秀）

即日起，正式踏上PL/SQL语言的征程。

在此，留贴为证！

最新文章