Re: no cursor_sharing..

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/
Received on Sun Jan 14 2001 - 17:09:24 CST