Re: Re: latch free - library cache

I am having Oracle Applications 11.5.4 without any custom code. So I don't see any reason for CURSOR_SHARING=FORCE as Apps uses bind variables and no literals. This was set in last Sep 2002 when this site was facing serious performance problems (I was not in this company at that time). Now it is going difficult for me to explain this to management.

~Dilip

ORACLE-L_at_fatcity.com wrote:

Cursor sharing set to FARCE forces constants to be converted into bind variables and enables SQL statements to be shared even if the're not identical. It also enables quite a few very interesting ora-0600 and ora-7445 errors that have very good applications in a DSS environment. It will work properly in Oracle 19zR20 and not in oracle 9iR2. Plase, de patient. Your money is very important to us.

On 2003.05.15 08:35 Regis Biassala wrote:
> 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....
>
> Regis
>
> -----Original Message-----
> Sent: Thursday, May 15, 2003 12:07 PM
> To: Multiple recipients of list ORACLE-L
>
>
> >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:
>
> > 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: 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).
>

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
INET: mgogala_at_adelphia.net

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