Few days ago I wrote the post about systematic Unix troubleshooting, I mentioned my sw.sql script for quick session wait monitoring from sqlplus.
I had forgot to upload the latest version of my script, which is able to decode the enqueue and latch names from P1 values of V$SESSION_WAIT.

I have uploaded it now and you can get it from here: http://www.tanelpoder.com/files/scripts/sw.sql

This script allows you to easily see the lock or latch session is waiting on even on before Oracle 10g.

Also, not all latches and enqueues are externalized into separate wait events in 10g+, but only the most usual ones.

The below output is from Oracle 11.1.0.6 where only 27 latch types of total 496 do have their own wait event:

SQL> select count(*) from v$latch;

  COUNT(*)
----------
       496

SQL> select name from v$event_name where name like 'latch:%';

NAME
----------------------------------------------------------------
latch: cache buffers chains
latch: redo writing
latch: redo copy
latch: Undo Hint Latch
latch: In memory undo latch
latch: MQL Tracking Latch
latch: row cache objects
latch: shared pool
latch: session allocation
latch: messages
latch: enqueue hash chains
latch: ges resource hash list
latch: gcs resource hash
latch: cache buffers lru chain
latch: checkpoint queue latch
latch: cache buffer handles
latch: object queue header operation
latch: redo allocation
latch: gc element
latch: undo global data
latch: Change Notification Hash table latch
latch: change notification client cache latch
latch: lob segment hash table latch
latch: lob segment query latch
latch: lob segment dispenser latch
latch: virtual circuit queues
latch: parallel query alloc buffer

27 rows selected.

Not all enqueue types have their own wait either:

SQL> select count(*) from v$lock_type;

  COUNT(*)
----------
       187

SQL> select count(distinct(substr(name,6,2))) locks from v$event_name where name like 'enq:%';

     LOCKS
----------
       161

The SUBSTR trick was needed because some enqueues show different wait events depending for what purpose the enqueue is required.

Anyway, here is the output from my script. Note the syntax how I pass a SQL statement as a parameter (enclosed in double quotes) to dynamically generate a list of SIDs of interest (you may need to make your browsing window wider to see all output. Also note that the blogging engine keeps replacing normal quotes & double-quotes with “nicer” looking ones, so directly pasting these commands to sqlplus may not work).

SQL> @sw "select sid from v$session where type != 'BACKGROUND'"

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    144 WAITING SQL*Net message from client                   27328           3 1650815232          1          0
    127 WAITING SQL*Net message from client                    7008           4 1650815232          1          0
    170 WAITING SQL*Net message from client                    1279         221 1650815232          1          0
    118 WAITING enq: TX - row lock contention                    35        2053 1415053318     458777       1454 0x54580006: TX mode 6
    116 WAITING jobq slave wait                                   3           2          0          0          0
    114 WAITING kksfbc child completion                        2694           0          0          0          0
    111 WAITING latch: cache buffers chains                   10673           0 1586048564        141          0 0x5E892E34: cache buffers chains[c3482]
    119 WORKING On CPU / runqueue                              5703           0 1413697536          1          0
    117 WORKING On CPU / runqueue                             51328           0 1586048564        141          0
    132 WORKING On CPU / runqueue                              7457           0 2314369474    7667712    6263293

10 rows selected.

SQL> @sw "select sid from v$session where type != 'BACKGROUND'"

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    144 WAITING SQL*Net message from client                   27328           3 1650815232          1          0
    127 WAITING SQL*Net message from client                    7008           4 1650815232          1          0
    170 WAITING SQL*Net message from client                    1279         222 1650815232          1          0
    114 WAITING cursor: pin S wait on X                        2769           0 3157682392    8650752     327681
    118 WAITING enq: TX - row lock contention                    35        2054 1415053318     458777       1454 0x54580006: TX mode 6
    116 WAITING jobq slave wait                                   3           2          0          0          0
    111 WAITING latch: shared pool                            10765           0  537529180        277          0 0x200A0B5C: shared pool[c1]
    117 WORKING On CPU / runqueue                             51423           0  537529180        277          0
    119 WORKING On CPU / runqueue                              5709           0 1413697536          1          0
    132 WORKING On CPU / runqueue                              7577           0 1586048564        141          0

10 rows selected.

In addition to latch name, this script also shows the latch address in SGA and the child latch number, which allow identifying if several sessions are waiting on the same actual latch or just another child latch with same name.

Enjoy ;)