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

[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;