Re: no cursor_sharing..

Along with cursor_sharing=FORCE you
must enable query_rewrite. Have you done so?

Robert

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/ Received on Wed Jan 17 2001 - 17:38:30 CST