Re: Library Cache
Date: Tue, 30 Dec 2008 08:47:35 -0000
> 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;
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
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