oracle 10g Collecting Real Time Wait Events

Oracle wait info



drop table real_time_session_waits
/
create table real_time_session_waits
( snap_tm date not null,
seq# number not null,
sid number not null,
event varchar2(64) not null,
p1 number,
p2 number,
p3 number,
wait_time number,
seconds_in_wait number,
object_id number )

/


SQL> @w
SQL> Enter time is seconds to run: 30



w.sql

truncate table real_time_session_waits -- comment out to keep history
/



accept tm prompt 'Enter time in seconds to run: '
variable tm_t number;

truncate table real_time_session_waits -- comment out to keep history
/




declare

iteration number;
start_time date;

begin
:tm_t := '&&tm';
select sysdate into start_time from dual;

iteration := 24*60*60/:tm_t;

while sysdate < start_time+1/iteration loop

insert into real_time_session_waits (SNAP_TM,
SEQ#,
SID,
EVENT,
P1,
P2,
P3,
WAIT_TIME,
SECONDS_IN_WAIT,
OBJECT_ID )
select sysdate,
a.seq#,
a.sid,
a.event,
a.p1,
a.p2,
a.p3,
a.wait_time,
a.seconds_in_wait,
b.row_wait_obj#
from v$session_wait a,
v$session b
where a.sid = b.sid
-- and a.sid = 'session id' -- only insert a single session
and a.event not in
('pmon timer',
'rdbms ipc message',
'smon timer',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'lock element cleanup',
'WMON goes to sleep'
);
dbms_lock.sleep(1); -- sleep for one second before checking and inserting next record.
-- Can take this out for more detail or change value for longer
-- runs.
end loop;
commit;
end;
/




set linesize 150
col sid format 99999 heading "Session ID"
col event format a45 heading "Wait Event"
col cnt heading "Wait Count"
col wait heading "Wait time(cs)"
col pct format 999.99 heading "% of Time"
select sid,event, count(*) cnt, sum(wait_time) wait
from real_time_session_waits
where wait_time > 0 -- this will limit wait times from sessions that have completed event and are > 1 sc.
-- and sid = XX -- change to limit by sid
-- filter out idle events
and event not in ('SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net message from client',
'SQL*Net more data to client')
group by sid,event
order by wait
/




set linesize 120
col event format a30

select * from ( select event, total_waits, total_timeouts, time_waited
from v$system_event
where event not like 'SQL*Net%'
and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
order by time_waited desc )
where rownum < 10;



SQL> select * from ( select event, total_waits, total_timeouts, time_waited
from v$system_event
where event not like 'SQL*Net%'
and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
order by time_waited desc )
where rownum < 10;
2 3 4 5 6
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
---------------------------------------------------------------- ----------- -------------- -----------
DIAG idle wait 4014401 4014401 401792698
shared server idle wait 66964 66963 200954924
Streams AQ: qmn coordinator idle wait 143494 71747 200953644
Streams AQ: qmn slave idle wait 73415 0 200952381
Streams AQ: waiting for time management or cleanup tasks 7134 4745 200891813
Space Manager: slave idle wait 405099 401211 200885816
jobq slave wait 1360408 1354900 68009146
VKRM Idle 23 0 67679254
control file parallel write 711619 0 1682008



SQL> SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;

COUNT(*) EVENT
---------- ----------------------------------------------------------------
2 DIAG idle wait
1 Space Manager: slave idle wait
1 Streams AQ: qmn coordinator idle wait
1 Streams AQ: waiting for time management or cleanup tasks
1 Streams AQ: qmn slave idle wait
1 VKTM Logical Idle Wait

6 rows selected.



SELECT count(*), event FROM v$session_wait
WHERE wait_time = 0
-- AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC; 2 3 4 5

COUNT(*) EVENT
---------- ----------------------------------------------------------------
16 rdbms ipc message
2 DIAG idle wait
1 smon timer
1 Space Manager: slave idle wait
1 VKTM Logical Idle Wait
1 Streams AQ: qmn coordinator idle wait
1 Streams AQ: waiting for time management or cleanup tasks
1 pmon timer
1 SQL*Net message from client
1 Streams AQ: qmn slave idle wait

10 rows selected.






clear breaks
break on event# on event on total_ms_waited
column event# format 999 heading "Event#"
column event format a30 heading "Event"
column wait_time_ms format 999,999 heading "Maximum|Wait Time (ms)"
column wait_count format 999,999,999,999 heading "Wait|Count"
column percent_wait_count format 990.00 heading "Percent|Wait Count"
column percent_wait_time format 990.00 heading "Percent|Wait Time"
column total_waited_s format 999,999,999,999 heading "Total|Waited (s)"
SELECT event#
, event
, SUM(wait_time_milli * wait_count / 1000) OVER(PARTITION BY event#) total_waited_s
, wait_time_milli wait_time_ms
, wait_count
, ROUND(100 * RATIO_TO_REPORT(wait_count) OVER(PARTITION BY event#),2) percent_wait_count
, ROUND(100 * RATIO_TO_REPORT(wait_time_milli * wait_count) OVER(PARTITION BY event#),2) percent_wait_time
FROM v$event_histogram
ORDER BY event, wait_time_ms



---

WHERE event IN (
'xblock change tracking buffer space'
,'buffer busy waits'
,'db file parallel read'
,'db file parallel write'
,'db file scattered read'
,'db file sequential read'
,'db file single write'
,'direct path read'
,'direct path read temp'
,'direct path write'
,'direct path write temp'
,'local write wait'
,'Log archive I/O'
,'log file parallel write'
,'log file sequential read'
,'log file single write'
)
ORDER BY event, wait_time_ms
----