用 logoff trigger 保留每个 session 的性能信息(转)

OWI -- 用 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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-242158/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-242158/