| 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 free - library cache
--=_MAILER_ATTACH_BOUNDARY1_200351541516161801979802
Content-Type: text/plain; charset=us-ascii
I checked this view. Surely this will give me most executed/loaded objects. But how to relate my perticular session with the object it is waiting for ??
Anyway I have taken the last sql statement from v$sql. So that sql statement might be the object in question. Now how to resolve this situation ??
Thankx,
~Dilip
ORACLE-L_at_fatcity.com wrote:
Dilip,
I would have a look at V$DB_OBJECT_CACHE to check what are the most requested - and reloaded - objects. It could give a clue about what it is trying to get.
HTH SF
>----- ------- Original Message ------- -----
>From: "Dilip"
>To: Multiple recipients of list ORACLE-L
>
>Sent: Wed, 14 May 2003 02:36:44
>
>Hi List,
>
>DB 8.1.7.4.0 on Sun sparc solaris 2.6
>This is oracle applications 11.5.4
>
>One of the program was running for very long time.
>So checked out v$session_wait for that perticular
>session. It was waiting on 'latch free' and from P2
>column and v$latch, found out that it is waiting
>for library cache latch.
>Now docs says this is related to shared pool
>fragmentation. So checked v$sgastat where it showed
>38M space free in the shared pool (Total shared
>pool is 800 MB). But it didnt throw ORA-4031 and
>the process was running for last 17 hours. I looked
>at v$librarycache and v$latchholder but didn't get
>any clue.
>At last, I flushed the shared pool but still that
>session was waiting on same wait event. Finally I
>had to kill the session. I am facing this situation
>once or twice in production and everytime I can't
>kill the session.
>
>Can somebody tell me how should I debug furthur and
>go to the root cause of the problem. Does this mean
>I need to increse the shared pool ? CURSOR_SHARING
>is kept to FORCE.
>
>Thanks,
>~Dilip
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: sfaroult_at_oriolecorp.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now ! Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now ! --=_MAILER_ATTACH_BOUNDARY1_200351541516161801979802 Content-Type: text/html; charset=us-ascii <BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px"> <P>I checked this view. Surely this will give me most executed/loaded objects. But how to relate my perticular session with the object it is waiting for ?? </P> <P>Anyway I have taken the last sql statement from v$sql. So that sql statement might be the object in question. Now how to resolve this situation ??</P> <P>Thankx,</P> <P>~Dilip<BR><BR><B><I>ORACLE-L_at_fatcity.com</B></I> wrote:<BR><BR></P> <BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px"><BR>Dilip,<BR><BR>I would have a look at V$DB_OBJECT_CACHE to check what are the most requested - and reloaded - objects. It could give a clue about what it is trying to get.<BR><BR>HTH<BR><BR>SF<BR><BR>>----- ------- Original Message ------- -----<BR>>From: "Dilip" <DILIP7772002_at_INDIATIMES.COM><BR>>To: Multiple recipients of list ORACLE-L<BR>><ORACLE-L_at_FATCITY.COM><BR>>Sent: Wed, 14 May 2003 02:36:44<BR>><BR>>Hi List,<BR>><BR>>DB 8.1.7.4.0 on Sun sparc solaris 2.6<BR>>This is oracle applications 11.5.4<BR>><BR>>One of the program was running for very long time.<BR>>So checked out v$session_wait for that perticular<BR>>session. It was waiting on 'latch free' and from P2<BR>>column and v$latch, found out that it is waiting<BR>>for library cache latch.<BR>>Now docs says this is related to shared pool<BR>>fragmentation. So checked v$sgast! at! ! where it showed<BR>>38M space free in the shared pool (Total shared<BR>>pool is 800 MB). But it didnt throw ORA-4031 and<BR>>the process was running for last 17 hours. I looked<BR>>at v$librarycache and v$latchholder but didn't get<BR>>any clue.<BR>>At last, I flushed the shared pool but still that<BR>>session was waiting on same wait event. Finally I<BR>>had to kill the session. I am facing this situation<BR>>once or twice in production and everytime I can't<BR>>kill the session.<BR>><BR>>Can somebody tell me how should I debug furthur and<BR>>go to the root cause of the problem. Does this mean<BR>>I need to increse the shared pool ? CURSOR_SHARING<BR>>is kept to FORCE.<BR>><BR>>Thanks,<BR>>~Dilip<BR>><BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.net<BR>-- <BR>Author: Stephane Faroult<BR>INET: sfaroult@oriolecorp.com<BR><BR>Fat City Network Services -- 858-538-5051 http://www.fatcity.com<BR>San D! ie! ! go, California -- Mailing list and web hosting services<BR>---------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).<BR></BLOCKQUOTE><BR> <HR> <FONT face=Arial size=2><B>Get Your Private, Free E-mail from Indiatimes at </FONT>http://email.indiatimes.com</B><BR>Buy The Best In <B>BOOKS</B> at http://www.bestsellers.indiatimes.com<BR>Bid for <B>Air Tickets @ Re.1</B> on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now !</FONT> </BLOCKQUOTE> <hr><font face="Arial" size="2"><b>Get Your Private, Free E-mail from Indiatimes at </font>http://email.indiatimes.com</b><br>Buy The Best In <b>BOOKS</b> at http://www.bestsellers.indiatimes.com<br>Bid for <b>Air Tickets @ Re.1</b> on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now !</font> --=_MAILER_ATTACH_BOUNDARY1_200351541516161801979802-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dilip INET: dilip7772002_at_indiatimes.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu May 15 2003 - 05:56:49 CDT
 |
 |