RE: Latch Contention
- From: "Potluri, Venu (CT Appl Suppt)" <venu_potluri@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- Date: Tue, 2 Mar 2004 13:59:42 -0500
John,
That query's output gives sessions that are waiting on a latch, correct? I am
looking to find the session holding the latch. Also when I ran the following
sql, it comes back some 90 rows selected.
select * from v$sql_shared_cursor where KGLHDPAR = '9194BF00';
We do have lots of sessions executing the sql below:
select to_char(CODE_COMBINATION_ID), nvl(ENABLED_FLAG, 'Y'),
nvl(SUMMARY_FLAG, 'N'), to_char(START_DATE_ACTIVE, 'YYYY/MM/DD HH24:MI:SS'),
to_char(END_DATE_ACTIVE, 'YYYY/MM/DD HH24:MI:SS'), SEGMENT1, SEGMENT2,
SEGMENT3, SEGMENT4, SEGMENT5, SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9,
DETAIL_BUDGETING_ALLOWED_FLAG, DETAIL_POSTING_ALLOWED_FLAG, ACCOUNT_TYPE,
JGZZ_RECON_FLAG from GL_CODE_COMBINATIONS where CHART_OF_ACCOUNTS_ID =
:FND_BIND1 and (SEGMENT1 = :FND_BIND2 ) and (SEGMENT2 = :FND_BIND3 ) and
(SEGMENT3 = :FND_BIND4 ) and (SEGMENT4 = :FND_BIND5 ) and (SEGMENT5 =
:FND_BIND6 ) and (SEGMENT6 = :FND_BIND7 ) and (SEGMENT7 = :FND_BIND8 ) and
(SEGMENT8 = :FND_BIND9 ) and (SEGMENT9 = :FND_BIND10 )
I think there is a problem with hard parsing as well. Oracle says this could be
a bug which requires installing a one-off patch. I obviously can't bring the
database down during the day, in a close week, to install a patch.
Still searching for the root cause of the problem...
Thanks,
Venu
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [SMTP:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of John Kanagaraj
> Sent: Tuesday, March 02, 2004 12:51 PM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: 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!
>
> ** 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
> ----------------------------------------------------------------->
--------------------------------------------------------
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:
