Re: no cursor_sharing..

  robertgfreeman_at_my-deja.com wrote:
> Along with cursor_sharing=FORCE you
> must enable query_rewrite. Have you done so?
>
> Robert

No, and I won't know whether it is enabled or not for a couple of days, however - After some testing we found out that 'alter system set cursor_sharing = force;' must be explicitly run after each startup, then it works perfect. I've checked several times with Oracle support and made sure that I didn't err somewhere.

Oracle support (or at least the consultant i spoke with there) has written it of as a bug which 'probably has been fixed' since he couldn't reproduce it on 8.1.6.1 or 8.1.6.2 and therefore asked me to apply latest patch. Guess I'll do so..

-oton

> In article <941c94$drg$1_at_nnrp1.deja.com>,
> oton66_at_my-deja.com wrote:
> >
> >
> > "Niall Litchfield" wrote:
> > > Having reread your first post. have you bounced the database
 instance?
> > > Oracle won't age the statements out of the statement cache untill
 you
> > > do or shared pool is full).
> >
> > Yes, been restarting the database several times. Funny thing though:
> > after I did the 'alter system set cursor_sharing = force' one more
> > bounce did the trick, suddenly I had about 180 statements in
 v$sqlarea
> > (with binds) instead of _thousands_ which never got reused. (And
 keep
> > in mind that cursor_sharing = force was all the time in init.ora
> > +that 'show parameter' gave setting 'force', seems a bit buggy..)
> >
> > "Sybrand Bakker" wrote:
> > > The reuse will only occur if statements are *exactly* identical.
 So,
> > > for Oracle you don't submit similar statements, as their hash
 values
> > > are different.
> > > You really need to try to use bind variables, as in that case the
> > > statement is only parsed once.
> > > This is a very common problem and it is getting worse because many
> > > developers using Java aren't using bind variables at all (while it
 is
> > > possible), and this procedure will easily bring your server
> > > completely on it's knees.
> > > This is only to stress the importance of using bind variables.
> > >
> > > Regards,
> > >
> > > Sybrand Bakker, Oracle DBA
> >
> > I totally agree with you, and this is Java-based. The developers are
> > using time-pressure as an excuse for not using bind-variables. I've
> > given the proper response to that.
> >
> > However after finally getting cursor-sharing to work, a new
 stresstest
> > (with 1600 simulated users at the site multiplexed to 40 db-
 sessions)
> > now showed that the average response-time per user was reduced by 80
 %,
> > So I'm happy - very happy.
> >
> > What puzzles me now is that the parse/execute ratio is still 1/1 on
 the
> > statements - although the CPU-load has dramatically decreased. Don't
> > know if this is expected behaviour so I'll look into it. Any hints
 on
> > that (too) are ofcourse appreciated:
> >
> > A select from v$sqlarea now gives:
> >
> > SQL_TEXT EXECUTIONS PARSE_CALLS
> > ------------------------------ ---------- -----------
> > SELECT KONTOR, ANT, KM FROM BY 28634 28634
> > DEL WHERE FNR=:SYS_B_0
> >
> > thanx,
> >
> > -oton
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 18 2001 - 19:18:31 CST