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! ** 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@xxxxxxxxxxxxx >[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Potluri, >Venu (CT Appl Suppt) >Sent: Tuesday, March 02, 2004 5:59 AM >To: oracle-l@xxxxxxxxxxxxx >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@xxxxxxxxxxxxx >[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of John Kanagaraj >Sent: Monday, March 01, 2004 6:20 PM >To: 'oracle-l@xxxxxxxxxxxxx' >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@xxxxxxxxxxxxx >[mailto:oracle-l-bounce@xxxxxxxxxxxxx] >On Behalf Of Potluri, Venu (CT Appl Suppt) >Sent: Monday, March 01, 2004 2:55 PM >To: oracle-l@xxxxxxxxxxxxx >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@xxxxxxxxxxxxx >put 'unsubscribe' in the subject line. >-- >Archives are at //www.freelists.org/archives/oracle-l/ >FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------