Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Latch Contention

RE: Latch Contention

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 1 Mar 2004 15:20:12 -0800
Message-ID: <35CFD500D7BDCE43B9030BBA5979DC181D937A@ussccem13.hds.com> 





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 %        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/>





==




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 Mon Mar 01 2004 - 17:15:07 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US