RE: latch free - library cache

CURSOR_SHARING=FORCE is used to FORCE binding variables...
Your application is not using bind variables where it should do.
Now depends if you application is an OLTP then bind variables are highly
recommended.
If you application is DSS then you don't need to use bind variables as it
won't HELP...and for this, those Oracle guys need to explain why....

>From the init.ora found out that CURSOR_SHARING=FORCE was done by oracle
people who had visited site for performance problems in Sep 2002.

Now I have to catch them (if that is possible !!!) and ask the reason behind
keeping it FORCE.

~Dilip

ORACLE-L_at_fatcity.com wrote:

WHOA!!!
Why do you have CURSOR_SHARING set to FORCE (default = EXACT)? You ain't
supposed to do that with Oracle 11.5.x! Certainly not with 817x. Check out
MetaLink doc #216205.1...

Better yet, search MetaLink on the keyword CURSOR_SHARING and be sure to
click on "Advanced" and "Bug Database"; good way to lose any enthusiasm for
that particular parameter...

on 5/14/03 3:36 AM, Dilip at dilip7772002_at_indiatimes.com wrote:

&gt; Hi List,&gt; &gt; DB 8.1.7.4.0 on Sun sparc solaris 2.6&gt; This is oracle applications 11.5.4&gt; &gt; One of the program was running for very long time. So checked out&gt; v$session_wait for that perticular session. It was waiting on 'latch
free' and
&gt; from P2 column and v$latch, found out that it is waiting for library
cache
&gt; latch.&gt; Now docs says this is related to shared pool fragmentation. So checked&gt; v$sgastat where it showed 38M space free in the shared pool (Total
shared pool
&gt; is 800 MB). But it didnt throw ORA-4031 and the process was running for
last
&gt; 17 hours. I looked at v$librarycache and v$latchholder but didn't get
any
&gt; clue.&gt; At last, I flushed the shared pool but still that session was waiting
on same
&gt; wait event. Finally I had to kill the session. I am facing this
situation once
&gt; or twice in production and everytime I can't kill the session.&gt; &gt; Can somebody tell me how should I debug furthur and go to the root
cause of
&gt; the problem. Does this mean I need to increse the shared pool ?CURSOR_SHARING
&gt; is kept to FORCE.&gt; &gt; Thanks,&gt; ~Dilip&gt; &gt; &gt; &gt; &gt; &gt; &gt; &gt; Get Your Private, Free E-mail from Indiatimes athttp://email.indiatimes.com&gt; &gt; Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com&gt; &gt; Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to&gt; http://airsahara.indiatimes.com and Bid Now !

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
INET: tim_at_sagelogix.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).
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.
This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Regis Biassala
INET: Regis.Biassala_at_datalex.ie
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).