| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Latch Contention
How do I find the latch holder? I have the same latch free wait problem
today. Looked in v$latchholder but there was nothing in there. So the
question is when I see major latch contention (library cache, cache
buffer chains and shared pool) how to find sessions holding the latches.
What do I do to attack this problem? Thanks.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Kanagaraj
Sent: Monday, March 01, 2004 6:20 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Latch Contention
Venu,
Were you able to determine which and how many Concurrent requests were
running at that time, and what changes if any were done for these
programs?
(the changes may be have been in the past, but manifest themseleves now
as
some programs run only during the monthend). I had a very interesting
and
similar situation where a scheduled cron job came in occassionally to
generate 'GRANT SELECT' on all objects in all schemas to a read-only
account
and then run the resulting SQLs several times a day. This generates
thousands of tiny (in comparison) SQLs, each of which had to be parsed,
and
modify objects and thus invalidating lib cache/dd cache and cluttered up
shared pool. Shared pool and Lib cache latches were through the roof. I
used
V$SESSION_WAIT to determine which sessions were waiting and thus
determined
what was happening... The fix was to stop running this during peak hours
and
change it to GRANT SELECT on only new objects...
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Potluri, Venu (CT Appl Suppt)
Sent: Monday, March 01, 2004 2:55 PM
To: oracle-l_at_freelists.org
Subject: Latch Contention
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 % IGETSIMISSES
------------------------- ------------ ------- ------- ------------ --------- intra txn parallel recove 0 .00 .00 00
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 00
X$KSFQP 39 .00 .00 0 0 archive process latch 167 .00 .00 0 0 file number translation t 285 .00 .00 00
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 % IGETSIMISSES
------------------------- ------------ ------- ------- ------------ --------- 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 00
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 % IGETSIMISSES
------------------------- ------------ ------- ------- ------------ --------- event group latch 40,868 .00 .00 0 0 transaction branch alloca 53,467 .00 .00 00
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 00
redo writing 1,598,340 .08 99.10 0 0 global transaction 2,039,041 .00 .00 0 0 NAME GETS Miss % Spin % IGETSIMISSES
------------------------- ------------ ------- ------- ------------ --------- 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
spin sl01 sl02 sl03 sl04
sl05
NAME GETS Miss % sl06 sl07 sl08 sl09 sl10
sl11
------------------ ------------ ------ ------ ----- ----- ----- -----
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
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
0.0 0.0 0.0 0.0 0.0
0.0
0.0 0.0 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
------------------ ------------ ------ ------ ----- ----- ----- -----
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
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
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
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
0.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/>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -------------------------------------------------------- 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/> ============================================================================== ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 02 2004 - 07:56:31 CST
 |
 |