Re: CURSOR_SHARING=SIMILAR

On 3 Aug 2006 13:05:34 -0700, "Sethu" <sethu77_at_hotmail.com> wrote:

>Hi,
>
>I am using Oracle 9i (9.2.0.6) on HPUX. I have the CURSOR_SHARING
>parameter set to SIMILAR and on testing I believe I may have some
>issues.
>
>SQL> alter session set cursor_sharing = 'EXACT';
>
>Session altered.
>
>SQL> select 'AAA' from dual;
>
>'AA
>---
>AAA
>
>SQL> alter session set cursor_sharing = 'SIMILAR';
>
>Session altered.
>
>SQL> select 'AAA' from dual; --- <== found from cache
>
>'AA
>---
>AAA
>
>SQL> select 'BBB' from dual; -- <== brand new one, notice the
>size of the returned value
>
>'BBB'
>--------------------------------
>BBB
>
>I read an article in asktom site about this which explains the reason
>as Oracle binds the literal string to a variable length constant. My
>question is can it create a problem, if my application looking for Y/N
>values coming back and the sql returned 'Y ' ?

I don't think Oracle implicitly trims hardcoded literals. So 'Y' and 'Y ' would result in multiple children for this statement and potentially different execution plans.

--
Sybrand Bakker, Senior Oracle DBA