之前一直用如下sql来查看非绑定变量的sql，但是不准

selecthash_value,substr(sql_text,1,80)fromv$sqlareawheresubstr(sql_text,1,40)in(selectsubstr(sql_text,1,40)fromv$sqlareahavingcount(*)>1groupbysubstr(sql_text,1,40));SELECTsubstr(sql_text,1,80),count(1)FROMv$sqlGROUPBYsubstr(sql_text,1,80)HAVINGcount(1)>1ORDERBY2;

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@xbtstSQL>select*fromtest;DEPTNODNAMELOC-------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONMOE@xbtstSQL>altersystemflushshared_pool;Systemaltered.MOE@xbtstSQL>select*fromtestwheredeptno=10;DEPTNODNAMELOC-------------------------------------10ACCOUNTINGNEWYORKMOE@xbtstSQL>select*fromtestwheredeptno=20;DEPTNODNAMELOC-------------------------------------20RESEARCHDALLASMOE@xbtstSQL>select*fromtestwheredeptno=30;DEPTNODNAMELOC-------------------------------------30SALESCHICAGOMOE@xbtstSQL>select*fromtestwheredeptno='10';DEPTNODNAMELOC-------------------------------------10ACCOUNTINGNEWYORKMOE@xbtstSQL>select*fromtestwheredeptno='20';DEPTNODNAMELOC-------------------------------------20RESEARCHDALLASMOE@xbtstSQL>select*fromtestwheredeptno='30';DEPTNODNAMELOC-------------------------------------30SALESCHICAGOMOE@xbtstSQL>varv_idnumberMOE@xbtstSQL>exec:v_id:=10PL/SQLproceduresuccessfullycompleted.MOE@xbtstSQL>select*fromtestwheredeptno=:v_id;DEPTNODNAMELOC-------------------------------------10ACCOUNTINGNEWYORKMOE@xbtstSQL>exec:v_id:=20PL/SQLproceduresuccessfullycompleted.MOE@xbtstSQL>select*fromtestwheredeptno=:v_id;DEPTNODNAMELOC-------------------------------------20RESEARCHDALLASMOE@xbtstSQL>exec:v_id:=30PL/SQLproceduresuccessfullycompleted.MOE@xbtstSQL>select*fromtestwheredeptno=:v_id;DEPTNODNAMELOC-------------------------------------30SALESCHICAGOMOE@xbtstSQL>setline123MOE@xbtstSQL>colsql_textformata40MOE@xbtstSQL>setnumwidth30MOE@xbtstSQL>selectsql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATUREFROMV$SQLWHEREsql_textlike'%select*fromtest%';SQL_TEXTFORCE_MATCHING_SIGNATUREEXACT_MATCHING_SIGNATURE----------------------------------------------------------------------------------------------------select*fromtestwheredeptno=2016742236444580572825701787720123824641select*fromtestwheredeptno='20'16742236444580572826624213459289620561select*fromtestwheredeptno='30'167422364445805728215799720645668840753select*fromtestwheredeptno='10'16742236444580572827423854019058606662select*fromtestwheredeptno=3016742236444580572826295409922938069091select*fromtestwheredeptno=1016742236444580572825918141949209886904select*fromtestwheredeptno=:v_id50384954612074902875038495461207490287MOE@xbtstSQL>showparametercursor_sharNAMETYPEVALUE-----------------------------------------------------------------------------cursor_sharingstringEXACT

可以看到以上的sql在没有使用绑定变量的sql中FORCE_MATCHING_SIGNATURE值均是相同的而EXACT_MATCHING_SIGNATURE是不同的那么通过以上的sql我们就可以完善出查找没有使用绑定变量的sql语句：

MOE@xbtstSQL>select*2from(selectsql_text,3row_number()over(partitionbyFORCE_MATCHING_SIGNATUREorderbyFORCE_MATCHING_SIGNATURE)rn4fromv$sql5whereFORCE_MATCHING_SIGNATURE>06andFORCE_MATCHING_SIGNATURE!=EXACT_MATCHING_SIGNATURE)7wherern>1;SQL_TEXTRN----------------------------------------------------------------------select*fromtestwheredeptno='30'2select*fromtestwheredeptno='20'3select*fromtestwheredeptno=104select*fromtestwheredeptno=305select*fromtestwheredeptno=206

参考：关于高效捕获数据库非绑定变量的SQL语句