A2.4 Capture session statistics | Oracle Real Application Clusters

< Day Day Up >
-- MV_CaptureSessionWaits.sql -- This script contains all the objects required to   capture -- user session information on session logout from -- gv$session_wait -- -- This will only work with Oracle Real Application   Clusters -- NOTE: The script assumes that the user MVPERFAUDIT   is present -- Author: Murali Vallath -- Date: 01-SEP-2002 -- Execute this procedure logged in as sys or system -- SET ECHO ON SET SPOOL MV_CaptureSessionWaits.lis -- -- Drop the table if already present -- DROP TABLE MVPERFAUDIT.MV_AUDIT_SESSION_WAIT; -- -- Create the table to store the session wait information -- CREATE TABLE MVPERFAUDIT.MV_AUDIT_SESSION_WAIT ( MVASW_SEQ                                      NUMBER ,MVASW_INST_ID                                   NUMBER ,MVASW_SID                                       NUMBER ,MVASW_USERNAME                                  VARCHAR2(30) ,MVASW_MACHINE                                   VARCHAR2(64) ,MVASW_OSUSER                                    VARCHAR2(30) ,MVASW_MODULE                                    VARCHAR2(48) ,MVASW_SQL_ADDRESS                               RAW(8) ,MVASW_SEQ#                                      NUMBER ,MVASW_EVENT                                     VARCHAR2(64) ,MVASW_P1TEXT                                    VARCHAR2(64) ,MVASW_P1                                        NUMBER ,MVASW_P1RAW                                     RAW(8) ,MVASW_P2TEXT                                    VARCHAR2(64) ,MVASW_P2                                        NUMBER ,MVASW_P2RAW                                     RAW(8) ,MVASW_P3TEXT                                    VARCHAR2(64) ,MVASW_P3                                        NUMBER ,MVASW_P3RAW                                     RAW(8) ,MVASW_WAIT_TIME                                 NUMBER ,MVASW_SECONDS_IN_WAIT                           NUMBER ,MVASW_STATE                                     VARCHAR2(19) ,MVASW_FAILOVER_TYPE                             VARCHAR2(13) ,MVASW_FAILOVER_METHOD                           VARCHAR2(10) ,MVASW_FAILED_OVER                               VARCHAR2(3) ,MVASW_SESSION_WAIT_DATE_TIME                    DATE DEFAULT SYSDATE ) TABLESPACE TOOLS / -- -- Create the public synonym for the table and other   privileges -- CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_WAIT for   MVPERFAUDIT.MV_AUDIT_SESSION_WAIT; GRANT INSERT,SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_WAIT TO PUBLIC; -- Create sequence and grant rights to users --   CREATE SEQUENCE MVPERFAUDIT.MV_AUDIT_SESSION_WAIT_SEQ   INCREMENT BY 1   START WITH 1   NOMAXVALUE   NOMINVALUE   NOCYCLE   CACHE 200   ORDER / CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_WAIT_SEQ FOR   MVPERFAUDIT.MV_AUDIT_SESSION_WAIT_SEQ; GRANT SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_WAIT_SEQ   TO PUBLIC; -- -- Create the trigger to capture session wait statistics -- CREATE OR REPLACE TRIGGER MV_AUDIT_SESSION_WAIT_TRG BEFORE LOGOFF ON DATABASE BEGIN INSERT INTO MV_AUDIT_SESSION_WAIT   SELECT MV_AUDIT_SESSION_WAIT_SEQ.NEXTVAL  ,GVSW.INST_ID  ,GVSW.SID  ,GVS.USERNAME  ,GVS.MACHINE  ,GVS.OSUSER  ,GVS.MODULE  ,GVS.SQL_ADDRESS  ,GVSW.SEQ#  ,GVSW.EVENT  ,GVSW.P1TEXT  ,GVSW.P1  ,GVSW.P1RAW  ,GVSW.P2TEXT  ,GVSW.P2  ,GVSW.P2RAW  ,GVSW.P3TEXT  ,GVSW.P3  ,GVSW.P3RAW  ,GVSW.WAIT_TIME  ,GVSW.SECONDS_IN_WAIT  ,GVSW.STATE  ,GVS.FAILOVER_TYPE  ,GVS.FAILOVER_METHOD  ,GVS.FAILED_OVER  ,SYSDATE FROM GV$SESSION_WAIT GVSW, GV$SESSION GVS  WHERE GVSW.SID = GVS.SID AND    GVSW.INST_ID = GVS.INST_ID AND    GVS.AUDSID = userenv(’sessionid’); END; ========================================================= -- MV_CaptureSessionEvents.sql -- This script contains all the objects required to   capture -- user session information on logout from   gv$session_event -- -- This will only work with Oracle Real Application   Clusters -- NOTE: The script assumes that the user MVPERFAUDIT   is present -- Author: Murali Vallath -- Date: 01-SEP-2002 -- Execute this procedure logged in as sys or system -- SET ECHO ON SET SPOOL MV_CaptureSessionEvents.lis -- -- Drop the table if already present -- DROP TABLE MVPERFAUDIT.MV_AUDIT_SESSION_EVENT; -- -- Create the table to store the session event information -- CREATE TABLE MVPERFAUDIT.MV_AUDIT_SESSION_EVENT ( MVASE_ID                     NUMBER ,MVASE_INST_ID                 NUMBER ,MVASE_SID                     NUMBER ,MVASE_USERNAME                VARCHAR2(30) ,MVASE_MACHINE                 VARCHAR2(64) ,MVASE_OSUSER                  VARCHAR2(30) ,MVASE_MODULE                  VARCHAR2(48) ,MVASE_SQL_ADDRESS             RAW(8) ,MVASE_EVENT                   VARCHAR2(64) ,MVASE_TOTAL_WAITS             NUMBER ,MVASE_TOTAL_TIMEOUTS          NUMBER ,MVASE_TIME_WAITED             NUMBER ,MVASE_AVERAGE_WAIT            NUMBER ,MVASE_MAX_WAIT                NUMBER ,MVASE_TIME_WAITED_MICRO       NUMBER ,MVASE_FAILOVER_TYPE           VARCHAR2(13) ,MVASE_FAILOVER_METHOD         VARCHAR2(10) ,MVASE_FAILED_OVER             VARCHAR2(3) ,MVASE_SESSION_DATE_TIME       DATE DEFAULT SYSDATE ) TABLESPACE TOOLS / CREATE SEQUENCE MVPERFAUDIT.MV_AUDIT_SESSION_EVENT_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE CACHE 200 ORDER / CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_EVENT for   MVPERFAUDIT.MV_AUDIT_SESSION_EVENT; CREATE PUBLIC SYNONYM MV_AUDIT_SESSION_EVENT_SEQ FOR   MVPERFAUDIT.MV_AUDIT_SESSION_EVENT_SEQ; GRANT INSERT,SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_EVENT   TO PUBLIC; GRANT SELECT ON MVPERFAUDIT.MV_AUDIT_SESSION_EVENT_SEQ   TO PUBLIC; -- -- Create the logout trigger to capture session event   statistics -- CREATE OR REPLACE TRIGGER MV_PERFAUDIT_SESSION_EVENT_TRG BEFORE LOGOFF ON DATABASE BEGIN   INSERT INTO MV_AUDIT_SESSION_EVENT    SELECT       MV_AUDIT_SESSION_EVENT_SEQ.NEXTVAL,       GVSE.INST_ID       ,GVSE.SID       ,GVS.USERNAME      ,GVS.MACHINE      ,GVS.OSUSER      ,GVS.MODULE      ,GVS.SQL_ADDRESS       ,GVSE.EVENT       ,GVSE.TOTAL_WAITS       ,GVSE.TOTAL_TIMEOUTS       ,GVSE.TIME_WAITED       ,GVSE.AVERAGE_WAIT       ,GVSE.MAX_WAIT       ,GVSE.TIME_WAITED_MICRO       ,GVS.FAILOVER_TYPE       ,GVS.FAILOVER_METHOD       ,GVS.FAILED_OVER       ,SYSDATE FROM GV$SESSION_EVENT GVSE, GV$SESSION GVS WHERE GVSE.SID = GVS.SID AND        GVSE.INST_ID = GVS.INST_ID AND        GVS.AUDSID = userenv(’sessionid’); END; /
< Day Day Up >