멋지게 놀아라

 CREATE TABLE before_stat AS SELECT * FROM v$sysstat WHERE 1 = 2 ;  

 CREATE TABLE after_stat AS SELECT * FROM v$sysstat WHERE 1 = 2 ;  

 CREATE TABLE before_event AS SELECT * FROM v$system_event WHERE 1 = 2 ;  

 CREATE TABLE after_event AS SELECT * FROM v$system_event WHERE 1 = 2 ;   

Package Header

CREATE OR REPLACE PACKAGE CHECK_MFO

AS

     TYPE return_stat IS RECORD (

NAME            VARCHAR2(64) ,

CLASS           NUMBER ,

DELTA_VALUE     NUMBER ,

VALUE_PER_SEC   NUMBER ) ;

     TYPE return_event IS RECORD (

EVENT                  VARCHAR2(64) ,

DELTA_TOTAL_WAITS      NUMBER ,

TOTAL_WAITS_PER_SEC    NUMBER  ,

DELTA_TIME_WAITED      NUMBER ,

TIME_WAITED_PER_SEC    NUMBER )         ;

     TYPE return_table_stat IS TABLE OF return_stat ;

TYPE return_table_event IS TABLE OF return_event ;

FUNCTION F_CHECK_STAT( i_interval number)  RETURN return_table_stat   PIPELINED;

FUNCTION F_CHECK_EVENT( i_interval number)  RETURN return_table_event   PIPELINED;

END CHECK_MFO ;

/

Package Body

CREATE OR REPLACE PACKAGE BODY CHECK_MFO AS

    FUNCTION F_CHECK_STAT( i_interval number)

        RETURN return_table_stat

        PIPELINED

IS

pragma autonomous_transaction;

        rec        return_stat;

-- common

V_NAME     VARCHAR2(64) ;

V_CLASS           NUMBER  ;

V_DELTA_VALUE    NUMBER ;

V_VALUE_PER_SEC  NUMBER ;

BEGIN

EXECUTE IMMEDIATE ' TRUNCATE TABLE before_stat ';

EXECUTE IMMEDIATE ' TRUNCATE TABLE after_stat' ;

INSERT INTO before_stat SELECT * FROM v$sysstat ;

DBMS_LOCK.SLEEP ( i_interval ) ;

INSERT INTO after_stat SELECT * FROM v$sysstat ;

COMMIT ;

FOR v_value IN ( SELECT af.name , af.class , (af.value - be.value) as delta_value , 

                        (af.value - be.value) / i_interval as value_per_sec

            FROM   after_stat af , before_stat be

            WHERE  af.name = be.name

            AND    (af.value - be.value) > 0  ) LOOP

  V_NAME    := v_value.NAME  ;  

  V_CLASS    := v_value.CLASS ; 

  V_DELTA_VALUE    := v_value.delta_value  ; 

  V_VALUE_PER_SEC  := v_value.value_per_sec ;                        

  REC.NAME    := V_NAME  ;  

  REC.CLASS    := V_CLASS  ; 

  REC.DELTA_VALUE    := V_DELTA_VALUE  ; 

  REC.VALUE_PER_SEC  := V_VALUE_PER_SEC ;

        PIPE ROW (rec);

END LOOP ;

RETURN;

    END F_CHECK_STAT ;

    FUNCTION F_CHECK_EVENT ( i_interval number)

        RETURN return_table_event

        PIPELINED

IS

pragma autonomous_transaction;

        rec        return_event;

-- common

V_EVENT                      VARCHAR2(64) ;

V_DELTA_TOTAL_WAITS    NUMBER ;

V_TOTAL_WAITS_PER_SEC  NUMBER ;

V_DELTA_TIME_WAITED    NUMBER ;

V_TIME_WAITED_PER_SEC  NUMBER ;

BEGIN

EXECUTE IMMEDIATE ' TRUNCATE TABLE before_event' ;

EXECUTE IMMEDIATE ' TRUNCATE TABLE after_event' ;

INSERT INTO before_event SELECT * FROM v$system_event  WHERE wait_class <> 'Idle' ;

DBMS_LOCK.SLEEP ( i_interval ) ;

INSERT INTO after_event SELECT * FROM v$system_event  WHERE wait_class <> 'Idle' ;

COMMIT ;

FOR v_value IN ( SELECT af.event , (af.total_waits - be.total_waits) as delta_total_waits , 

                        (af.total_waits - be.total_waits) / i_interval as total_waits_per_sec ,

                             (af.time_waited - be.time_waited) as delta_time_waited , 

                             (af.time_waited - be.time_waited) / i_interval as time_waited_per_sec

                 FROM   after_event af , before_event be

                 WHERE  af.event = be.event

                 AND    af.total_waits - be.total_waits > 0 ) LOOP

  V_EVENT    := v_value.EVENT  ;  

  V_DELTA_TOTAL_WAITS    := v_value.delta_total_waits  ; 

  V_TOTAL_WAITS_PER_SEC  := v_value.total_waits_per_sec ;                

  V_DELTA_TIME_WAITED    := v_value.delta_time_waited ;

  V_TIME_WAITED_PER_SEC  := v_value.time_waited_per_sec ;                 

  REC.EVENT    := V_EVENT  ;  

  REC.DELTA_TOTAL_WAITS    := V_DELTA_TOTAL_WAITS  ; 

  REC.TOTAL_WAITS_PER_SEC  := V_TOTAL_WAITS_PER_SEC ;

  REC.DELTA_TIME_WAITED    := V_DELTA_TIME_WAITED ;

  REC.TIME_WAITED_PER_SEC  := V_TIME_WAITED_PER_SEC ;

             PIPE ROW (rec);

END LOOP ;

RETURN;

    END F_CHECK_EVENT ;        

END;

/