DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 24, 2012

Oracle Wait Event Analysis


Wait event
column seq# format 99999
column EVENT format a30
column p2 format 999999
column STATE format a10
column WAIT_T format 9999
select SID,SEQ#,EVENT,P1,P2,WAIT_TIME WAIT_T,SECONDS_IN_WAIT,STATE
from v$session_wait
where sid = '&sid' ;
WAIT EVENT DETAILS COMPLETE

Wait event List in DB

select event,count(event) "EVENT_COUNT" from v$session_wait group by event order by event;
To Find Wait Events for a given Session

column seq# format 99999                                                        
column EVENT format a30                                                        
column p2 format 9999                                                          
column STATE format a10                                                         
column WAIT_T format 9999                                                      
select SID,SEQ#,EVENT,P1,P2,WAIT_TIME WAIT_T,SECONDS_IN_WAIT,STATE
from gv$session_wait
where sid =  '&sid' ;

To Find Wait Event details of a specific wait event

column seq# format 99999                                                       
column EVENT format a30                                                        
column p2 format 9999                                                          
column STATE format a10                                                        
column WAIT_T format 9999                                                      
select SID,SEQ#,EVENT,P1,P2,WAIT_TIME WAIT_T,SECONDS_IN_WAIT,STATE
from gv$session_wait
where event like '%cursor: pin S%';

Count of sessions ordered by wait event associated

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

To find Wait event Most of the time the session waited for

select event,TOTAL_WAITS ,TOTAL_TIMEOUTS,TIME_WAITED from gv$session_event where sid=54
order by TIME_WAITED

To find the list of wait events and count of associated sessions

select count(sid),event from v$session_wait group by event order by 1;

No of events with sid's

prompt Sessions Wait Event Summary            
select EVENT,COUNT(SID)
from v$session_wait
GROUP BY EVENT;

Obtaining a parameter defined

col value for a10
col description for a30
select name,value,description from v$parameter where name like '%timed_statistics%';


Wait events

set linesize 152
set pagesize 80
column EVENT format a30
select *  from  v$system_event
where  event like '%wait%';

Sessions waiting "sql*net message from client"

prompt Sessions having Wait Event "sql*net message from client"          
select program,module,count(s.sid) from v$session s, v$session_Wait w
where w.sid=s.sid and w.event='SQL*Net message from client' group by program,module  having
count(s.sid)>5 order by count(s.sid);

Sessions having Wait Event "sql*net message from client" from more than 1Hour

select program,module,count(s.sid) from v$session s, v$session_Wait w
where w.sid=s.sid
and s.last_call_et > 3600
and w.event='SQL*Net message from client' group by program,module  having
count(s.sid)>5 order by count(s.sid);

Sessions having Wait Event "sql*net message from client"

select s.sid,s.process,S.STATUS,s.program,s.module,s.sql_hash_value,s.last_call_et/3600 Last_Call_Et_HRS
from v$session s, v$session_Wait w
where w.sid=s.sid and w.event='SQL*Net message from client'
and s.module='&Module_name'
order by 6 desc; 

Segment Statistics

select
object_name,
statistic_name,
value
from
V$SEGMENT_STATISTICS
where object_name ='SOURCE$';

select    statistic_name,  count(object_name)  from V$SEGMENT_STATISTICS
where STATISTIC_NAME like 'physical%'
group by statistic_name;

select distinct(STATISTIC_NAME) from v$SEGMENT_STATISTICS;  

V$SYSTEM_EVENT

This view contains information on total waits for an event.
Note that the TIME_WAITED and AVERAGE_WAIT columns will contain
a value of zero on those platforms that do not support a fast timing mechanism.
If you are running on one of these platforms and you want this column to reflect
true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file;
doing this will have a small negative effect on system performance.

Buffer Busy waits

SELECT * FROM v$event_name WHERE name = 'buffer busy waits';

SELECT   sid, event, state, seconds_in_wait, wait_time, p1, p2, p3
FROM     v$session_wait
WHERE    event = 'buffer busy waits'
ORDER BY sid;
select * from v$waitstat;

SELECT   sid, event, state, seconds_in_wait, wait_time, p1, p2, p3
FROM     v$session_wait
WHERE    event = 'buffer busy waits'
ORDER BY sid;

Segment details from File number

SELECT owner, segment_name, segment_type
FROM   dba_extents
WHERE  file_id = &absolute_file_number
AND    &block_number BETWEEN block_id AND block_id + blocks -1;

Direct path write

SELECT * FROM v$event_name WHERE name = 'direct path write';         

SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
FROM   dba_data_files
WHERE  file_id = 201;           
SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
FROM   dba_data_files
WHERE  file_id = 201;

Total waits/time waited/max wait for a session

SELECT   event, total_waits, time_waited, max_wait
FROM     v$session_event
WHERE    sid = 47
ORDER BY event;

SELECT   A.name, B.value
FROM     v$statname A, v$sesstat B
WHERE    A.statistic# = 12
AND      B.statistic# = A.statistic#
AND      B.sid = 47;           
Sessions Ordered by Wait event in Database

set lines 150
set pages 500
col event for a50
select event,count(event) "EVENT_COUNT" from v$session_event group by event order by event;

No comments: