之前一直用如下sql来查看非绑定变量的sql,但是不准
select hash_value, substr(sql_text, 1, 80) from v$sqlarea where substr(sql_text, 1, 40) in (select substr(sql_text, 1, 40) from v$sqlarea having count(*) > 1 group by substr(sql_text, 1, 40)); SELECT substr(sql_text, 1, 80), count(1) FROM v$sql GROUP BY substr(sql_text, 1, 80) HAVING count(1) > 1 ORDER BY 2;
10g之后,oracle对v$sql视图进行了变更,添加了一个新的字段FORCE_MATCHING_SIGNATURE该字段oracle对于其解释为The signature used when the CURSOR_SHARING parameter is set to FORCE
初步的理解应该是假定数据库的cursor_sharing为force时计算得到的值,
而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.
个人的理解为当sql语句进入数据库中时对于一些可以潜在可以共享或者因为绑定变量问题造成游标没有共享的sql他的FORCE_MATCHING_SIGNATURE值完全相同,而EXACT_MATCHING_SIGNATURE值是不同的
下面在11gr2中做个测试:
MOE@xbtst SQL>select * from test; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON MOE@xbtst SQL>alter system flush shared_pool; System altered. MOE@xbtst SQL>select * from test where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK MOE@xbtst SQL>select * from test where deptno=20; DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS MOE@xbtst SQL>select * from test where deptno=30; DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO MOE@xbtst SQL>select * from test where deptno='10'; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK MOE@xbtst SQL>select * from test where deptno='20'; DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS MOE@xbtst SQL>select * from test where deptno='30'; DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO MOE@xbtst SQL>var v_id number MOE@xbtst SQL>exec :v_id := 10 PL/SQL procedure successfully completed. MOE@xbtst SQL>select * from test where deptno=:v_id; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK MOE@xbtst SQL>exec :v_id := 20 PL/SQL procedure successfully completed. MOE@xbtst SQL>select * from test where deptno=:v_id; DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS MOE@xbtst SQL>exec :v_id := 30 PL/SQL procedure successfully completed. MOE@xbtst SQL>select * from test where deptno=:v_id; DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO MOE@xbtst SQL>set line 123 MOE@xbtst SQL>col sql_text format a40 MOE@xbtst SQL>set numwidth 30 MOE@xbtst SQL>select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE sql_text like '%select * from test%'; SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ---------------------------------------- ------------------------------ ------------------------------ select * from test where deptno=20 1674223644458057282 5701787720123824641 select * from test where deptno='20' 1674223644458057282 6624213459289620561 select * from test where deptno='30' 1674223644458057282 15799720645668840753 select * from test where deptno='10' 1674223644458057282 7423854019058606662 select * from test where deptno=30 1674223644458057282 6295409922938069091 select * from test where deptno=10 1674223644458057282 5918141949209886904 select * from test where deptno=:v_id 5038495461207490287 5038495461207490287 MOE@xbtst SQL>show parameter cursor_shar NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACT
可以看到以上的sql在没有使用绑定变量的sql中FORCE_MATCHING_SIGNATURE值均是相同的而EXACT_MATCHING_SIGNATURE是不同的那么通过以上的sql我们就可以完善出查找没有使用绑定变量的sql语句:
MOE@xbtst SQL>select * 2 from (select sql_text, 3 row_number() over(partition by FORCE_MATCHING_SIGNATURE order by FORCE_MATCHING_SIGNATURE) rn 4 from v$sql 5 where FORCE_MATCHING_SIGNATURE > 0 6 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE) 7 where rn > 1; SQL_TEXT RN ---------------------------------------- ------------------------------ select * from test where deptno='30' 2 select * from test where deptno='20' 3 select * from test where deptno=10 4 select * from test where deptno=30 5 select * from test where deptno=20 6
参考:关于高效捕获数据库非绑定变量的SQL语句
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。