[Query] Latch information (latchinfo.sql)

This query shows latch information in an Oracle database.

 

-- latchinfo.sql
col name for a30
SELECT name, gets, misses,
       (misses/decode(gets,0,1,gets))*100 as md, immediate_gets,
       immediate_misses,
       (immediate_misses/DECODE(immediate_gets,0,1,immediate_gets))*100 as im,
       sleeps
FROM v$latch
WHERE name IN ('library cache',
               'cache buffers chains',
               'shared pool','cache buffer handles',
               'checkpoint queue latch',
               'row cache objects',
               'session allocation',
               'redo writing',
               'cache buffers lru chain',
               'redo allocation',
               'parallel query alloc buffer',
               'process queue reference',
               'undo global data',
               'process allocation',
               'transaction allocation',
               'parallel query stats',
               'user lock',
               'done queue latch',
               'longop free list',
               'enqueues',
               'enqueue hash chains',
               'latch wait list',
               'dml lock allocation')
ORDER BY sleeps DESC;