Re: Binding Variable Benefit Across Sessions ?

In article <bd9a9a76.0204051417.4f5695cd_at_posting.google.com>, bchorng_at_yahoo.com says...
>
>"R Chin" <rchin_at_panix.com> wrote in message
>news:<a8kua5$52t$1_at_news.panix.com>...
>> Thanks guys !
>> I should have said 'ONE single DYNAMIC SQL'
>>
>> So if SessionA fired 'select X form TABOA where X = :w'
>> and SessionB fired 'select Y form TABOA where X = :z'
>>
>> Now the hash_values are different, no ?
>> is there gonna be a hard parse then ?
>>
>> robert
>>
>
> That will definitely be another hard parse. The hash values are
> different.
>
> I may be wrong on this, but there is another scenario I believe
> that even the hash values ARE the same, there is still a hard parse.
> That is, the objects in the 2 SQLs although having the same names,
> but are actually different objects.
>

you are not wrong. See

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2588723819082

for a more in depth discussion on this.

> If user A issues: select * from emp; and user B issues the same
> later. But each user has its own emp. Then Oracle has to hard parse
> again. The SQL although is shared in a common location, the parse
> trees have to be stored twice in 2 different locations. The latter
> are usually much bigger. In that case, even if you use bind variables
> and the hash value is the same, your are still not sharing your SQLs.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp