摘要：
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_SIGNATURE
-------------------------------------------------------------------------------- ------------------------ ------------------------
select /*mystatement*/ * from tb_force where id=:"SYS_B_0" 1.30958144015941E19 1.30958144015941E19
--当我们把cursor_sharing设置为force后，发现语句执行计划共用了，并且自动使用绑定变量(此时v$sql视图中只查询到一条与之相关的语句)。
--通过上面的实验，我们发现查找系统中有哪些语句未使用绑定变量，可根据FORCE_MATCHING_SIGNATURE实现。
--下面我们就通过FORCE_MATCHING_SIGNATURE来查找系统中未使用绑定变量的语句...
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;
--通过上面查询出来的FORCE_MATCHING_SIGNATURE值，来查找sql语句
select t.* from v$sql t where FORCE_MATCHING_SIGNATURE=16456394970215993993;
--延伸：
cursor_sharing有三个可选参数：EXACT、SIMILAR、FORCE。
--语句完全一样，则利用share pool中的语句，反之则重新生成(默认)。
EXACT：Only allows statements with identical text to share the same cursor.
--语句相似，但系统自行判断是否利用share pool中的语句(不推荐，bug很多)。
SIMILAR：Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or
the degree to which the plan is optimized.
--语句相似，系统强制利用share pool中的语句(不推荐，bug较多)。
FORCE：Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
