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

On May 2, 1:44 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Another DBA attempted to set it to SIMILAR, and *INSTANTLY* started getting 07445 errors.

I wonder if we should open a TAR on this? Received on Wed May 02 2007 - 16:02:16 CDT