系統統計資訊system_pkg

目的:
Oracle系統上的統計和事件以累計方式記錄,為了定量分析某個操作上的統計資訊/等待事件,建立此工具包.
侷限:
*獲取統計資訊的操作本身會影響該會話的統計資訊

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

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

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

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

CREATE OR REPLACE PACKAGE BODY system_pkg AS
  sampleid int;

  PROCEDURE get_snap IS
  BEGIN
    select max(sample_id) into sampleid from v_$active_session_history;   
 
    DELETE FROM system_event;
    INSERT INTO system_event
      SELECT wait_class, event, total_waits, time_waited
        FROM sys.v_$system_event;
 
    DELETE FROM system_stats;
    INSERT INTO system_stats
      SELECT statistic#, VALUE FROM sys.v_$sysstat;
 
    COMMIT;
  END;

  PROCEDURE rpt_stat_event(p_stats_threshold in int default 0) IS
  BEGIN

    dbms_output.enable(NULL);
    dbms_output.put_line('Last sampid:'||sampleid);

    dbms_output.put_line(chr(9));
    dbms_output.put_line('----system 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 system_stats a, sys.v_$sysstat b, sys.v_$statname n
               WHERE a.statistic# = b.statistic#
                 AND a.statistic# = n.statistic#
                 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('----system 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 system_event a,
                             (SELECT wait_class,
                                     event,
                                     total_waits,
                                     time_waited
                                FROM sys.v_$system_event) 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 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 system_pkg.get_snap;
--do something
exec system_pkg.rpt_stat_event(0);