Re: Library Cache
Date: Tue, 30 Dec 2008 08:47:35 -0000
Message-ID: <ssadndcIe_2_fcTUnZ2dnUVZ8vOdnZ2d@bt.com>
>
> Sorry, I wasn't very clear. here is a simple example of what I mean:
> v_sqlstring:='select lastname from emp where empid=:bv_empid';
> execute immediate v_sqlstring using 1234;
> the1234 will be the variable for the bind variable :bv_empid. (or
> execute immediate v_sqlstring using v_passedin_empid;
>
> Jim
>
Comparing the fixed code version with the execute immediate version, the difference in performance impact has been changing over the last few releases.
Critically, the fixed code version would immediately end up in the pl/sql cursor cache, which would then allow repeated calls to the SQL with minimum latch activity (effectively it became a held cursor).
Initially, the execute immediate could not get into the pl/sql cursor
cache,
and I think that in earlier versions it wouldn't even take advantage of the
session cursor cache (session_cached_cursors parameter) so if you
executed it multiple times, each call would have to hunt through the
library cache to find and authenticate the statement against the first
call. This has changed in recent releases, and the mechanism can now
take advantage of the cache - which again makes it behave like a held
cursor. I think there was still a little difference in latch activity in
10.2.0.3
though - but I can't remember why at the moment.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Tue Dec 30 2008 - 02:47:35 CST