实例级别的监控，一直开启并且低开销：
建立基础表：
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
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
