Re: EXECUTE IMMEDIATE and library cache latch contention

In article <9rc7nu$c3p$1_at_babylon.agtel.net>, "Vladimir says...
>
>Dear DBAs,
>
>am I correct when I think that excessive use of EXECUTE IMMEDIATEs
>can lead to library cache latch contention? Oracle's documentation on EI
>states that it re-prepares the statement every time no matter if it uses
>bind variables or not - does this mean that each time this statement
>is re-inserted into library cache (and thus, the latch is acquired) even it
>has no literals?
>
>I am trying to figure out the source of library cache latch contention
>on one of our production databases running 8.1.6.2. Application
>extensively uses PL/SQL to generate HTML (actually, it's a web application)
>and we have a lot of PL/SQL functions with EIs. Does it make sense to
>replace direct EI with its procedural counterpart which will use DBMS_SQL?
>Or I am wrong at this and direct EI of the statement with only bind
>vars inside will enter the library cache only once?
>

yes it is true that execute immediate will do a soft parse at least (hard parse the first time the server is started and the query is new)....

So, a construct like:

   for i in 1 .. 1000
   loop

      execute immediate 'insert into t values ( :x )' using i;    end loop;

would be better with dbms_sql (lots less latching)...

Now, in your web app you must ask yourself

• do I execute this query more then once or only once during the session.

If the answer is "i only execute it once during the session", then execute immediate in this case is harmless.

If the answer is "i execute it a TON of times", dbms_sql would be better (STATIC sql better still!)

You can get some payback from setting session_cached_cursors as well. You'll see the parse counts still go up -- but the latching will go down as this is a softer soft parse if we find the cursor in the sessions cursor cache instead of having to goto the shared pool.

>thanks in advance,
> Bob.
>
>--
>Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
>Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
>applications.
>All opinions are mine and do not necessarily go in line with those of my
>employer.
>
>
>

--
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