ITPub博客

首页 > 数据库 > Oracle > Oracle10g Wait Event Data Collection Procedure

Oracle10g Wait Event Data Collection Procedure

原创 Oracle 作者:tolilong 时间:2015-08-25 09:37:09 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: HP-UX lvm管理
请登录后发表评论 登录
全部评论

注册时间:2010-07-13

  • 博文量
    406
  • 访问量
    1009576