You could use the force_matching_signature column of v$sqlstats to find possible SQLs:
create table t (
x int
);
select * from t where x = 1;
select * from t where x = 2;
select force_matching_signature, count(*)
from v$sqlstats
where force_matching_signature > 0
and sql_text like 'select * from t%'
group by force_matching_signature
having count(*) = 2
order by 2 desc;
FORCE_MATCHING_SIGNATURE COUNT(*)
17,455,114,290,046,537,663 2
Read more on Connor's blog:
https://connormcdonald.wordpress.com/2016/05/30/sql-statements-using-literals/ Note this isn't guaranteed to find
every statement using literals. Just those that are currently cached. If you're licensed for Diagnostics and Tuning, you could use the AWR data:
select force_matching_signature, count(distinct sql_id)
from dba_hist_sqlstat
where force_matching_signature > 0
group by force_matching_signature
having count(distinct sql_id) > 1
order by 2 desc;
Again, this isn't going to be everything. AWR only captures the "top" consuming statements.