查看需绑定变量的sql

利用 FORCE_MATCHING_SIGNATURE，捕获可以绑定变量减少硬解析的sql以及执行次数： （相似sql的 FORCE_MATCHING_SIGNATURE相同） 以下sql查找的是执行超过20次的未绑定变量的sql SET pages 10000 SET linesize 250 column FORCE_MATCHING_SIGNATURE format 99999999999999999999999 WITH c AS (SELECT FORCE_MATCHING_SIGNATURE,COUNT(*) cnt FROM v$sqlarea WHERE FORCE_MATCHING_SIGNATURE!=0 GROUP BY FORCE_MATCHING_SIGNATURE HAVING COUNT(*) > 20 ) , sq AS (SELECT sql_text ,FORCE_MATCHING_SIGNATURE, row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p FROM v$sqlarea s WHERE FORCE_MATCHING_SIGNATURE IN (SELECT FORCE_MATCHING_SIGNATURE FROM c ) ) SELECT sq.sql_text , sq.FORCE_MATCHING_SIGNATURE,c.cnt "unshared count" FROM c,sq WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE AND sq.p =1 ORDER BY c.cnt DESC;

测试：

alter system flush shared_pool;

create table test as select * from dba_objects;

构造相似的sql并执行：

select * from test where object_id=201;

select * from test where object_id=202;

select * from test where object_id=203;

select * from test where object_id=204;

select * from test where object_id=205;

select * from test where object_id=206;

select * from test where object_id=207;

select * from test where object_id=208;

select * from test where object_id=209;

select * from test where object_id=210;

select * from test where object_id=211;

select * from test where object_id=212;

select * from test where object_id=213;

select * from test where object_id=214;

select * from test where object_id=215;

select * from test where object_id=216;

select * from test where object_id=217;

select * from test where object_id=218;

select * from test where object_id=219;

select * from test where object_id=220;

select * from test where object_id=221;

select * from test where object_id=222;

可以通过上述sql得到需变量绑定的sql