即日起,正式踏上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 $ wrap iname=xplan_monitor.pkg [oname=xplan_monitor.plb];
SQL> @xplan_monitor.plb;
2016.07.29 Jackson Chen
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25521690/viewspace-2122744/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25521690/viewspace-2122744/