Taking just a few minutes to look at the output from a CPU monitoring tool like 'sar', 'Task Manager' and the parsing statistics will convince you of the worth of bind variables. But once you accept the evidence of the enormous benefits of bind variables (as we all will do), what can we do besides trawling through v$sqlarea statement-by-statement looking for literals?
The following process may assist (you can see Tom Kyte's post here)
The logic is quite simple - look for anything in quotes (since these will be "implicit" if bind variables are in use), and any plain numerics as indicators of literals. Then by populating a column with the "de-literalised" version of the SQL, we can assess which statements should be tackled first.
create table t1 as select * 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 -- or any other limit you would like to choose (the lower the better) order by 2 /