Show latch contention (from gv$latch) with miss > 0
PERC INST_ID ADDR LATCH# LEVEL# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 SLEEP8 SLEEP9 SLEEP10 SLEEP11 WAIT_TIME
------- ---------- ---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
8.51 2 0000000060017448 128 5 gcs remastering latch 1106929 94149 0 3 0 0 0 94150 0 0 0 0 0 0 0 0 0 0 0 0
8.30 1 0000000060017448 128 5 gcs remastering latch 825785 68555 0 401 58 0 0 68556 0 0 0 0 0 0 0 0 0 0 0 0
3.78 1 000000006000CBA8 24 6 parameter table management 174978 6606 1 0 0 0 0 6605 0 0 0 0 0 0 0 0 0 0 0 81
2.63 1 000000006002B430 239 3 dml lock allocation 15448 406 0 0 0 0 0 406 0 0 0 0 0 0 0 0 0 0 0 0
1.47 1 0000000060042CE0 429 3 session state list latch 75953 1114 0 0 0 0 0 1114 0 0 0 0 0 0 0 0 0 0 0 0
1.02 1 000000006000E748 47 5 resmgr:free threads list 70791 722 3 0 0 0 0 720 0 0 0 0 0 0 0 0 0 0 0 81
.88 1 000000006002B860 243 6 dummy allocation 69621 616 0 0 0 0 0 616 0 0 0 0 0 0 0 0 0 0 0 0
.65 1 000000006000A0C0 10 11 interrupt manipulation 147677 957 0 0 0 0 0 957 0 0 0 0 0 0 0 0 0 0 0 0
.61 2 000000006000D4C8 31 5 enqueue freelist latch 164 1 0 87756498 38 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0
.59 1 000000006003F748 393 6 parallel query alloc buffer 420275 2499 1 0 0 0 0 2498 0 0 0 0 0 0 0 0 0 0 0 37
.53 2 0000000060015640 113 5 gcs opaque info freelist 789006 4221 0 0 0 0 0 4221 0 0 0 0 0 0 0 0 0 0 0 0
.43 1 000000006000C610 22 5 object stats modification 39769 171 1 0 0 0 0 170 0 0 0 0 0 0 0 0 0 0 0 4312
.33 1 0000000060015640 113 5 gcs opaque info freelist 679304 2217 0 0 0 0 0 2217 0 0 0 0 0 0 0 0 0 0 0 0
.32 1 000000006004F048 549 1 JS Sh mem access 1247 4 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 49
.32 1 000000006003F060 388 5 process queue 193643 618 2 0 0 0 0 616 0 0 0 0 0 0 0 0 0 0 0 481
.30 1 0000000060036A08 307 7 shared pool 7850899 23494 568 0 0 0 0 22960 0 0 0 0 0 0 0 0 0 0 0 2905024
.29 1 000000006000A018 9 5 call allocation 382285 1104 26 2 0 0 0 1078 0 0 0 0 0 0 0 0 0 0 0 2256
.27 1 000000006002E4D8 258 3 corrupted undo seg latch 2586 7 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0 0 0 0
.27 1 0000000060049B40 499 2 ASM map operation freelist 23905 64 1 0 0 0 0 63 0 0 0 0 0 0 0 0 0 0 0 1240
.26 2 0000000060030FD8 268 0 space background task latch 702537 1842 27 389212 10 0 0 1815 0 0 0 0 0 0 0 0 0 0 0 6290
.24 2 000000006001B428 153 5 active checkpoint queue latch 802556 1934 2 2 0 0 0 1932 0 0 0 0 0 0 0 0 0 0 0 72
.21 1 0000000060030FD8 268 0 space background task latch 700923 1490 15 390366 18 0 0 1475 0 0 0 0 0 0 0 0 0 0 0 2861
.21 1 000000006001B428 153 5 active checkpoint queue latch 741950 1530 2 2 0 0 0 1528 0 0 0 0 0 0 0 0 0 0 0 56
.14 1 0000000060022778 187 5 redo allocation 827854 1156 4 5581066 5621 0 0 1152 0 0 0 0 0 0 0 0 0 0 0 153
.12 1 00000000600128C8 91 1 ges resource hash list 17093270 20529 1039 351345 73 0 0 19494 0 0 0 0 0 0 0 0 0 0 0 74104
col Perc for 999.99
select ( misses/gets ) * 100 as Perc, inst_id, addr, latch#, level#, name, gets, misses,sleeps,
immediate_gets, immediate_misses, waiters_woken,
waits_holding_latch, spin_gets,
sleep1, sleep2, sleep3, sleep4, sleep5, sleep6, sleep7, sleep8, sleep9, sleep10, sleep11, wait_time
from gv$latch
where misses > 0
order by Perc desc;