會話統計資訊session_pkg

目的:
Oracle會話上的統計和事件以累計方式記錄,為了定量分析某個操作上的統計資訊/等待事件,建立此工具包.
侷限:
*由於沒有會話上的latch統計資訊,沒有列出latch統計(Tom的runstats假設系統只有當前使用者,進而獲取當前會話的latch資訊)
*獲取統計資訊的操作本身會影響該會話的統計資訊

--1,所需許可權
--作為sysdba使用者，授予以下許可權
grant select on v_$session_event to usera;
grant select on v_$statname to usera;
grant select on v_$mystat to usera;
grant select on v_$active_session_history to usera;

--2,建立臨時表和package
create global temporary table session_stats
(statistic# int,
 value int)
on commit preserve rows;

create global temporary table session_event
(wait_class varchar2(64),
 event varchar2(64),
 total_waits int,
 time_waited int)
on commit preserve rows;     
 
--

create or replace package session_pkg
as
  procedure get_snap;
  procedure rpt_stat_event(p_stats_threshold in int default 0);
end;
/

CREATE OR REPLACE PACKAGE BODY session_pkg AS
  mysid INT;
  myserial int;
  sampleid int;

  PROCEDURE get_snap IS
  BEGIN
    SELECT s.sid, s.serial# INTO mysid,myserial
      FROM v_$session s, v_$mystat m
     WHERE s.sid = m.sid
       AND rownum = 1;
    select max(sample_id) into sampleid from v_$active_session_history;   
 
    DELETE FROM session_event;
    INSERT INTO session_event
      SELECT wait_class, event, total_waits, time_waited
        FROM sys.v_$session_event
       WHERE sid = mysid;
 
    DELETE FROM session_stats;
    INSERT INTO session_stats
      SELECT statistic#, VALUE FROM sys.v_$mystat;
 
    COMMIT;
  END;

  PROCEDURE rpt_stat_event(p_stats_threshold in int default 0) IS
  BEGIN

    dbms_output.enable(NULL);
    dbms_output.put_line('Session id:'||mysid ||' Serial#:' || myserial||' Last sampid:'||sampleid);

    dbms_output.put_line(chr(9));
    dbms_output.put_line('----session stats---');
    dbms_output.put_line(rpad('Name', 30) || lpad('Value', 12));
    FOR x IN (SELECT rpad(n.NAME, 30) ||
                     to_char(b.VALUE - a.VALUE, '999,999,999') data
                FROM session_stats a, sys.v_$mystat b, sys.v_$statname n
               WHERE a.statistic# = b.statistic#
                 AND a.statistic# = n.statistic#
   AND n.name not in('session uga memory max','session pga memory max','session uga memory','session pga memory')
                 AND (b.VALUE - a.VALUE) > p_stats_threshold
               ORDER BY (b.VALUE - a.VALUE) desc) LOOP
      dbms_output.put_line(x.data);
    END LOOP;
 
    dbms_output.put_line(chr(9));
    dbms_output.put_line('----session stats-pga/uga <<>>----');
    dbms_output.put_line(rpad('Name', 30) || lpad('Value1', 16) || '->' || lpad('Value2', 16));
    FOR x IN (SELECT rpad(n.NAME, 30) ||
                     to_char(a.VALUE, '999,999,999,999') || '->' ||
                     to_char(b.VALUE, '999,999,999,999') data
                FROM session_stats a, sys.v_$mystat b, sys.v_$statname n
               WHERE a.statistic# = b.statistic#
                 AND a.statistic# = n.statistic#
   AND n.name in('session uga memory max','session pga memory max','session uga memory','session pga memory')
               ORDER BY n.NAME) LOOP
      dbms_output.put_line(x.data);
    END LOOP;
 
    dbms_output.put_line(chr(9));
    dbms_output.put_line('----session events---');
    dbms_output.put_line(rpad('Wait_Class', 15) || rpad('Event', 30) ||
                         rpad('Total_waits', 12) ||
                         rpad('Time_waited', 12));
 
    FOR x IN (SELECT rpad(wait_classb, 15) || rpad(eventb, 30) ||
                     to_char(total_waitsb - nvl(total_waitsa, 0),
                             '999,999,999') ||
                     to_char(time_waitedb - nvl(time_waiteda, 0),
                             '999,999,999') data
                FROM (SELECT a.wait_class  wait_classa,
                             a.event       eventa,
                             a.total_waits total_waitsa,
                             a.time_waited time_waiteda,
                             b.wait_class  wait_classb,
                             b.event       eventb,
                             b.total_waits total_waitsb,
                             b.time_waited time_waitedb
                        FROM session_event a,
                             (SELECT wait_class,
                                     event,
                                     total_waits,
                                     time_waited
                                FROM sys.v_$session_event
                               WHERE sid = mysid) b
                       WHERE a.event(+) = b.event)
               WHERE total_waitsb - nvl(total_waitsa, 0) > 0
               ORDER BY wait_classb,
                        (total_waitsb - nvl(total_waitsa, 0)),
                        (time_waitedb - nvl(time_waiteda, 0))) LOOP
      dbms_output.put_line(x.data);
    END LOOP;
 
    dbms_output.put_line(chr(9));
    dbms_output.put_line('----v$active_session_history---');
    dbms_output.put_line(rpad('Wait_class', 15) || rpad('Event', 30) ||
                         rpad('count(*)', 16) || rpad('min_samp', 12) ||rpad('max_samp', 12));
    FOR x IN (SELECT rpad(wait_class, 15, ' ') || rpad(event, 30, ' ') ||
                     rpad(count(*), 16, ' ') || rpad(min(sample_id), 12, ' ') ||
       rpad(max(sample_id), 12, ' ')  data
                FROM sys.v_$active_session_history
               WHERE session_id = mysid
          AND session_serial# = myserial
   AND sample_id > sampleid
   AND event is not null
        GROUP BY wait_class, event
        ORDER BY wait_class,event) LOOP
      dbms_output.put_line(x.data);
    END LOOP;

  END;
END;
/
--3使用方法：
set serveroutput on
exec session_pkg.get_snap;
--do something
exec session_pkg.rpt_stat_event(0);