| 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
Venu,
You can use v$session_wait itself
Select sid, p1, p2, p3,
>From v$session_wait
Where event = 'latch free'
P1 is the address of the latch, p2 is the latch type and p3 is the number of tries. You might even get some info off the SECONDS_IN_WAIT column (not included above).
Once you get the sid, look at _all_ of them, since one of them may actually be causing the problem. A 10046 trace for those sids would be appropraite at this time.
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!
>-----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: Tuesday, March 02, 2004 5:59 AM
>To: oracle-l_at_freelists.org
>Subject: 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
>
>** The opinions and facts contained in this message are entirely mine
>and do
>not reflect those of my employer or customers **
>
>-----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
>-----------------------------------------------------------------
>--------------------------------------------------------
>
>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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 11:45:54 CST
 |
 |