Oraclue

Oracle internals, debugging and undocumented features

Run away from GUI DBA.. Script to report waiting sessions for RAC

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

Advertisements

3 responses to “Run away from GUI DBA.. Script to report waiting sessions for RAC

  1. Gary Chaika February 24, 2009 at 2:30 pm

    Good script. I have an old directory full of scripts I used to use in version 7.3.4 and 8i. I started to get lazy around 9i with OEM and TOAD. I just spent a few days converting these old scripts to work in 10g/11g RAC. It really is nice to be able to just log in and get answers without all the GUI overhead.

  2. oraclue February 24, 2009 at 5:53 pm

    We all get lazy 🙂

    Best thing is to balance GUI and command line tools.

  3. Shekhar February 25, 2009 at 1:56 am

    Better yet run script in TOAD.
    – easy to type & navigate
    – nice result so don’t need to format
    – still keep your knowledge of dba_* & v$ view up-to-date

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: