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

On May 1, 9:09 pm, 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

Be careful with setting CURSOR_SHARING to FORCE on Oracle 10.2.0.2. I had run Oracle 8.1.7.3 for years with CURSOR_SHARING set to FORCE without issue, and made it several months on 10.2.0.2 with the parameter set the same way. Then, I applied the October 2006 patchset, and only made it through one day of production use before sessions started dropping with ORA-07445 errors - this is on Windows 2003 x64, but others on Metalink have indicated similar problems on Linux, Unix, and other platforms as well.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed May 02 2007 - 03:44:59 CDT