Bindless Statements or How to Pinpoint the Reason of ORA-04031


SQL statements not using bind variables are just messing up your library cache and easily can cause ORA-04031 (unable to allocate %s bytes of shared memory) for other users. All attempts to increase shared pool or issue 'alter system flush shared_pool' do not eliminate the nature of the problem and can help only for a short while.

To bust the problem with ORA-04031 it is necessary to find the actual reason. Very often it happens to be SQL statements that do not use bind variables.

The "Note:146599.1" (Diagnosing and Resolving Error ORA-04031) gives an example of SQL query which is able to show some parts of those SQL statements:

SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 80

But we do not consider this query as an appropriate solution, and that is why:

After tracing the query and parsing trace file with OraSRP, you can see the following picture:

Response Time Components Summary

Response Time Component Duration # Calls - Duration per Call -
Avg Min Max
direct path write 6.15s 41.1% 963 0.006386s 0.00s 0.17s
CPU service 5.74s 38.3% 7 0.820000s 0.00s 5.72s
direct path read 4.15s 27.7% 1,007 0.004121s 0.00s 0.32s
latch free 0.06s 0.4% 73 0.000822s 0.00s 0.01s
SQL*Net message from client 0.02s 0.1% 4 0.005000s 0.00s 0.01s
file open 0.00s 0.0% 1 0.000000s 0.00s 0.00s
SQL*Net message to client 0.00s 0.0% 4 0.000000s 0.00s 0.00s
unaccounted-for -1.15s -7.7%        
Total 14.97s 100.0% 2059 0.007271s 0.00s 5.72s

Look carefully at 'direct path read/write' waits. Two thirds of our time we are just sorting. Why ? Because v$sqlarea is built up on x$kglcursor with grouping by several columns and that gives us these 'direct path read/write' waits.

Anyway, the best that query can show is only some shreds of real statements.


Another way (which you can try in real life) was proposed by Tom Kyte back in 2000. Answering question about "Library cache latch contention", Tom proposed the following PL/SQL code which detects statements with no bind variables:

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function 
remove_constants( p_query in varchar2 ) return varchar2
    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;
    for i in 1 .. length( p_query )
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;
        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;
    end loop;
    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
    for i in 0 .. 8 loop
        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    end loop;
    return upper(l_query);
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2

Quite rough solution, but anyway, it can be accepted as a working one. It's funny to observe how this algorithm is cutting out numeric and text constants. But it is a bit pity to see the same 'v$sqlarea' being used in here, impacting performance of Oracle Server. However, if we are limited to use only SQL and PL/SQL it is probably the best way.

At this point there could come up an idea to not restrict ourselves with SQL. And finally, we created simple script which gives almost the same as Tom Kyte's does, but puts less impact on Oracle Server and is reasonable faster.

In fact, this script could be written in Perl and even in ksh probably, but we have chosen Python, because it is just more fun for us.

Here what is happening in the script:


Should you have any questions or notes regarding script, you can write them to me at any time.