Instance-level monitoring, open and low overhead:
To create a base table:
create table sys.sesstat_history tablespace eol asselect 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 = 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 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, &nbsP; 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;
Query CPU-consuming wait events rank:
sql> select * from (select a.sid, a.serial#, a.event, a.total _waits from session_event_history a &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;ORDER&NBSP;BY&NBSP;A.TIME_WAITED&NBSP;DESC) WHERE rownum < 100; 2 3 4 5 sid serial# event total_waits---------- ---------- ------------------------------------------------------- ----- -----------&NBSp; 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
Collect Oracle Wait event information using database Log off