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

On May 4, 4:11 pm, bdbafh <bdb..._at_gmail.com> wrote:
> On May 2, 7:13 pm, BD <robert.d..._at_gmail.com> wrote:
>
>
>
>
>
> > On May 2, 4:08 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > > On May 2, 5:02 pm, BD <robert.d..._at_gmail.com> wrote:
>
> > > > On May 2, 1:44 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > > > 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.
>
> > > > Another DBA attempted to set it to SIMILAR, and *INSTANTLY* started
> > > > getting 07445 errors.
>
> > > > I wonder if we should open a TAR on this?
>
> > > I did not get anywhere with a TAR on this problem last November. I
> > > have been following Metalink bug logs since that time - it appears
> > > that a fix will be released in a month or two for both 10.2.0.2 and
> > > 10.2.0.3 - I do not have the Metalink bug number at hand where this
> > > was mentioned.
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > > - Show quoted text -
>
> > That's okay. I've seen some notes on Metalink, and on other forums.
>
> > Thanks again.
>
> You did not mention the version, other than the overly broad "10g".
> Please include the patched version on bug issues.
> For 10.2.0.3 on win32, cursor_sharing !='exact' != SAFE.
> This is as of 10.2.0.3 patch 4 that was released as part of
> CPUApr2007.
>
> -bdbafh- Hide quoted text -
>
> - Show quoted text -

CURSOR_SHARING=similar works fine in 10R1 (worked for more than a year now in a 10.1.0.4 production system) and according to the tests I made at the time provided two advantages:
1. rewrites litteral queries using bind vars which added an average of 50% on the CPU usage (compared to the queries already using bind vars), but much lower than having litterals - sorry, but I cannot remember the exact percentage.
2. does not add overhead in terms of CPU usage for queries already using bind vars - I think this is the most important.

Unfortunately, I no longer have the stats for CURSOR_SHARING=FORCE, but the SIMILAR worked better for me. Anyway you should do your own tests in order to decide which way to go - they may be different due to the platform, application, etc.

Good luck! Received on Fri May 04 2007 - 15:24:10 CDT