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
ORDER BY 2;

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
as
    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;
begin
    for i in 1 .. length( p_query )
    loop
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            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);
end;
/
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 bindless.py 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:

• using sqlplus, run query 'select sql_text from v$sql'
• put contents of spool file into 'hash'; it will remove duplicates coming from v$sql (v$sqlarea is doing 'group by' for the same reason)
• with 'regular expressions' a bit more accurately and sure faster that in Tom's case, do replace numeric and text constants with symbols '@' and '#' accordingly.
• build up a new 'hash' where 'key' is a "post-replace" sql statement and the 'value' is number of occurencies.
• sort new 'hash' by 'value' and print out SQL statements which occurs in library more than hundred times (or whatever number we specify at the script startup)
• All set! You can start to pull out owners of "bad" sql statements and encourage them to use bind variables in their SQL.

 

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