用 logoff trigger 保留每個 session 的效能資訊

如果某個開發人員跟你說他的程式最近跑得很慢，你問他做了什麼修改，他說沒有。你說能不能跑給你看一下，他說程式只在每天凌晨跑。這時候收集每個session的效能歷史資訊就很重要。

event 10046對於不確定的session，收集資訊太多，成本太高。

statspack缺乏追蹤每一個資料庫連線的功能。

用l ogoff trigger 是個不錯的選擇，該 trigger 會在每個 session 正常退出時自動觸發。指令碼如下，可根據具體情況作適當修改。

--from <>

--建立表

-------------------------------
--
-- Create table
-- SESSION_EVENT_HISTORY
--
-------------------------------
create table session_event_history
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
-- SESSTAT_HISTORY
--
-------------------------------
create table sesstat_history
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;

--建立 DB logoff trigger

------------------------------
--
-- Create Trigger
--
------------------------------
-- 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 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 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 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 used by this session',
'recursive cpu usage',
'parse time cpu')
and c.sid = logoff_sid
and c.username = login_user;
end;