两个步骤,一个步骤一个脚本
一、查询出未只用绑定变量的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;
一、查询出未只用绑定变量的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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30135314/viewspace-1839612/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30135314/viewspace-1839612/