RE: latch free - library cache

For various reasons, we've been using CS=F on 8.1.7 for awhile now. We did experience problems on 8.1.7.2 with incorrect results returned on a query and a monthly visit from our friend (ORA-7445), but not with 8.1.7.4. We're on HP/UX 11.0 and not running Oracle Apps, so this probably doesn't mean squat to the original poster.

Also, for killing sessions on Solaris, you may want to try to kill the process from the OS instead of killing the session from within Oracle. Try "kill" or "kill -15" (should be the same) before you "kill -9" the process. That "-9" can be icky in some cases, at least with HP/UX. And when you kill it from the OS, do NOT kill the session from within Oracle. No need to confuse PMON.

My $.02,
Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Mladen Gogala [mailto:mgogala_at_adelphia.net]
> Sent: Thursday, May 15, 2003 8:42 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: latch free - library cache
>
>
> 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).