使用DATABASE Log off收集oracle 等待事件信息

实例级别的监控，一直开启并且低开销：

建立基础表：

create table sys.sesstat_history tablespace EOL as
SELECT c.username,
       c.osuser,
       a.sid,
       c.serial#,
       c.paddr,
       c.process,
       c.logon_time,
       a.statistic#,
       b.name,
       a.value,
       SYSDATE AS logoff_timestamp
  FROM v$sesstat a, v$statname b, v$session c
 WHERE 1 = 2
create table sys.session_event_history tablespace EOL
as SELECT b.sid,
       b.SERIAL#,
       b.username,
       b.osuser,
       b.paddr,
       b.process,
       b.logon_time,
       b.type,
       a.event,
       a.total_waits,
       a.total_timeouts,
       a.time_waited,
       a.average_wait,
       a.max_wait,
       SYSDATE AS logoff_timestamp
  FROM v$session_event a, v$session b
 WHERE 1 = 2

创建触发器：

CREATE OR REPLACE TRIGGER sys.logoff_trig

  BEFORE logoff ON DATABASE

DECLARE

  logoff_sid  PLS_INTEGER;

  logoff_time DATE := SYSDATE;

BEGIN

  SELECT sid INTO logoff_sid FROM v$mystat WHERE rownum < 2;

  INSERT INTO sys.session_event_history

    (sid,

     serial#,

     username,

     osuser,

     paddr,

     process,

     logon_time,

     TYPE,

     event,

     total_waits,

     total_timeouts,

     time_waited,

     average_wait,

     max_wait,

     logoff_timestamp)

    SELECT b.sid,

           b.serial#,

           b.username,

           b.osuser,

           b.paddr,

           b.process,

           b.logon_time,

           b.type,

           a.event,

           a.total_waits,

           a.total_timeouts,

           a.time_waited,

           a.average_wait,

           a.max_wait,

           SYSDATE AS logoff_timestamp

      FROM v$session_event a, v$session b

     WHERE a.sid = b.sid

       AND b.username = login_user

       AND b.sid = logoff_sid;

  INSERT INTO sys.sesstat_history

    (username,

     osuser,

     sid,

     serial#,

     paddr,

     process,

     logon_time,

     statistic#,

     NAME,

     VALUE,

     logoff_timestamp)

    SELECT c.username,

           c.osuser,

           a.sid,

           c.serial#,

           c.paddr,

           c.process,

           c.logon_time,

           a.statistic#,

           b.name,

           a.value,

           logoff_time

      FROM v$sesstat a, v$statname b, v$session c

     WHERE a.statistic# = b.statistic#

       AND a.sid = c.sid

       AND b.name IN ('CPU used where call started',

                      'CPU used by this session',

                      'recursive cpu usage',

                      'parse time cpu')

       AND c.sid = logoff_sid

       AND c.username = login_user;

END;

查询消耗CPU的等待事件排名：

SQL> SELECT *

  FROM (SELECT a.sid, a.serial#, a.event, a.total_waits

          FROM session_event_history a

         ORDER BY a.time_waited DESC)

 WHERE rownum < 100;

  2    3    4    5  

       SID    SERIAL# EVENT                                                        TOTAL_WAITS

---------- ---------- ------------------------------------------------------------ -----------

      1858       8391 SQL*Net message from client                                       147692

      1437      52565 SQL*Net message from client                                        34305

       584      52801 SQL*Net message from client                                        85105

       585      40229 SQL*Net message from client                                       163331

       874       3263 SQL*Net message from client                                        77519

      1285      21797 SQL*Net message from client                                        19041

       861      25015 SQL*Net message from client                                          194

       726       9275 SQL*Net message from client                                        66724

      1717       1935 SQL*Net message from client                                        92394

      1014        883 SQL*Net message from client                                        34455

        21      10841 SQL*Net message from client                                        28685