To check the Active Waiting sessions


Select
sysdate
sample_time,
s.sid               “SESSION_ID”    ,
decode(w.WAIT_TIME,
0,’WAITING’,’ON CPU’) “SESSION_STATE”,
s.serial#         “SESSION_SERIAL#”,
s.user#           “USER_ID”,
s.sql_address     “SQL_ADDRESS”,
s.sql_hash_value      “SQL_HASH” ,
s.command         “SQL_OPCODE”  ,
s.type            “SESSION_TYPE”  ,
w.event           “EVENT”,
w.p1              “P1”          ,
w.p2              “P2”        ,
w.p3              “P3”         ,
s.ROW_WAIT_OBJ#   “CURRENT_OBJ#”,
s.ROW_WAIT_FILE#  “CURRENT_FILE#”,
s.ROW_WAIT_BLOCK#
“CURRENT_BLOCK#”,
s.program         “PROGRAM”,
s.module     “MODULE”, 
s.action    “ACTION”
from
v$session
s ,
v$session_wait
w
where
s.sid
!= ( select distinct sid from v$mystat 
where rownum < 2 ) and
w.sid
= s.sid and
(  (
/*
status Active – seems inactive & “on cpu”=> not on CPU */
w.wait_time
!= 0  and 
/* on CPU  */
s.status=’ACTIVE’  /* ACTIVE */
)
or
lower(w.event)  not in  
/* waiting and the wait event is not idle */
(
‘queue
monitor wait’,
‘null
event’,
‘pl/sql
lock timer’,
‘px
deq: execution msg’,
‘px
deq: table q normal’,
‘px
idle wait’,
‘sql*net
message from client’,
‘sql*net
message from dblink’,
‘dispatcher
timer’,
‘lock
manager wait for remote message’,
‘pipe
get’,
‘pmon
timer’,
‘queue
messages’,
‘rdbms
ipc message’,
‘slave
wait’,
‘smon
timer’,
‘virtual
circuit status’,
‘wakeup
time manager’,
‘i/o
slave wait’,
‘jobq
slave wait’,
‘queue
monitor wait’,
‘SQL*Net
message from client’
)
);
Recent Posts