Execute immediate fills up the library cache

Question

I have a question regarding how queries executed through 'execute immediate' is treated in the library cache (We use Oracle 11).

Let's say I have a function like this:

FUNCTION get_meta_map_value (  
  getfield            IN VARCHAR2,  
  searchfield         IN VARCHAR2,  
  searchvalue         IN VARCHAR2  
) RETURN VARCHAR2 IS  
  v_outvalue          VARCHAR2(32767);  
  sql_stmt            VARCHAR2(2000) := 'SELECT '||getfield||' FROM field_mapping, metadata '||  
        'WHERE field_mapping.metadataid = metadata.metadataid AND rownum = 1 AND '||searchfield||' = :1';  
BEGIN  
  EXECUTE IMMEDIATE sql_stmt INTO v_outvalue USING searchvalue;  
...

The getfield and searchfield are in one installation always the same (but has other values in another installation, so that is why we use dynamic sql) So this leaves us with an sql that only differs in the searchvalue (which is a parameter). This function is called in a loop that executes x times, from inside another stored procedure. The stored procedure is executed y times during the connection life time, through ODBC connection. And there are z connections, but each of them uses the same database login.

Now let us also assume that the searchvalue changes b times during one loop.

Question 1: When calculating how many copies of the sql will be kept in the library cache, can we disregard the different values the searchvalue can have (b), as the value is sent as a parameter to execute immediate?

Question 2: Will the loop cause a hard parse of the query x times (query will be created in library cache x times), or can Oracle reuse the query? (We assume that the searchvalue is the same for all calls in this question here, for simplicity)

Question 3: Does the y (number of times the stored procedure is called from odbc during the lifetime of one connection) also multiply the amount of copies of the query that are kept in library cache?

Question 4: Does the z (number of simultaneous connections with same db login) multiply the amount of copies of the query that are kept in library cache?

Main question: What behaviour should I expect here? Is the behaviour configurable? The cause for this question is that we have had this code is production for 4 years, and now one of our customer gets back to us and says "This query fills our whole SGA, and Oracle says it's your fault".

score 0 · Answer 1 · answered Dec 05 '15 at 18:24

The number of different combinations of getfield and searchfield should determine how many "copies" there will be. I use teh word "copies" cautiously because Oracke will treat each variation as distinct. Since you are using a bind variable for searchvalue so however many values you have for this will not add to the query count.

In short, it looks like your code is OK.

Number of connections should not increase the hard parses.

Ask for a AWR report to see exactly how many of these queries are in the SGA, and how many hard parses are being triggered.

score 0 · Answer 2 · answered Jul 02 '19 at 14:34

I will disagree that the number of connections will not increase the hard parse count for the posted code because the last I knew dynamic SQL cannot be shared between sessions. Since the generated SQL uses a bind variable it should generate a reusable statement by the session, but it will not be sharable between user sessions. As a general rule dynamic SQL should be used only for infrequently executed statements. You may want to refer to the following: - - Designing applications for performance and scalability An Oracle White Paper July 2005 https://www.oracle.com/technetwork/database/performance/designing-applications-for-performa-131870.pdf - -

enter code here

Execute immediate fills up the library cache

2 Answers2