Wait Events in Oracle Database

 Wait Events in Oracle Database

Usage as per wait class

col WAIT_CLASS format a25 
col TIME_SECONDS format a25 
SELECT WAIT_CLASS, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS FROM V$SYSTEM_WAIT_CLASS WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle' ORDER BY TIME_WAITED; 

Check wait class session

SELECT sid, serial#, sql_id, event, wait_time_micro 
FROM v$session 
WHERE wait_class = '&class_name' 
AND status = 'ACTIVE';

Check in Wait Class for more details

set linesize 190

set pages 5000

col NAME format A50

col CLASS_LIST format a60

SELECT NAME,

       RTRIM(

         CASE WHEN BITAND(CLASS, 1)   = 1   THEN 'User,'   END ||

         CASE WHEN BITAND(CLASS, 2)   = 2   THEN 'Redo,'   END ||

         CASE WHEN BITAND(CLASS, 4)   = 4   THEN 'Enqueue,' END ||

         CASE WHEN BITAND(CLASS, 8)   = 8   THEN 'Cache,'  END ||

         CASE WHEN BITAND(CLASS, 16)  = 16  THEN 'OS,'     END ||

         CASE WHEN BITAND(CLASS, 32)  = 32  THEN 'RAC,'    END ||

         CASE WHEN BITAND(CLASS, 64)  = 64  THEN 'SQL,'    END ||

         CASE WHEN BITAND(CLASS, 128) = 128 THEN 'Debug,'  END

       , ',') AS CLASS_LIST,

       VALUE

FROM   V$SYSSTAT

ORDER  BY CLASS_LIST, NAME;

System Stats for specific level

 SELECT NAME, VALUE

FROM V$SYSSTAT

WHERE (NAME LIKE 'table%' or NAME LIKE 'index%') AND VALUE<>0

ORDER BY NAME; 

Which SID and User consuming particular Resource

col USERNAME format a50 

col NAME format a30 

SELECT S.SID, H.USERNAME, T.NAME, S.VALUE FROM V$SESSTAT S, V$STATNAME T, V$SESSION H WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID AND T.NAME = 'parse time cpu' AND H.USERNAME IS NOT NULL ORDER BY S.VALUE DESC;

Which SID and User consuming particular Resource with SQL_TEXT

col SQL_TEXT format a25 

SELECT S.SID, H.USERNAME, T.NAME, S.VALUE, SUBSTR(Q.SQL_TEXT,1,25) SQL_TEXT FROM V$SESSTAT S, V$STATNAME T, V$SESSION H, V$SQL Q WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID AND H.SQL_ID=Q.SQL_ID(+) AND T.NAME = 'parse time cpu' AND H.USERNAME IS NOT NULL ORDER BY S.VALUE DESC;

This will give my session details

SELECT S.SID, H.USERNAME, T.NAME, S.VALUE FROM V$MYSTAT S, V$STATNAME T, V$SESSION H WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID AND T.NAME = 'parse time cpu' AND H.USERNAME IS NOT NULL ORDER BY S.VALUE DESC;

col EVENT format a40 

col WAIT_CLASS format a11 

SELECT EVENT, AVERAGE_WAIT, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, WAIT_CLASS FROM V$SYSTEM_EVENT WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle' ORDER BY TIME_WAITED;

 

col EVENT format a40

col WAIT_CLASS format a11

SELECT EVENT, AVERAGE_WAIT, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, WAIT_CLASS

FROM V$SYSTEM_EVENT

WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle'

ORDER BY TIME_WAITED;

col WAIT_CLASS format a25 col TIME_SECONDS format a25 SELECT WAIT_CLASS, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS FROM V$SYSTEM_WAIT_CLASS WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle' ORDER BY TIME_WAITED;

 SELECT WAIT_CLASS, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS,

'%' || ROUND(RATIO_TO_REPORT(TIME_WAITED) over ()*100) PCT

FROM V$SYSTEM_WAIT_CLASS

WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle'

ORDER BY TIME_SECONDS;

set linesize 180 

col EVENT format a25 

SELECT E.SID, S.USERNAME, E.EVENT, TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS, E.WAIT_CLASS FROM V$SESSION_EVENT E, V$SESSION S WHERE E.SID=S.SID AND ROUND(E.TIME_WAITED/100)>0 AND S.USERNAME='&user' AND E.EVENT='log file sync' ORDER BY TIME_WAITED;

col USERNAME format a4 

col WAIT_CLASS format a10 

SELECT SID, USERNAME, EVENT, WAIT_TIME, WAIT_CLASS FROM V$SESSION WHERE USERNAME='SOE' AND EVENT='log file sync' ORDER BY WAIT_TIME;

 SELECT SID, EVENT

FROM V$SESSION

WHERE STATE='WAITING' AND USERNAME ='SOE' AND WAIT_CLASS<>'Idle';

col SESSION_WAITS format a100

SELECT 'SID: '|| SID||

CHR(10)||'USERNAME: '|| USERNAME||

CHR(10)||'STATE: '|| STATE||

CHR(10)||'EVENT: '|| EVENT||

CHR(10)||'WAIT_TIME: '|| WAIT_TIME||

CHR(10)||'SECONDS_IN_WAIT: '|| SECONDS_IN_WAIT||

CHR(10)||'WAIT_CLASS: '|| WAIT_CLASS||

CHR(10)||'P1TEXT: '|| P1TEXT||

CHR(10)||'P1: '|| P1||

CHR(10)||'P2TEXT: '|| P2TEXT||

CHR(10)||'P2: '|| P2 ||

CHR(10)||'P3TEXT: '|| P3TEXT||

CHR(10)||'P3: ' || P3 AS SESSION_WAITS

FROM V$SESSION

WHERE EVENT LIKE 'enq: TX%'

ORDER BY WAIT_TIME;

SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY WHERE EVENT LIKE 'enq: TX%'; 

SELECT E.EVENT, TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS FROM V$SESSION_EVENT E, V$SESSION S WHERE E.SID=S.SID AND S.USERNAME='&username' AND E.EVENT LIKE 'enq: TX%' ORDER BY TIME_WAITED; 

col SESSION_WAITS format a100 

SELECT 'SID: '|| SID|| CHR(10)||'EVENT: '|| EVENT|| CHR(10)||'WAIT_TIME: '|| WAIT_TIME|| CHR(10)||'P1TEXT: '|| P1TEXT|| CHR(10)||'P1: '|| P1|| CHR(10)||'P2TEXT: '|| P2TEXT|| CHR(10)||'P2: '|| P2 || CHR(10)||'P3TEXT: '|| P3TEXT|| CHR(10)||'P3: ' || P3 AS SESSION_WAITS FROM V$SESSION_WAIT_HISTORY WHERE EVENT LIKE 'enq: TX%' ORDER BY WAIT_TIME;