Re: Library Cache

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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.html
Received on Tue Dec 30 2008 - 02:47:35 CST

Original text of this message