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;
Recommended Posts

Start typing and press Enter to search