Latch Contention
- From: "Potluri, Venu (CT Appl Suppt)" <venu_potluri@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 1 Mar 2004 17:55:03 -0500
Today we had problems with latch contention in our production Oracle Apps
database. I saw lots of latch free waits, of the cache buffer chains and
library cache variety. I looked for the most resource intensive sessions. But
nothing stood out. Finally I flushed the shared pool (Cary Millsap is going to
cringe at this time........). That cleared all the latch free waits and users
reported better performance. Didn't want to flush the shared pool but had to do
it due to month end close processing that was getting severly bogged down. I
still haven't found the root cause. My question is where to look for the root
cause next time.
Here are some stats I gathered while the problem occurred.
NAME GETS Miss % Spin % IGETS IMISSES
------------------------- ------------ ------- ------- ------------ ---------
intra txn parallel recove 0 .00 .00 0 0
ry
parallel txn reco latch 0 .00 .00 0 0
address list 1 .00 .00 0 0
mostly latch-free SCN 2 .00 .00 0 0
NLS data objects 2 .00 .00 0 0
Direct I/O Adaptor 2 .00 .00 0 0
message pool operations p 3 .00 .00 0 0
arent latch
begin backup scn array 37 .00 .00 0 0
X$KSFQP 39 .00 .00 0 0
archive process latch 167 .00 .00 0 0
file number translation t 285 .00 .00 0 0
able
ktm global data 300 .00 .00 0 0
archive control 927 .00 .00 0 0
longop free list 1,160 .00 .00 0 0
NAME GETS Miss % Spin % IGETS IMISSES
------------------------- ------------ ------- ------- ------------ ---------
device information 1,710 .00 .00 0 0
kwqit: protect wakeup tim 2,811 .00 .00 0 0
dictionary lookup 3,250 .00 .00 0 0
global tx free list 3,763 .00 .00 0 0
sort extent pool 4,086 .00 .00 0 0
loader state object freel 6,103 .00 .00 0 0
ist
ncodef allocation latch 9,642 .00 .00 0 0
job_queue_processes param 9,642 .00 .00 0 0
eter latch
hash table modification l 0 .00 .00 22,991 0
atch
session switching 24,272 .00 .00 0 0
i/o slave adaptor 0 .00 .00 36,912 0
vecio buf des 0 .00 .00 36,912 0
library cache load lock 38,888 .04 100.00 0 0
NAME GETS Miss % Spin % IGETS IMISSES
------------------------- ------------ ------- ------- ------------ ---------
event group latch 40,868 .00 .00 0 0
transaction branch alloca 53,467 .00 .00 0 0
tion
process group creation 80,972 .00 .00 0 0
channel handle pool latch 80,981 .00 .00 0 0
process allocation 40,868 .04 .00 40,863 .01223601
channel operations parent 121,890 .00 .00 0 0
latch
global tx hash mapping 158,890 .00 .00 0 0
user lock 180,484 .01 68.42 0 0
Token Manager 181,487 .00 100.00 36,912 0
sequence cache 341,190 .01 89.66 0 0
active checkpoint queue l 695,343 .00 .00 0 0
atch
list of block allocation 1,130,452 .00 100.00 0 0
dml lock allocation 1,322,672 .00 100.00 0 0
redo writing 1,598,340 .08 99.10 0 0
global transaction 2,039,041 .00 .00 0 0
NAME GETS Miss % Spin % IGETS IMISSES
------------------------- ------------ ------- ------- ------------ ---------
transaction allocation 2,532,521 .00 100.00 0 0
undo global data 2,744,812 .00 100.00 0 0
enqueue hash chains 3,303,961 .00 94.44 0 0
enqueues 3,814,510 .01 99.50 0 0
session allocation 4,803,687 .04 92.49 0 0
messages 5,014,273 .06 99.27 0 0
multiblock read objects 10,086,892 .02 99.42 2 0
session idle bit 13,586,255 .00 100.00 0 0
row cache objects 16,937,164 .08 99.75 89 1.1235955
checkpoint queue latch 23,051,771 .00 67.45 0 0
redo copy 13,680 .00 .00 36,266,260 .00756626
redo allocation 36,522,674 .01 99.87 0 0
cache buffer handles 82,954,716 .32 99.93 0 0
cache buffers lru chain 36,022,870 .14 96.82 79,027,986 .16287268
shared pool 379,947,867 .16 98.51 0 0
latch wait list 317,618,315 5.94 99.76 297,982,053 5.0251922
library cache ############ 293.06 94.87 819,140 3.7733477
cache buffers chains ############ .16 99.78 107,818,804 .02991315
spin sl01 sl02 sl03 sl04 sl05
NAME GETS Miss % sl06 sl07 sl08 sl09 sl10 sl11
------------------ ------------ ------ ------ ----- ----- ----- ----- -----
cache buffers chai ############ 0.2 99.8 0.2 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
library cache ############ ##### 94.9 4.0 0.7 0.4 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
shared pool 379,948,308 0.2 98.5 0.3 0.7 0.4 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
latch wait list 317,630,086 5.9 99.8 0.2 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
cache buffer handl 82,954,716 0.3 99.9 0.1 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
redo allocation 36,522,674 0.0 99.9 0.1 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
cache buffers lru 36,022,877 0.1 96.8 3.2 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
checkpoint queue l 23,051,900 0.0 67.4 32.6 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
row cache objects 16,937,179 0.1 99.8 0.2 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
session idle bit 13,586,307 0.0 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
spin sl01 sl02 sl03 sl04 sl05
NAME GETS Miss % sl06 sl07 sl08 sl09 sl10 sl11
------------------ ------------ ------ ------ ----- ----- ----- ----- -----
multiblock read ob 10,086,892 0.0 99.4 0.6 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
messages 5,014,290 0.1 99.3 0.7 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
session allocation 4,803,691 0.0 92.5 7.3 0.1 0.1 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
enqueues 3,814,532 0.0 99.5 0.5 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
enqueue hash chain 3,303,974 0.0 94.4 5.6 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
undo global data 2,744,812 0.0 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
transaction alloca 2,532,523 0.0 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
redo writing 1,598,345 0.1 99.1 0.9 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
dml lock allocatio 1,322,672 0.0 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
list of block allo 1,130,452 0.0 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
spin sl01 sl02 sl03 sl04 sl05
NAME GETS Miss % sl06 sl07 sl08 sl09 sl10 sl11
------------------ ------------ ------ ------ ----- ----- ----- ----- -----
sequence cache 341,190 0.0 89.7 10.3 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
Token Manager 181,487 0.0 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
user lock 180,484 0.0 68.4 26.3 5.3 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
process allocation 40,868 0.0 0.0 93.3 6.7 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
library cache load 38,888 0.0 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
CHILD# ROUND(SLEEPS/GETS*100,2) RATIO
--------- ------------------------ ---------
12 0 100
11 0 100
10 0 100
9 0 100
8 0 100
7 0 100
6 0 100
5 0 100
4 0 100
3 0 100
2 0 100
1 0 100
--------------------------------------------------------
If you are not an intended recipient of this e-mail, please notify the sender,
delete it and do not read, act upon, print, disclose, copy, retain or
redistribute it. Click here for important additional terms relating to this
e-mail. http://www.ml.com/email_terms/
--------------------------------------------------------
==============================================================================
If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.
Click here for important additional terms relating to this e-mail.
<http://www.ml.com/email_terms/>
==============================================================================
Other related posts:
