Re: Cursor sharing on Win32 - 'the safe bet?'

On May 2, 5:09 am, BD <robert.d..._at_gmail.com> wrote:
> I'm just winding up a migration of a database from 8i on AIX, to 10G
> on Win32. OLTP, roughly 300 concurrent users.
>
> Don't ask. ;) It's a Microsoft shop. Erg.
>
> The application in this system does not use bind variables.
>
> One specific tuning parameter has come up in discussion - cursor
> sharing.
>
> Cursor sharing had been set to FORCE in the previous incarnation.
>
> Our application is running, but the parse-to-execute ratio is higher
> than it had been in the previous system.
>
> We are restricted by Win32's memory model, and although the buffer
> cache has been moved into upper memory, the free memory in my Shared
> Pool is still quite low.
>
> We have discussed the adjustment of the cursor_sharing paramter, as a
> means of lowering the parse-to-execute ratio.
>
> But, it seems to me, the SQL that is transformed via the changed
> cursor sharing model is gonna have to live somewhere for other
> sessions to get at it - and it will go right into the shared pool.
>
> I believe that when moving to a Win32-based platform, EXACT is a safer
> bet, at least until you can be absolutely sure about your memory
> consumption.
>
> I am concerned that modifying this parameter, while it will take some
> load off the CPU, is going to put us well into the danger zone
> regarding physical memory.
>
> Seems to me that changing my cursor_sharing from EXACT is just begging
> for more memory headaches. Under the Win32 memory model, I'd prefer to
> let the CPUs burn a little hot than trip ORA-04031s every 10 seconds.
>
> Anyone agree/disagree?
>
> Thanks,
>
> BD

It's not about Windows memory model or Unix memory model - it's about Oracle memory architecture. The shared pool is the shared pool, regardless the platform and platform-specific memory allocation mechanisms. All parsed SQL goes to the shared pool, that's what it's for, primarily. More specifically, it goes to the library cache, which is part of the shared pool. It doesn't matter if particular statement uses bind variables or not, or if it's forced/rewritten to use them via cursor_sharing - it will end up in the shared pool anyway even if it won't actually ever be shared. The use of bind variables allows other sessions to "reuse" previously executed SQL, which both lowers CPU consumption AND library cache usage. So by enforcing bind variables, you actually lower your chances for hitting ORA-4031 even in most memory-constrained configurations.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed May 02 2007 - 00:08:17 CDT