Re: Binding Variable Benefit Across Sessions ?

"R Chin" <rchin_at_panix.com> wrote in message news:<a8kenf$nm6$1_at_news.panix.com>...
> I understand that BV is important and all....
>
> My question is :
> If Session A runs my PL/SQL program that issues ONE single
> SQL that I took the "pain" to use BV.
>
> Then Session B logs in (shortly) and exec the SAME
> program, Is this session going to benefit
> from User A's session by virtue of the BV
> used in that session ?
> i.e. Is session B able to avoid the hard parse ? how about soft p. possible
> ?
>
> Are the benefits ONLY realized in the current session then "wiped clean" ?
>
> Thanks
>
> Robert

  Bind variable benefits all sessions. The biggest difference is you   have one copy of SQL instead of many. It saves you shared pool space   and avoid shared pool latch contention.

  When session B ( if you mean user B ) executes a SQL with bind variable   first loaded by session A ( user A ). It does not require a hard parse,   as it does not need to load that SQL again. Loading SQL is very expensive,   because oracle has to obtain shared pool latch when scanning the free   list and there is only one in the entire database. If your shared pool   is fragmented, the scanning can take very long. This is especially true   pre-8.1.6. During this time, the entire database would freeze.   

  However, session B still needs to perform a soft parse because Oracle   still needs to verify this users' privilege on the objects. But this   is much cheaper than a hard parse. While soft parsing, Oracle only   needs library cache latch. There are many of them and the duration   is very short.

  Note that even the same user rerunning the same SQL in a different   session, Oracle would still need to perform a soft parse, because   privilege could be changed, and objects could be gone since the SQL   was first loaded.

  So for SQLs that are highly repeated, you should use bind variables   as much as possible. Received on Fri Apr 05 2002 - 13:38:21 CST