Re: no cursor_sharing..

From: <oton66_at_my-deja.com>
Date: Tue, 16 Jan 2001 11:45:42 GMT
Message-ID: <941c94$drg$1@nnrp1.deja.com>

"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/ Received on Tue Jan 16 2001 - 05:45:42 CST