Miladin Modrakovic's Blog: Oraclue

Found this one in my old DBA folder.. It’s time to use more sqlplus command line than Toad and OEM ( GUI DBA )..

set serverout on size 999999
     declare
     begin
     dbms_output.put_line(‘ ‘);
     dbms_output.put_line(‘************* Start report for WAITING sessions with current SQL ***************’);
     for x in (select vs.inst_id, vs.sid || ‘,’ || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
     vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
     from gv$session_wait vsw, gv$session vs
     where vsw.sid = vs.sid
     and vsw.inst_id = vs.inst_id
     and vs.type <> ‘BACKGROUND’
     and vsw.event NOT IN (‘rdbms ipc message’
     ,’smon timer’
     ,’pmon timer’
     ,’SQL*Net message from client’
     ,’lock manager wait for remote message’
     ,’ges remote message’
     ,’gcs remote message’
     ,’gcs for action’
     ,’client message’
     ,’pipe get’
     ,’Null event’
     ,’PX Idle Wait’
     ,’single-task message’
     ,’PX Deq: Execution Msg’
     ,’KXFQ: kxfqdeq – normal deqeue’
     ,’listen endpoint status’
     ,’slave wait’
     ,’wakeup time manager’))
     loop
     begin
     dbms_output.put_line(‘Event WaitState InstID SidSerial LastCallEt SecondsInWait’);
     dbms_output.put_line(‘************************* ******************** ****** *********** ********** *************’);
     dbms_output.put_line(rpad(x.event,25) ||’ ‘|| rpad(x.state,20) ||’ ‘|| lpad(x.inst_id,6) ||’ ‘|| lpad(x.sidser,11) ||’
     ‘|| lpad(x.last_call_et,10) ||’ ‘|| lpad(x.seconds_in_wait,13));
     dbms_output.put_line(‘ SQLText ‘);
     dbms_output.put_line(‘****************************************************************’);
     for y in (select sql_text
     from gv$sqltext
     where address = x.sql_address
     and hash_value = x.sql_hash_value
     and inst_id = x.inst_id
     order by piece)
     loop
     dbms_output.put_line(y.sql_text);
     end loop;
     end;
     end loop;
     dbms_output.put_line(‘************** End report for sessions waiting with current SQL ****************’);
     dbms_output.put_line(‘ ‘);
     end;
     /

Output look like:

************* Start report for WAITING sessions with current SQL ***************
Event WaitState InstID SidSerial LastCallEt SecondsInWait
************************* ******************** ****** *********** **********
*************
PX Deq: Execute Reply     WAITING                   1     79,2619

0             0
SQLText
****************************************************************
SELECT VS.INST_ID, VS.SID || ‘,’ || VS.SERIAL# SIDSER, VS.SQL_AD
DRESS, VS.SQL_HASH_VALUE, VS.LAST_CALL_ET, VSW.SECONDS_IN_WAIT,
VSW.EVENT, VSW.STATE FROM GV$SESSION_WAIT VSW, GV$SESSION VS WHE
RE VSW.SID = VS.SID AND VSW.INST_ID = VS.INST_ID AND VS.TYPE <>
‘BACKGROUND’ AND VSW.EVENT NOT IN (‘rdbms ipc message’ ,’smon ti
mer’ ,’pmon timer’ ,’SQL*Net message from client’ ,’lock manager
wait for remote message’ ,’ges remote message’ ,’gcs remote mes
sage’ ,’gcs for action’ ,’client message’ ,’pipe get’ ,’Null eve
nt’ ,’PX Idle Wait’ ,’single-task message’ ,’PX Deq: Execution M
sg’ ,’KXFQ: kxfqdeq – normal deqeue’ ,’listen endpoint status’ ,
‘slave wait’ ,’wakeup time manager’)
Event WaitState InstID SidSerial LastCallEt SecondsInWait
************************* ******************** ****** *********** **********
*************
virtual circuit status    WAITED KNOWN TIME         1       84,20

22383         22383
SQLText
****************************************************************
Event WaitState InstID SidSerial LastCallEt SecondsInWait
************************* ******************** ****** *********** **********
*************
virtual circuit status    WAITED KNOWN TIME         1       90,67

22383         22383
SQLText
****************************************************************
Event WaitState InstID SidSerial LastCallEt SecondsInWait
************************* ******************** ****** *********** **********
*************
virtual circuit status    WAITED KNOWN TIME         1     97,1045