Identifying SQL which are not using bind variables

The scripits are offered by TOM KYTE at


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

The output of that last query will show you statements that are identical in the shared
pool after all numbers and character string constants have been removed.  These
statements — and more importantly their counts — are the potential bottlenecks.  In
addition to causing the contention, they will be HUGE cpu consumers.

If you discover your applications do not use bind variables — you must have this
corrected.  You’ll never have a good hit ratio if everyone submits “unique” sql.  Your
shared pool will never be used right and you’ll be using excessive CPU (90% of the time
it takes to process “insert into t values ( 1 )” is parsing.  If you use “insert into t
values ( 😡 )”, and bind the value of 1 — then the next person that runs that insert
will benefit from your work and run that much faster.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: