Re: explain plan changes if using bind vars

NetComrade wrote:
> On 2 Aug 2005 10:18:30 -0700, "AK" <AK_TIREDOFSPAM_at_hotmail.COM> wrote:
>
> >I used /*+ CURSOR_SHARING_EXACT */ in a similar situation
> >
>
> Great.. didn't know this existed.
> This will work for now, and won't look as ugly as wrapping with two
> alter session statements.
>
> Thanks.
> .......
> We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
> remove NSPAM to email

Am I missing something? Setting CURSOR_SHARING = EXACT restores the default setting:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96536/ch131.htm#REFRN10025

as does the CURSOR_SHARING_EXACT hint:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#6650

As I understand it, if you have set CURSOR_SHARING = something other than the default EXACT, but you want the CBO to treat a query as if you hadn't (that is, you want to prevent it from replacing all your literals with bind variables), then hinting with CURSOR_SHARING_EXACT should do the trick.

You would typically set CURSOR_SHARING = SIMILAR or FORCE if you had code that didn't use bind variables but you wish it did.

The situation described above seems to be the reverse of that, in that bind variables can give the optimizer less information to go on than literals, and in some circumstances that can adversely affect the execution plan. There is no CURSOR_SHARING_SIMILAR or CURSOR_SHARING_FORCE hint AFAIK.

Still if it works for you, then great. Received on Tue Aug 02 2005 - 13:09:23 CDT