统计没有使用绑定变量的sql语句
摘要：
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count(1) as counts
from v$sql
where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2 desc;
create table tb_force(id integer,name varchar2(10));
insert into tb_force(id,name) values ('1','111');
insert into tb_force(id,name) values ('2','222');
insert into tb_force(id,name) values ('3','333');
commit;
SQL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> select /*mystatement*/ * from tb_force where id=1;
ID NAME
--------------------------------------- ----------
1 111
SQL> select /*mystatement*/ * from tb_force where id=2;
ID NAME
--------------------------------------- ----------
2 222
SQL> select /*mystatement*/ * from tb_force where id=3;
ID NAME
--------------------------------------- ----------
3 333
SQL> select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%mystatement%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------ ------------------------
select /*mystatement*/ * from tb_force where id=2 1.30958144015941E19 6.87046715690462E18
select /*mystatement*/ * from tb_force where id=1 1.30958144015941E19 5.16419277214989E17
select /*mystatement*/ * from tb_force where id=3 1.30958144015941E19 1.36147732398326E19
--id取不同值时，FORCE_MATCHING_SIGNATURE的值相同，而EXACT_MATCHING_SIGNATURE值不同，说明在cursor_sharing=force时，执行计划可共用；cursor_sharing=exact时，执行计划不可共用；实际
上我们使用绑定变量后，同样也可以达到执行计划共用的效果。
sys@ORCL> alter system set cursor_sharing=force;
System altered.
sys@ORCL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE
sys@ORCL> alter system flush shared_pool;
System altered.
SQL> select /*mystatement*/ * from tb_force where id=1;
ID NAME
--------------------------------------- ----------
1 111
SQL> select /*mystatement*/ * from tb_force where id=2;
ID NAME
--------------------------------------- ----------
2 222
SQL> select /*mystatement*/ * from tb_force where id=3;
ID NAME
--------------------------------------- ----------
3 333
SQL> select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%mystatement%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATU