Following on from my post yesterday, it belatedly occurred to me that I should have included some more information. Rather than add an addendum to the original where it might be missed by anyone interested that had already read it, I’m going kick off another post.
By the time, I got involved the problem had been ongoing for 25 minutes. All-in-all, the “incident” lasted 40 minutes. To get an accurate report, we need a snap that spans the problem, i.e. start snap is before problem started, end snap is post incident resolution. So, what follows are selected highlights from from a two hour AWR report.
Note the RC enqueues.
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
XXXX 3639132207 XXXX 1 29-Aug-08 18:50 11.1.0.6.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
xxxxxxxxxxxxxxxx Linux 64-bit for AMD 16 16 4 31.43
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 4824 12-Nov-08 11:00:50 411 7.9
End Snap: 4827 12-Nov-08 13:00:59 355 7.5
Elapsed: 120.15 (mins)
DB Time: 5,716.93 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 1,536M 1,920M Std Block Size: 8K
Shared Pool Size: 3,648M 3,264M Log Buffer: 7,252K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 47.6 5.2 0.01 0.34
DB CPU(s): 1.2 0.1 0.00 0.01
Redo size: 249,580.9 27,444.3
Logical reads: 70,998.1 7,807.0
Block changes: 1,765.9 194.2
Physical reads: 643.1 70.7
Physical writes: 79.0 8.7
User calls: 140.9 15.5
Parses: 278.0 30.6
Hard parses: 1.3 0.1
W/A MB processed: 11,776,252.5 1,294,933.1
Logons: 0.7 0.1
Executes: 3,681.1 404.8
Rollbacks: 0.2 0.0
Transactions: 9.1
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.56 In-memory Sort %: 100.00
Library Hit %: 99.91 Soft Parse %: 99.55
Execute to Parse %: 92.45 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 98.46
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 56.10 52.34
% SQL with executions>1: 87.04 91.24
% Memory for SQL w/exec>1: 76.08 82.45
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch free 128,725 541,831 4209 158.0 Other
DB CPU 8,875 2.6
db file sequential read 1,497,779 2,158 1 .6 User I/O
SQL*Net message from dblink 24,546 1,263 51 .4 Network
enq: RC - Result Cache: Conten 4,056 854 210 .2 Applicatio
Host CPU (CPUs: 16 Cores: 16 Sockets: 4)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
4.95 3.93 7.9 1.1 2.4 90.8
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 8.4
% of busy CPU for Instance: 92.1
%DB time waiting for CPU - Resource Mgr: 0.0
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 32,187.8 32,187.8
SGA use (MB): 6,144.0 6,144.0
PGA use (MB): 11,531.6 11,354.8
% Host Mem used for SGA+PGA: 54.91 54.91
Time Model Statistics DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> Total time in database user-calls (DB Time): 343015.7s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 341,838.1 99.7
PL/SQL execution elapsed time 314,071.3 91.6
DB CPU 8,875.0 2.6
RMAN cpu time (backup/restore) 498.2 .1
parse time elapsed 280.7 .1
hard parse elapsed time 153.5 .0
connection management call elapsed time 130.5 .0
repeated bind elapsed time 30.3 .0
PL/SQL compilation elapsed time 24.0 .0
Java execution elapsed time 22.1 .0
hard parse (sharing criteria) elapsed time 21.4 .0
hard parse (bind mismatch) elapsed time 7.5 .0
sequence load elapsed time 6.9 .0
failed parse elapsed time 0.3 .0
inbound PL/SQL rpc elapsed time 0.0 .0
DB time 343,015.7
background elapsed time 1,339.8
background cpu time 845.1
-------------------------------------------------------------
Operating System Statistics - Detail DB/Inst: XXXX/XXXX Snaps: 4824-4827
Snap Time Load %busy %user %sys %idle %iowait
--------------- -------- -------- -------- -------- -------- --------
12-Nov 11:00:50 4.9 N/A N/A N/A N/A N/A
12-Nov 12:00:55 1.3 10.9 9.5 1.2 3.4 89.1
12-Nov 12:18:01 0.4 3.3 2.6 0.7 0.1 96.7
12-Nov 13:00:59 3.9 9.1 7.9 1.1 2.0 90.9
-------------------------------------------------------------
Foreground Wait Class DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> s - second, ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc
-> %Timeouts: value of 0 indicates value was Captured Time accounts for 162.1% of Total DB time 343,015.70 (s)
-> Total FG Wait Time: 547,088.75 (s) DB CPU time: 8,875.04 (s)
Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
Other 136,738 0 541,966 3964 158.0
DB CPU 8,875 2.6
User I/O 1,859,888 0 2,295 1 0.7
Network 1,000,390 0 1,480 1 0.4
Application 5,131 1 1,145 223 0.3
Commit 11,616 0 126 11 0.0
Concurrency 53,354 96 56 1 0.0
System I/O 1,473,457 0 14 0 0.0
Configuration 183 77 4 24 0.0
Administrative 26 0 0 16 0.0
-------------------------------------------------------------
Foreground Wait Events DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> s - second, ms - millisecond - 1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason)
------------------------------------------------------------------------------
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
RC-Result Cache: Enqueue (Result Cache: Contention)
686,436 686,400 5 4,035 854 211.59
TX-Transaction (row lock contention)
45 45 0 2 160 79,995.00
UL-User-defined
3,393 3,352 41 42 123 2,930.95
WL-Being Written Redo Log
57 28 29 8 9 1,123.75
RO-Multiple Object Reuse (fast object reuse)
18 18 0 2 5 2,305.00
CF-Controlfile Transaction
342,596 342,595 1 78 3 42.56
TX-Transaction (index contention)
1,534 1,534 0 27 1 43.70
KO-Multiple Object Checkpoint (fast object checkpoint)
198 198 0 22 1 49.55
PS-PX Process Reservation
3,690 3,690 0 40 0 .75
HW-Segment High Water Mark
7,628 7,628 0 1 0 20.00
TX-Transaction
97,145 97,145 0 2 0 5.00
TH-Threshold Chain (metric threshold evaluation)
1,306 1,306 0 1 0 10.00
SQ-Sequence Cache
10,320 10,320 0 20 0 .00
-------------------------------------------------------------
Latch Activity DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ASM db client latch 5,077 0.0 0 0 N/A
ASM map operation hash t 8 0.0 0 0 N/A
AWR Alerted Metric Eleme 99,177 0.0 0 0 N/A
Change Notification Hash 2,646 0.0 0 0 N/A
Consistent RBA 71,683 0.0 1.0 0 0 N/A
DML lock allocation 1,632,832,756 0.0 0.8 1 0 N/A
DMON Work Queues Latch 96 0.0 0 0 N/A
Event Group Locks 2,024 0.0 0 0 N/A
FAL request queue 373 0.0 0 0 N/A
FAL subheap alocation 373 0.0 0 0 N/A
FIB s.o chain latch 334,842 0.0 0.0 0 0 N/A
FOB s.o list latch 368,326 0.0 0.3 0 0 N/A
File State Object Pool P 8 0.0 0 0 N/A
IPC stats buffer allocat 8 0.0 0 0 N/A
In memory undo latch 4,276 0.0 0 0 N/A
JOX SGA heap latch 10,787 0.0 0 0 N/A
JS Sh mem access 24 0.0 0 0 N/A
JS broadcast autostart l 572 0.0 0 0 N/A
JS queue access latch 8 0.0 0 0 N/A
JS queue state obj latch 60,816 0.0 0 0 N/A
JS slv state obj latch 503 0.0 0 0 N/A
KFC FX Hash Latch 8 0.0 0 0 N/A
KFC Hash Latch 8 0.0 0 0 N/A
KFCL LE Freelist 8 0.0 0 0 N/A
KFR redo allocation latc 8 0.0 0 0 N/A
KGNFS-NFS:SHM structure 8 0.0 0 0 N/A
KGNFS-NFS:SVR LIST 8 0.0 0 0 N/A
KJC message pool free li 8 0.0 0 0 N/A
KJCT flow control latch 8 0.0 0 0 N/A
KMG MMAN ready and start 28,680 0.0 0 0 N/A
KMG resize request state 32 0.0 0 0 N/A
KTF sga latch 41 0.0 0 2,217 0.0
KWQMN job cache list lat 125 3.2 0.5 0 0 N/A
KWQP Prop Status 6 0.0 0 0 N/A
KWQS pqsubs latch 18 0.0 0 0 N/A
KWQS pqueue ctx latch 30 0.0 0 0 N/A
LGWR NS Write 141,954 0.0 0.4 0 0 N/A
Locator state objects po 8 0.0 0 0 N/A
MQL Tracking Latch 0 N/A 0 144 0.0
Memory Management Latch 26,752 0.0 0 28,680 0.0
Memory Queue 8 0.0 0 0 N/A
Memory Queue Message Sub 8 0.0 0 0 N/A
Memory Queue Message Sub 8 0.0 0 0 N/A
Memory Queue Message Sub 8 0.0 0 0 N/A
Memory Queue Message Sub 8 0.0 0 0 N/A
Memory Queue Subscriber 8 0.0 0 0 N/A
MinActiveScn Latch 941,188 0.0 0.0 0 0 N/A
Mutex 8 0.0 0 0 N/A
Mutex Stats 8 0.0 0 0 N/A
OS process 110,192 0.0 0 0 N/A
OS process allocation 7,424 0.0 0.0 0 0 N/A
OS process: request allo 4,094 0.0 0 0 N/A
PL/SQL warning settings 192,843 0.0 0 0 N/A
QMT 8 0.0 0 0 N/A
Real-time plan statistic 34,211 0.7 0.3 0 0 N/A
Request id generation la 48 0.0 0 0 N/A
Result Cache: Latch 6,045,296 0.2 1.0 5.3E+05 0
Result Cache: SO Latch 2,558 0.0 0 0 N/A
SGA IO buffer pool latch 1,435 0.0 0 2,004 0.0
SGA blob parent 8 0.0 0 0 N/A
Latch Activity DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
SGA bucket locks 8 0.0 0 0 N/A
SGA heap locks 8 0.0 0 0 N/A
SGA pool locks 8 0.0 0 0 N/A
SQL memory manager latch 21,875 4.0 1.0 0 2,391 0.1
SQL memory manager worka 2,866,488 0.0 0.0 0 0 N/A
Shared B-Tree 364 0.0 0 0 N/A
Streams Generic 8 0.0 0 0 N/A
Testing 8 0.0 0 0 N/A
Token Manager 8 0.0 0 0 N/A
Write State Object Pool 8 0.0 0 0 N/A
X$KSFQP 168 0.0 0 0 N/A
XDB NFS Security Latch 8 0.0 0 0 N/A
XDB unused session pool 8 0.0 0 0 N/A
XDB used session pool 8 0.0 0 0 N/A
active checkpoint queue 11,510 0.0 0.0 0 0 N/A
active service list 54,071 0.1 0.0 0 3,942 0.0
alert log latch 6,176 0.0 0 0 N/A
archive control 1,348 0.0 0 0 N/A
archive process latch 3,194 0.1 1.0 0 0 N/A
begin backup scn array 3,675 0.0 0 0 N/A
buffer pool 652 0.0 0 0 N/A
business card 8 0.0 0 0 N/A
cache buffer handles 2,530,118 0.0 0.0 0 0 N/A
cache buffers chains 606,040,029 0.0 0.0 0 14,793,597 0.0
cache buffers lru chain 1,547,224 0.1 0.1 0 12,707,295 0.1
cache table scan latch 57,513 0.0 0.0 0 57,513 0.0
cas latch 8 0.0 0 0 N/A
change notification clie 8 0.0 0 0 N/A
channel handle pool latc 4,801 0.0 0 0 N/A
channel operations paren 57,667 0.0 0.0 0 0 N/A
checkpoint queue latch 1,103,984 0.0 0.1 0 465,121 0.0
client/application info 1,164,166 0.0 0.0 0 0 N/A
commit callback allocati 4,892 0.0 0 0 N/A
compile environment latc 4,760 0.0 0 0 N/A
corrupted undo seg lock 30,980 0.0 0.0 0 0 N/A
cp cmon/server latch 8 0.0 0 0 N/A
cp pool latch 8 0.0 0 0 N/A
cp server hash latch 8 0.0 0 0 N/A
cp sga latch 1,547 0.0 0 0 N/A
cv apply list lock 8 0.0 0 0 N/A
cv free list lock 8 0.0 0 0 N/A
deferred cleanup latch 1,547 0.0 0 0 N/A
dictionary lookup 2 0.0 0 0 N/A
dml lock allocation 1,547 0.0 0 0 N/A
done queue latch 8 0.0 0 0 N/A
dummy allocation 9,584 0.3 0.0 0 0 N/A
enqueue hash chains 3,908,136 0.0 0.1 0 407 0.0
enqueues 3,082,619 0.1 0.0 0 0 N/A
error message lists 1,911 12.9 0.2 0 0 N/A
fifth spare latch 8 0.0 0 0 N/A
file cache latch 5,840 0.0 0 0 N/A
flashback archiver latch 24 0.0 0 0 N/A
flashback copy 8 0.0 0 0 N/A
gc element 8 0.0 0 0 N/A
gcs commit scn state 8 0.0 0 0 N/A
gcs partitioned table ha 8 0.0 0 0 N/A
gcs pcm hashed value buc 8 0.0 0 0 N/A
gcs resource freelist 8 0.0 0 0 N/A
gcs resource hash 8 0.0 0 0 N/A
gcs resource scan list 8 0.0 0 0 N/A
Latch Activity DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
gcs shadows freelist 8 0.0 0 0 N/A
ges domain table 8 0.0 0 0 N/A
ges enqueue table freeli 8 0.0 0 0 N/A
ges group table 8 0.0 0 0 N/A
ges process hash list 8 0.0 0 0 N/A
ges process parent latch 8 0.0 0 0 N/A
ges resource hash list 8 0.0 0 0 N/A
ges resource scan list 8 0.0 0 0 N/A
ges resource table freel 8 0.0 0 0 N/A
ges value block free lis 8 0.0 0 0 N/A
global KZLD latch for me 2,785 0.0 0 0 N/A
global tx hash mapping 4,244 0.0 0 0 N/A
granule operation 8 0.0 0 0 N/A
hash table column usage 1,614 0.0 0 3,551,922 0.0
hash table modification 487 0.0 0 0 N/A
internal temp table obje 77 0.0 0 0 N/A
intra txn parallel recov 8 0.0 0 0 N/A
io pool granule metadata 8 0.0 0 0 N/A
job workq parent latch 8 0.0 0 1,309 0.2
job_queue_processes para 591 0.0 0 0 N/A
k2q lock allocation 8 0.0 0 0 N/A
kdlx hb parent latch 8 0.0 0 0 N/A
kgb parent 8 0.0 0 0 N/A
kks stats 38,560 0.0 0 0 N/A
kokc descriptor allocati 97,640 0.0 0.3 0 0 N/A
krbmrosl 53,549 0.0 0 0 N/A
ksfv messages 8 0.0 0 0 N/A
kss move lock 62 0.0 0 0 N/A
ksuosstats global area 492 0.0 0 0 N/A
ksv allocation latch 1,655 0.0 0 0 N/A
ksv class latch 116 0.0 0 0 N/A
ksv msg queue latch 8 0.0 0 0 N/A
ksz_so allocation latch 4,094 0.1 0.0 0 0 N/A
ktm global data 168 0.0 0 0 N/A
kwqbsn:qsga 299 0.0 0 0 N/A
lgwr LWN SCN 71,741 0.0 0.0 0 0 N/A
library cache load lock 2,702 0.1 0.0 0 8 0.0
list of block allocation 41,711 0.0 0.0 0 0 N/A
loader state object free 427,580 0.0 0.0 0 0 N/A
lob segment dispenser la 8 0.0 0 0 N/A
lob segment hash table l 32 0.0 0 0 N/A
lob segment query latch 8 0.0 0 0 N/A
lock DBA buffer during m 8 0.0 0 0 N/A
logical standby cache 8 0.0 0 0 N/A
logminer context allocat 85 0.0 0 0 N/A
logminer work area 8 0.0 0 0 N/A
longop free list parent 1,220 0.0 0 87 0.0
mapped buffers lru chain 8 0.0 0 0 N/A
message pool operations 1,416 0.0 0 0 N/A
messages 358,366 0.0 0.0 0 0 N/A
mostly latch-free SCN 72,594 0.3 0.0 0 0 N/A
msg queue latch 8 0.0 0 0 N/A
multiblock read objects 249,432 0.0 0.0 0 0 N/A
name-service namespace b 8 0.0 0 0 N/A
ncodef allocation latch 1,547 0.0 0 0 N/A
object queue header heap 109,342 0.0 0.0 0 108,179 0.0
object queue header oper 7,078,993 0.0 0.1 0 108,350 0.2
object stats modificatio 3,832 0.0 0 0 N/A
parallel query alloc buf 10,064 0.5 0.0 0 0 N/A
parallel query stats 1,191 14.0 0.5 0 0 N/A
Latch Activity DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
parameter list 5,827 0.0 0 0 N/A
parameter table manageme 9,896 0.0 0.0 0 0 N/A
peshm 8 0.0 0 0 N/A
pesom_free_list 298,774 0.0 0 0 N/A
pesom_hash_node 299,604 0.0 0 0 N/A
pesom_heap_alloc 1,705 0.0 0 0 N/A
post/wait queue 22,585 0.0 0.0 0 11,709 0.0
process allocation 4,417 0.1 1.0 0 2,016 0.0
process group creation 4,094 0.1 0.0 0 0 N/A
process queue 5,740 4.9 0.2 0 0 N/A
process queue reference 226,129 0.0 0.0 0 20,098 0.5
qmn task queue latch 1,572 2.2 0.2 0 0 N/A
query server freelists 4,003 0.2 0.0 0 0 N/A
query server process 40 0.0 0 50 0.0
queued dump request 24 0.0 0 0 N/A
readredo stats and histo 70,657 0.0 0 0 N/A
recovery domain hash lis 8 0.0 0 0 N/A
redo allocation 236,306 0.1 0.1 0 6,110,775 0.0
redo copy 8 0.0 0 6,111,637 0.0
redo writing 235,335 0.0 0.0 0 0 N/A
resmgr group change latc 366,965 0.0 0 0 N/A
resmgr:active threads 9,584 0.0 0 0 N/A
resmgr:actses change gro 4,539 0.0 0 0 N/A
resmgr:actses change sta 9 0.0 0 0 N/A
resmgr:free threads list 9,576 1.3 0.6 23 0 N/A
resmgr:plan CPU method 8 0.0 0 0 N/A
resmgr:resource group CP 8 0.0 0 0 N/A
resmgr:schema config 244 0.0 0 0 N/A
resmgr:session queuing 8 0.0 0 0 N/A
resmgr:vc list latch 1 0.0 0 0 N/A
rm cas latch 8 0.0 0 0 N/A
row cache objects 24,881,624 0.0 0.1 0 90 0.0
rules engine aggregate s 6 0.0 0 0 N/A
rules engine rule set st 312 0.0 0 0 N/A
second spare latch 8 0.0 0 0 N/A
sequence cache 1,510,566 0.0 0.0 0 0 N/A
session allocation 34,584,281 0.5 0.0 5 0 N/A
session idle bit 2,167,918 0.0 0.0 0 0 N/A
session queue latch 8 0.0 0 0 N/A
session state list latch 17,034 0.3 0.1 0 0 N/A
session switching 2,554 0.0 0 0 N/A
session timer 3,939 0.0 0 0 N/A
shared pool 5,851,141 0.1 0.2 2 0 N/A
shared pool sim alloc 8 0.0 0 0 N/A
shared pool simulator 18,576,870 0.0 0.1 0 0 N/A
sim partition latch 8 0.0 0 0 N/A
simulator hash latch 30,498,932 0.0 0.0 0 0 N/A
simulator lru latch 30,170,595 0.1 0.0 0 163,363 0.0
sort extent pool 139,425 0.0 0.0 0 0 N/A
space background task la 5,636 0.2 0.4 0 4,804 0.0
state object free list 6 0.0 0 0 N/A
statistics aggregation 2,520 0.0 0 0 N/A
tablespace key chain 8 0.0 0 0 N/A
temp lob duration state 2,521 0.0 0 0 N/A
temporary table state ob 1,662 0.0 0 0 N/A
test excl. parent l0 8 0.0 0 0 N/A
test excl. parent2 l0 8 0.0 0 0 N/A
third spare latch 8 0.0 0 0 N/A
threshold alerts latch 2,488 0.0 0 0 N/A
trace latch 57 0.0 0 0 N/A
Latch Activity DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
transaction allocation 398,751,031 0.0 0.9 0 0 N/A
transaction branch alloc 5,753 0.0 0 0 N/A
undo global data 439,645 0.0 0 0 N/A
user lock 12,874 0.2 0.7 0 0 N/A
virtual circuit buffers 8 0.0 0 0 N/A
virtual circuit holder 8 0.0 0 0 N/A
virtual circuit queues 8 0.0 0 0 N/A
-------------------------------------------------------------
Latch Sleep Breakdown DB/Inst: XXXX/XXXX Snaps: 4824-4827
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
session allocation 34,584,281 180,605 6,062 175,033
DML lock allocation 1,632,832,756 170,158 129,597 59,222
cache buffers chains 606,040,029 64,824 207 64,625
simulator lru latch 30,170,595 30,868 1,064 29,852
transaction allocation 398,751,031 20,481 17,958 6,525
Result Cache: Latch 6,045,296 9,900 10,204 7,436
shared pool 5,851,141 6,027 1,471 4,692
row cache objects 24,881,624 3,662 302 3,407
enqueues 3,082,619 2,393 108 2,285
cache buffers lru chain 1,547,224 1,791 179 1,627
enqueue hash chains 3,908,136 1,512 172 1,344
cache buffer handles 2,530,118 915 7 908
SQL memory manager latch 21,875 878 877 61
shared pool simulator 18,576,870 857 88 787
object queue header operat 7,078,993 816 54 763
redo allocation 236,306 342 24 318
simulator hash latch 30,498,932 317 1 316
process queue 5,740 283 64 222
Real-time plan statistics 34,211 247 85 165
error message lists 1,911 246 44 205
mostly latch-free SCN 72,594 246 1 245
parallel query stats 1,191 167 86 92
resmgr:free threads list 9,576 120 69 60
sequence cache 1,510,566 97 1 96
session state list latch 17,034 55 3 52
parallel query alloc buffe 10,064 49 1 48
FOB s.o list latch 368,326 40 11 34
qmn task queue latch 1,572 35 6 29
kokc descriptor allocation 97,640 24 6 18
user lock 12,874 23 16 7
space background task latc 5,636 12 5 7
checkpoint queue latch 1,103,984 11 1 10
LGWR NS Write 141,954 10 4 6
KWQMN job cache list latch 125 4 2 2
process allocation 4,417 4 4 0
archive process latch 3,194 2 2 0
Consistent RBA 71,683 1 1 0
-------------------------------------------------------------
0 responses so far ↓
There are no comments yet...Kick things off by filling out the form below.