[20120919]利用v$sql的FORCE_MATCHING_SIGNATURE简单确定没有绑定的sql语句.txt


接链接:

http://space.itpub.net/267265/viewspace-743928


按照上面的理解:


利用v$sql的FORCE_MATCHING_SIGNATURE可以简单确定没有绑定的sql语句.


当然会遗漏一些像这种情况的绑定语句.(绑定与常量混用的情况):


select rownum r,x.* from (

select sql_id,plan_hash_value,sql_text,exact_matching_signature,force_matching_signature

from v$sql where lower(sql_text) like '%/*%findme%' and sql_text not like '%v$sql%' order by exact_matching_signature) x;


  R SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

--- ------------- --------------- ------------------------------------------------------------ ------------------------ ------------------------

  1 b3x2pcgkxaxft      1183254286 select /*+ Findme */ name from t where id1= :v_id1 and id2=2      7008393373276421720      7008393373276421720

  2 ckr7rh1zfrwcv      1183254286 select /*+ Findme */ name from t where id1= :v_id1 and id2=1     14799038700516685754     14799038700516685754


select force_matching_signature, count (*) from gv$sql group by force_matching_signature

having force_matching_signature <> 0 order by 2 desc;

ITPub博客

[20120919]利用v$sql的FORCE_MATCHING_SIGNATURE简单确定没有绑定的sql语句.txt