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

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

建立基础表：

create table sys.sesstat_history tablespace EOL asSELECT 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 = 2create table sys.session_event_history tablespace EOLas 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 DATABASEDECLARE  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