Shulou(Shulou.com)06/01 Report--

Instance-level monitoring is enabled all the time with low overhead:

Establish the basic table:

Create table sys.sesstat_history tablespace EOL asSELECT c.username, c.osuser, a.sid, c. Serializations, c.paddr, c.process, c.logon_time, a.statisticals, b.name, a.value, SYSDATE AS logoff_timestamp FROM v$sesstat a, v$statname b, v$session c WHERE 1 = 2create table sys.session_event_history tablespace EOLas 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 a trigger:

CREATE OR REPLACE TRIGGER sys.logoff_trig BEFORE logoff ON DATABASEDECLARE 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.serializations, a.event, a.total_waits FROM session_event_history an 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.