16 years ago, someone “Ask-ed Tom” how to find those SQL statements that were not using bind variables. You can see the question here (because we don’t delete stuff ever 
) but I’ll paraphrase the answer below:
Tom took the following approach
- take a copy of SQL statements in the library cache
- create a routine that would hunt for constants in the SQL text (that is, numbers and anything within quotes) and replace them with place holders
- then count the resultant SQL’s for duplicates
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
/
It’s a utility I used many times over the years. But times change and it’s always good to reflect on the tools and techniques you use, and whether anything has come along in later versions to assist you. Consider the second bullet point above:
create a routine that would hunt for constants and replace them with place holders
That sounds a lot like the process that must be followed when “cursor_sharing” is enabled (and set to “force”), the only difference being the place holders would be come bind variables. With that in mind, even if we are not using cursor_sharing, then the information that got added to the library cache when cursor_sharing was introduced all those years ago can now be used to assist us.
In V$SQLSTATS, there is a column FORCE_MATCHING_SIGNATURE which is a “signature” (or hash value) representing a SQL statement that has been converted to allow for cursor_sharing = force. So that becomes the perfect grouping mechanism to identify repeated statements using literals. For example:
SQL> select force_matching_signature, count(*)
2 from v$sqlstats
3 where force_matching_signature > 0
4 group by force_matching_signature
5 having count(*) > 10
6 order by 2 desc;
FORCE_MATCHING_SIGNATURE COUNT(*)
------------------------- ----------
7756258419218828704 73
15993438058742417605 16
15893216616221909352 15
14052954841849993177 12
10493170372233636846 11
5 rows selected.
SQL> select sql_text from v$sqlstats
2 where FORCE_MATCHING_SIGNATURE = 7756258419218828704;
SQL_TEXT
----------------------------------------------------------------
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 148525
and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8448
and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 6309
and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 148524
and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8442
and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8594
and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 148693
and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8602
and bitand(FLAGS, 128)=0
...
Identification of literal SQL has become trivial. Fixing it ? Well…that’s another story
Learning is not a spectator sport 
Got some thoughts? Leave a comment