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

On 1 May 2007 18:09:08 -0700, BD <robert.drea_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

1. changing cursor_sharing to exact will *increase* the CPU load. There are two things you can be sure about
2. every statement not using bind variables will have to be parsed
3. every statement not using bind variables is going to use memory
4. A non-scalable O/S like Windows is just begging to set cursor_sharing to FORCE. If you set it to EXACT, at least you can be absolutely sure Oracle is going to starve *both* memory *and* CPU.

You may wish to reconsider, as right now you are on the Road to Hell (tm)

-- 

Sybrand Bakker
Senior Oracle DBA