Re: latch free - library cache

I checked v$latch_holder view many times but there was nobody holding library cache latch.

When I was looking at v$session_wait, three columns state,seconds_in_wait and wait_time are having value WAITED SHORT TIME,-1 and 00. How seconds_in_wait can have minus value ?

CURSOR_SHARING=FORCE was done as per oracle's recommandation.

~Dilip

ORACLE-L_at_fatcity.com wrote:

Dilip,

Have you checked the other sessions holding the 'library cache latch'? Since you have set CURSOR_SHARING = FORCE on an Apps 11i database, I assume that you have some Custom code that uses literals rather than bind vars? The best starting point to troubleshooting this problem is to start looking at all sessions that are holding this latch and the SQLs that they have either executed or are executing - there are SQLs out there that can show you the currently executing SQLs as well as look at V$OPEN_CURSOR for those sessions - and look for clues...

All the best,
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end of your journey in this earth?

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Dilip [mailto:dilip7772002_at_indiatimes.com]
> Sent: Wednesday, May 14, 2003 3:37 AM
> To: Multiple recipients of list ORACLE-L
> Subject: latch free - library cache
>
>
> 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
>
>
>
>
>
>
>
> 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 for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now !

-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
INET: john.kanagaraj_at_hds.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 for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now !

-- 
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).