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