从网上Macleande 网站收录了一个查看非绑定变量的sql的脚本如下:
select FORCE_MATCHING_SIGNATURE, count(1)
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2;
红色字体的条件不知道为何要加进去。看文档里面说EXACT_MATCHING_SIGNATURE
Signature calculated on the normalized SQL text. The normalization
includes the removal of white space and the uppercasing of all non-literal
strings
测试如下语句:
select * from scott.emp;
SELECT * FROM SCOTT.EMP;
select sql_text,sql_id,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sqlarea s where sql_id in ('9x1bq3zas4y72','9nk5rk9chd248')
1 SELECT * FROM SCOTT.EMP 9x1bq3zas4y72 3.70118869611514E18 3.70118869611514E18
2 select * from scott.emp 9nk5rk9chd248 3.70118869611514E18 3.70118869611514E18
以上能看出如果加上ORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE这个条件,就会把上面这种可以做共享的sql漏掉