column name format a30
column pct_of_gets format 99.00 heading "Pct of|Gets"
column pct_of_misses format 99.00 heading "Pct of|Misses"
column pct_of_sleeps format 99.00 heading "Pct of|Sleeps"
column pct_of_wait_time format 99.00 heading "Pct of|Wait Time"
set pagesize 100
WITH latch AS (
SELECT name,
ROUND(gets * 100 / SUM(gets) OVER (), 2) pct_of_gets,
ROUND(misses * 100 / SUM(misses) OVER (), 2) pct_of_misses,
ROUND(sleeps * 100 / SUM(sleeps) OVER (), 2) pct_of_sleeps,
ROUND(wait_time * 100 / SUM(wait_time) OVER (), 2)
pct_of_wait_time
FROM v$latch)
SELECT *
FROM latch
WHERE pct_of_wait_time > .1 OR pct_of_sleeps > .1
ORDER BY pct_of_wait_time DESC;
Pct of Pct of Pct of Pct of
NAME Gets Misses Sleeps Wait Time
------------------------------ ------ ------ ------ ---------
cache buffers chains 68.60 88.61 94.45 71.50
space background task latch .01 .20 2.40 14.76
ksuosstats global area .00 .00 .01 2.61
cache buffers lru chain .87 .05 1.20 2.20
archive process latch .00 .00 .25 1.90
shared pool .12 .00 .02 1.50
object stats modification .01 .00 .00 1.42
object queue header operation 11.91 .09 .47 1.24
undo global data 3.96 10.34 .74 1.17
process allocation .00 .00 .00 .52
LGWR NS Write .03 .00 .21 .43
Comments