Oracle非绑定变量SQL语句查询

两个步骤，一个步骤一个脚本
一、查询出未只用绑定变量的SQL语句的哈希值，以及变体个数
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE,count(1) "COUNTS"
from v$sql
where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 30
order by 2 desc
二、根据第一步查询出的哈希值，进行具体SQL语句的查询
with nobind_sql as (select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE,count(1) "COUNTS"
from v$sql
where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 5
order by 2 desc)
select sql_text,ns.counts 
from v$sql vs,nobind_sql ns 
where vs.FORCE_MATCHING_SIGNATURE=&num and  vs.FORCE_MATCHING_SIGNATURE=ns.FORCE_MATCHING_SIGNATURE and rownum=1;