tag:blogger.com,1999:blog-4096575.post2054073627634482086..comments2024-03-18T02:14:37.994-04:00Comments on The Arup Nanda Blog: Diagnosing Library Cache Latch Contention: A Real Case StudyArup Nandahttp://www.blogger.com/profile/03392706779349258765noreply@blogger.comBlogger27125tag:blogger.com,1999:blog-4096575.post-76128180746365606992013-05-16T06:53:56.089-04:002013-05-16T06:53:56.089-04:00It will be very usefull for me. Have application t...It will be very usefull for me. Have application team done any code change?<br /><a href="http://www.frankssheds.com/sheds.html" rel="nofollow">Sheds Fort Lauderdale</a>Aryan Smithhttps://www.blogger.com/profile/02605085609460438976noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-47175199473578014892012-06-06T01:19:54.713-04:002012-06-06T01:19:54.713-04:00This is a really good read for me, Must admit that...This is a really good read for me, Must admit that you are one of the best bloggers I ever saw.Thanks for posting this informative article.Keira Pagehttp://www.diskdoctors.com/data-recovery-san-diego.aspnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-42130405736698649082011-09-01T06:34:46.260-04:002011-09-01T06:34:46.260-04:00Useful info...
TajUseful info...<br /><br /><br />TajMohammed Tajhttps://www.blogger.com/profile/18022044698097362461noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-9977769997473684372010-10-27T11:18:43.908-04:002010-10-27T11:18:43.908-04:00Hi Arup,
I have this situation, with db hang. Any ...Hi Arup,<br />I have this situation, with db hang. Any idea?<br /><br />Found 11 objects waiting for <br /> <4947/951/0x6e845ff8/13204/No Wait><br />Found 11 objects waiting for <br /> <41/2570/0x6ebc6a58/12454/latch free><br />Found 10 objects waiting for <br /> <3336/1628/0x6eaf2648/679/latch free><br />Found 20 objects waiting for <br /> <5589/800/0x6e898b38/1259/latch free><br />Found 16 objects waiting for <br /> <2192/2293/0x6e82cdf8/27111/latch free><br />Found 18 objects waiting for <br /> <4475/1274/0x6e97c228/4024/latch free><br />Found 20 objects waiting for <br /> <3533/1281/0x6eb01928/1238/latch free><br />Found 32 objects waiting for <br /> <908/8663/0x6e8122d8/25902/latch free><br />Found 16 objects waiting for <br /> <5333/808/0x6e82bd38/8862/latch free><br />Found 13 objects waiting for <br /> <4450/1413/0x6ebdacc8/15475/latch free><br />Found 14 objects waiting for <br /> <5784/927/0x6ea836c8/5289/latch free>Emilianohttps://www.blogger.com/profile/10888726547238500209noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-84905018059533653792010-08-12T22:58:02.061-04:002010-08-12T22:58:02.061-04:00Hi Arup
i understand now. Thanks for your reply!Hi Arup<br /><br />i understand now. Thanks for your reply!Unknownhttps://www.blogger.com/profile/16698870318686471232noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-48629290236415040702010-07-30T00:13:45.139-04:002010-07-30T00:13:45.139-04:00@andes
I could reproduce the behavior in 10.2.0.3...@andes<br /><br />I could reproduce the behavior in 10.2.0.3.<br /><br />$ sqlplus -prelim<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 30 03:46:05 2010<br /><br />Copyright (c) 1982, 2006, Oracle. All Rights Reserved.<br /><br />Enter user-name: / as sysdba<br />03:46:09 SQL> exit<br />Disconnected from ORACLE<br /><br />$ sqlplus -prelim<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 30 03:46:16 2010<br /><br />Copyright (c) 1982, 2006, Oracle. All Rights Reserved.<br /><br />Enter user-name: sys<br />Enter password: <br />03:46:40 SQL> <br /><br />As you can see both "/ as sysdba" and "sys/password as sysdba" worked.<br /><br />You are not entering " as sysdba". You are entering just "". Remember, sys password must be given with sysdba clause.<br /><br />You may also enter it as:<br /><br />$ sqlplus -prelim<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 30 04:12:40 2010<br /><br />Copyright (c) 1982, 2006, Oracle. All Rights Reserved.<br /><br />Enter user-name: sys as sysdba<br />Enter password: <br />04:12:46 SQL>Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-27340249967500830402010-07-29T23:16:03.989-04:002010-07-29T23:16:03.989-04:00i can't use sqlplus -prelim, my database versi...i can't use sqlplus -prelim, my database version is 10.2.0.3, please see below :<br /><br />[oracle@deployment2 ~]$ sqlplus -prelim<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 30 09:15:18 2010<br /><br />Copyright (c) 1982, 2006, Oracle. All Rights Reserved.<br /><br />Enter user-name: sys<br />Enter password:<br />ERROR:<br />ORA-24300: bad value for mode<br /><br /><br />Enter user-name:<br /><br />any comments ??<br /><br />ThanksUnknownhttps://www.blogger.com/profile/16698870318686471232noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-68369483069350064162009-11-21T03:39:38.845-05:002009-11-21T03:39:38.845-05:00This comment has been removed by a blog administrator.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-87028831200351220582009-10-09T11:20:40.705-04:002009-10-09T11:20:40.705-04:00Excellent analysis, Arup.Excellent analysis, Arup.Willhttps://www.blogger.com/profile/14287968262115465561noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-23238791068953415122009-08-20T10:57:51.938-04:002009-08-20T10:57:51.938-04:00Arup ... I really enjoyed reading this. Great stu...Arup ... I really enjoyed reading this. Great stuff.<br /><br />Thanks.k roynoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-64516857704056874532009-08-13T11:03:03.687-04:002009-08-13T11:03:03.687-04:00Hi Arup:
Excellent article...thanksHi Arup:<br /><br />Excellent article...thanksAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-51547369716004560682009-08-10T23:05:18.673-04:002009-08-10T23:05:18.673-04:00Hi Arup:
1. Great Article. What I like about your...Hi Arup:<br /><br />1. Great Article. What I like about your articles is the simplicity with which you explain. It is like peeling a banana and giving it to us for eating (if you know what I mean)<br /><br />2. Few clarifications <br />You mention 'First it shows that the SID 146 Serial# 17 is waiting for library cache latch'<br />Should it not be <br />First it shows that the SID 146 Serial# 1 is waiting for library cache latch<br /><br />3. You mention ' So, we had to track 8; not just one session. We trudged on. All the sessions dumped their information. So, I searched the directory for some other files with the same issues'<br /><br />How did you track the remaining sessions? Was tha available in oradebug? Did you do a ls -ltr and get the latest files and checked the last 10 or 15 files?<br /><br /><br />4. You mentiond ' I could have dumped the library cache latches to get that information; but the application owner volunteered to terminate the sessions'<br /><br />But this situation can happen again when the developer runs his program again. The remedy is probably not issue all those updates in parallel or pospone this activity to a later time when there may be less contention. What would be the long term solution?<br /><br /><br />Thank you again for such an excellent article.Kumar Maddurihttps://www.blogger.com/profile/15189324674840369808noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-44234483041935397992009-02-17T17:11:00.000-05:002009-02-17T17:11:00.000-05:00Arup, Nice article. I am also facing same type...Arup, <BR/> Nice article. I am also facing same type of problem in my production environment. How you have solved this problem? It will be very usefull for me. Have application team done any code change?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-11110518627767982882009-01-30T15:57:00.000-05:002009-01-30T15:57:00.000-05:00Excellent!!!Did help a lot with a similar problem....Excellent!!!<BR/>Did help a lot with a similar problem.<BR/><BR/>Great work!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-10968803671224143352009-01-20T06:30:00.000-05:002009-01-20T06:30:00.000-05:00Hi Arup,If this issue occurs on Oracle 9i version,...Hi Arup,<BR/><BR/>If this issue occurs on Oracle 9i version, how can we login to sga with out connecting to the database.<BR/><BR/>As sqlplus -prelim option is present only in Oracle 10g version.<BR/><BR/>Regards,<BR/>Yasser.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-89498013571534226802009-01-20T06:01:00.000-05:002009-01-20T06:01:00.000-05:00Excellent article...Always i use to search eagerly...Excellent article...Always i use to search eagerly this kind of real-time problem.<BR/><BR/>Please keep posting this kind of real-time problems you have resolved.<BR/><BR/>Awaiting for your new real-time issues walkthrough...<BR/><BR/>Please keep posting this kind of posts !!!!!<BR/><BR/>Regards,<BR/>Yasser.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-29220683416989391182009-01-08T04:57:00.000-05:002009-01-08T04:57:00.000-05:00Hi Arup,An interesting posting. However, several t...Hi Arup,<BR/><BR/>An interesting posting. However, several things spring to mind:-<BR/><BR/>1. The developer is using hard coded literals, evident from the fact that binds are being prefixed by :SYS_B and not :B1, :B2 etc. Presumably cursor_sharing is set to similar. <BR/><BR/>2. There are technical notes on Metalink alluding to high version counts when cursor_sharing is set to similar; 296377.1 and 377847.1. It would have been interesting to have had the ability to see the version counts on these UPDATEs and also wait events on the library cache pin mutex.<BR/><BR/>3. Did someone suggest to the developer that if he is carrying out updates in a tight loop, bulk binding via PL/SQL is really the way to go. I think there are some circumstances in 10g where the database engine will do this under the covers without it having to be explicitly coded for. <BR/><BR/>A very good walk through all in all.<BR/><BR/>ChrisChris Adkinhttps://www.blogger.com/profile/14506026605015116618noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-62880893128341184382008-11-10T17:11:00.000-05:002008-11-10T17:11:00.000-05:00Well said.Well said.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-39905538070347957352008-09-11T19:33:00.000-04:002008-09-11T19:33:00.000-04:00This post was very very useful. Thanks for sharing...This post was very very useful. Thanks for sharing your experience with us.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-27675708776618066902008-09-10T19:33:00.000-04:002008-09-10T19:33:00.000-04:00The -prelim option is definitely available on 10g ...The -prelim option is definitely available on 10g Release 2. I tried it on 10g R1 and it worked; but I can't confirm its validity. None of our databases are R1. It's definitely not available in 9i and prior.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-13176493043931805402008-09-10T06:41:00.000-04:002008-09-10T06:41:00.000-04:00sqlplus -prelim on which database verion does it w...sqlplus -prelim on which database verion does it worksparaghttps://www.blogger.com/profile/06970569866758977505noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-73163398452962302282008-09-10T06:34:00.000-04:002008-09-10T06:34:00.000-04:00sqlplus -prelim on which database version does it ...sqlplus -prelim on which database version does it worksAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-81221350436245792282008-08-25T12:34:00.000-04:002008-08-25T12:34:00.000-04:00Arup,thank you for this excelent example. For furt...Arup,<BR/><BR/>thank you for this excelent example. For further reading I suggest Metalink Note: 215858.1 - "Interpreting HANGANALYZE trace files to diagnose hanging and performance problems".<BR/><BR/>Adrian HollayAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-48115024936884728592008-08-25T11:39:00.000-04:002008-08-25T11:39:00.000-04:00Thanks, Nuno. It was a pleasure to cross paths wit...Thanks, Nuno. It was a pleasure to cross paths with you again. I haven't visited Oracle-L for quite a while, where I assume you are still active.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-45483883703820603322008-08-25T05:26:00.000-04:002008-08-25T05:26:00.000-04:00Damn useful stuff, Arup.Bookmarked.Damn useful stuff, Arup.<BR/>Bookmarked.Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.com