OWI监控和收集方法——将database logoff trigger用作数据收集器（1）

在第一时间指出性能问题的正确解决办法是每一个从事调整的实践者的终极目标。若性能问题是过去发生的，而让你现在去确定根本原因并给出解决办法以防止其再次发生，则可能会让你感到有些畏惧。良好的历史性能数据收集有助于根本原因的分析和标识。这里记录一个会话级等待事件监控和历史数据收集的脚本。这样dba就不再畏惧接到电话说刚才、昨天什么什么慢的情况了。

首先建立2个由system用户拥有的表。也可以选择另外一个用户，第一张表，session_event_history，存储所有等待事件；第二张表，sesstat_history，则存储会话的CPU统计。

create table system.session_event_history 
tablespace <name>
storage (freelist groups <value>)
initrans <value>
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 table system.sesstat_history 
tablespace < name >
storage (freelist groups <value>)
initrans <value>
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;

一个好的分区方案可以让你快速清除旧的数据。后面创建触发器：

-- This script creates a database logoff trigger for the purpose of 
-- collecting historical performance data during logoffs. 
-- It is applicable to Oracle8i Database and above. 
-- You must be connected as "/ as sysdba" to create this trigger.

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 system.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 a.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,
           logoff_time
      from v$session_event a, v$session b
     where a.sid = b.sid
       and b.username = login_user
       and b.sid = logoff_sid;
  -- If you are on earlier releases of Oracle9i Database, you should check to
  -- see if your database is affected by bug #2429929, which causes 
  -- misalignment of SID numbers between the V$SESSION_EVENT and V$SESSION
  -- views. The SID number in the V$SESSION_EVENT view is off by 1. 
  -- If your database is affected, please replace the above 
  -- "where a.sid = b.sid" with "where b.sid = a.sid + 1".

  insert into system.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 when call started',  --调用开始时使用的CPU时间。
                      'CPU used by this session',    --会话自用户调用起至结束期间使用CPU时间的总量。
                      'recursive cpu usage',         --非用户调用（递归调用）使用的CPU时间总量。
                      'parse time cpu')              --（硬和软）解析使用的CPU时间的总量。
       and c.sid = logoff_sid
       and c.username = login_user;
end;
/

总结：
在数据库上部署了这个脚本，就能够及时回顾以发现每一个前台进程在数据库中所作的工作。
收集了v$session_event和v$sesstat中的数据。
会话级粒度，实例级粒度太粗了。
一直开启和低开销。
历史数据存储，允许你及时回顾。
如果会话被kill或者shutdown、abort数据库，会话信息来不及收集。