实例级别的监控，一直开启并且低开销：
建立基础表：
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
