Re: explain plan changes if using bind vars

On 2 Aug 2005 11:09:23 -0700, "William Robertson" <william.robertson_at_bigfoot.com> wrote:

>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.
>>

>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.

You're right.. and I am an idiot.. Of course it 'works', but I still didn't get the effects of cusor_sharing=force. And I believe you're also right about such hints non-existing. I've rushed to come to a conclusion that it worked, w/o really verifying via trace file.

Not only that.. but I've found out that for some other IDs passed, query performs poorly as well, even w/o using bind vars

.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes remove NSPAM to email Received on Tue Aug 02 2005 - 13:54:45 CDT