Cursor sharing on Win32 - 'the safe bet?'

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 Received on Tue May 01 2007 - 20:09:08 CDT