no cursor_sharing..

From: <oton66_at_my-deja.com>
Date: Sun, 14 Jan 2001 21:34:24 GMT
Message-ID: <93t610$ck$1@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 - 15:34:24 CST