理解v$sql的exact_matching_signature与force_matching_signature

理解v$sql的exact_matching_signature与force_matching_signature

对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。

对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。

 

但是例外的情况是：如果SQL中有绑定变量，force_matching_signature就会与exact_matching_signature一样的生成标准。

select /*+ findme */ name from t01 where id=2;
select /*+ findme1 */ name from t01 where id=2;
select /*+ findme */  Name from t01 where id=2;
SELECT /*+ findme */ nAme FROM t01 WHERE id=2;
SELECT /*+ Findme */ nAme FROM t01 WHERE id=2;
SELECT /*+ Findme test */ nAme FROM t01 WHERE id=2;
SELECT /*+ full(t01) Findme */ nAme FROM t01 WHERE id=2;
SELECT /*+ index(t01) Findme */ nAme FROM t01 WHERE id=2;

SQL> create table t01(id number,name varchar2(30));
SQL> insert into t01 values(1,'bb01');
SQL> insert into t01 values(2,'bb02');
SQL> insert into t01 values(3,'bb03');
SQL> insert into t01 values(4,'bb04');
SQL> insert into t01 values(5,'bb05');

SQL> select /*+ findme */ name from t01 where id=2;
SQL> select /*+ findme1 */ name from t01 where id=2;
SQL> select /*+ findme */  Name from t01 where id=2;
SQL> SELECT /*+ findme */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ Findme */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ Findme test */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ full(t01) Findme */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ index(t01) Findme */ nAme FROM t01 WHERE id=2;
SQL> select sql_text,force_matching_signature,exact_matching_signature from v$sql where lower(sql_text) like '%/*%findme%' and sql_text not like '%v$sql%';
SQL_TEXT                                                                         FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------ ------------------------
select /*+ findme */  Name from t01 where id=2                                        2.61638783165569E18      1.81667648323122E19
select /*+ findme1 */ name from t01 where id=2                                        1.48618482593165E19      3.84294405114677E18
select /*+ findme */ name from t01 where id=2                                         2.61638783165569E18      1.81667648323122E19
SELECT /*+ index(t01) Findme */ nAme FROM t01 WHERE id=2                              8.64347201076369E18      1.06927716956949E19
SELECT /*+ full(t01) Findme */ nAme FROM t01 WHERE id=2                               3.77473736948433E18      2.01226189844612E18
SELECT /*+ Findme */ nAme FROM t01 WHERE id=2                                         2.61638783165569E18      1.81667648323122E19
SELECT /*+ findme */ nAme FROM t01 WHERE id=2                                         2.61638783165569E18      1.81667648323122E19
SELECT /*+ Findme test */ nAme FROM t01 WHERE id=2                                    1.31270739882141E19      1.18833249442956E19
8 rows selected


SQL> alter session set cursor_sharing=force;
SQL> alter system flush shared_pool;
SQL> select /*+ findme */ name from t01 where id=2;
SQL> select /*+ findme1 */ name from t01 where id=2;
SQL> select /*+ findme */  Name from t01 where id=2;
SQL> SELECT /*+ findme */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ Findme */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ Findme test */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ full(t01) Findme */ nAme FROM t01 WHERE id=2;
SQL> SELECT /*+ index(t01) Findme */ nAme FROM t01 WHERE id=2;
SQL> select sql_text,force_matching_signature,exact_matching_signature from v$sql where lower(sql_text) like '%/*%findme%' and sql_text not like '%v$sql%';
SQL_TEXT                                                                         FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------ ------------------------
select /*+ findme1 */ name from t01 where id=:"SYS_B_0"                               1.48618482593165E19      1.48618482593165E19
select /*+ findme */  Name from t01 where id=:"SYS_B_0"                               2.61638783165569E18      2.61638783165569E18
select /*+ findme */ name from t01 where id=:"SYS_B_0"                                2.61638783165569E18      2.61638783165569E18
SELECT /*+ index(t01) Findme */ nAme FROM t01 WHERE id=:"SYS_B_0"                     8.64347201076369E18      8.64347201076369E18
SELECT /*+ Findme test */ nAme FROM t01 WHERE id=:"SYS_B_0"                           1.31270739882141E19      1.31270739882141E19
SELECT /*+ Findme */ nAme FROM t01 WHERE id=:"SYS_B_0"                                2.61638783165569E18      2.61638783165569E18
SELECT /*+ full(t01) Findme */ nAme FROM t01 WHERE id=:"SYS_B_0"                      3.77473736948433E18      3.77473736948433E18
SELECT /*+ findme */ nAme FROM t01 WHERE id=:"SYS_B_0"                                2.61638783165569E18      2.61638783165569E18
8 rows selected

SQL>