Re: no cursor_sharing..

Sybrand,

It sounds like you might not know about 8i's cursor sharing functionality. This reduces (note I don't say eliminates) the need for identical SQL. Instead, it assignes a system generated bind variable to the predicate values in the WHERE clause. This can help increase SQL reusability a great deal. cursor_sharing=FORCE and query_rewrite must be enabled.

Robert

Robert

In article <t672kjjjo8hc53_at_beta-news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> 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
>
> <oton66_at_my-deja.com> wrote in message news:93t610
 $ck$1_at_nnrp1.deja.com...
> > We have an app that has 50-70.000 querys pr.hour,
> > and there's only about 30-40 different queries. After some
> > tracing/tkprof-fiddling the conclusion is that 70% of
> > CPU-time is spent parsing these statements over and over.
> > (parse/execute ratio is about 1/1).
> >
> > As I have no way to change the app (i know, i know)
> > I thought that cursor_sharing must be the thing here. So
> > I set CURSOR_SHARING=TRUE in init.ora, but no luck:
> >
> > select sql_text from v$sqlarea where sql_text like '%BYDEL%';
> >
> > SELECT KONTOR, ANT, KM FROM BYDEL WHERE FNR='91912972689'
> > SELECT KONTOR, ANT, KM FROM BYDEL WHERE FNR='91912975217'
> > SELECT KONTOR, ANT, KM FROM BYDEL WHERE FNR='91912975728'
> > SELECT KONTOR, ANT, KM FROM BYDEL WHERE FNR='91912977224'
> > SELECT KONTOR, ANT, KM FROM BYDEL WHERE FNR='91912942958'
> > SELECT KONTOR, ANT, KM FROM BYDEL WHERE FNR='91217429497'
> > etc..
> >
> > And the same goes for the other statements. I was under the
> > impression that this crowd of similar statements should be
> > gone now, so at the risk of missing the far too obvious,
> > why is that not so ?? Any hints would be greatly appreciated!
> >
> >
> >
> >
> > Oracle version:
> >
> > Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit
> > Production
> > With the Partitioning option
> > JServer Release 8.1.6.0.0 - Production
> >
> > os/hw:
> > SunOS 5.7 Generic_106541-12 sun4u sparc SUNW,UltraSPARC-IIi-cEngine
> > (running on a Netra t1)
> >
> >
> > 'show parameter cursor_sharing' gives:
> >
> > NAME TYPE VALUE
> > ------------------------------------ ------- -------
> > cursor_sharing string FORCE
> >
> > TIA
> >
> > -oton
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 17 2001 - 17:41:00 CST