Follow is Oracle Wait Event Data Collection Simple Procedure(Database is 10g):
create or replace procedure dc as
--------event comment
--59 library cache lock
--64 buffer busy waits
--115 db file sequential read
--116 db file scattered read
--289 latch free
--161 direct path read
--163 direct path write
--214 library cache pin
--215 library cache lock
cursor c1 is
select se.SID,se.SERIAL#,se.USERNAME,se.OSUSER,se.PADDR,se.LOGON_TIME,se.PROCESS,se.SQL_HASH_VALUE,se.SADDR,se.MODULE,se.ROW_WAIT_OBJ#,se.ROW_WAIT_FILE#,se.ROW_WAIT_BLOCK#,
se.ROW_WAIT_ROW#,se.SEQ#,se.EVENT#,se.EVENT,se.P1TEXT,se.P1,se.P1RAW,se.P2TEXT,se.P2,se.P2RAW,se.P3TEXT,se.P3,se.P3RAW,se.WAIT_TIME,se.SECONDS_IN_WAIT,se.STATE
from
v$session se where se.TYPE<>'BACKGROUND' and se.EVENT# in(115,116,289,161,163,214,215,64,59);
v1 c1%rowtype;
v_sqltext varchar2(4000);
v_id int;
begin
select max(id) into v_id from dc_tab1;
insert into dc_tab1 values (v_id+1,sysdate);
if c1%isopen then
close c1;
end if;
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
select sql_text into v_sqltext from v$sqltext where hash_value=v1.sql_hash_value;
insert into dc_tab
(SID,SERIAL#,USERNAME,OSUSER,PADDR,LOGON_TIME,PROCESS,SQL_HASH_VALUE,SADDR,MODULE,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,
SEQ#,EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_TIME,SECONDS_IN_WAIT,STATE,SQLTEXT)
values
(v1.SID,v1.SERIAL#,v1.USERNAME,v1.OSUSER,v1.PADDR,v1.LOGON_TIME,v1.PROCESS,v1.SQL_HASH_VALUE,v1.SADDR,v1.MODULE,v1.ROW_WAIT_OBJ#,v1.ROW_WAIT_FILE#,v1.ROW_WAIT_BLOCK#,
v1.ROW_WAIT_ROW#,v1.SEQ#,v1.EVENT#,v1.EVENT,v1.P1TEXT,v1.P1,v1.P1RAW,v1.P2TEXT,v1.P2,v1.P2RAW,v1.P3TEXT,v1.P3,v1.P3RAW,v1.WAIT_TIME,v1.SECONDS_IN_WAIT,v1.STATE,
v_sqltext);
end loop;
close c1;
commit;
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24237320/viewspace-1781776/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24237320/viewspace-1781776/