[20120918]理解v$sql的exact_matching_signature与force_matching_signature.txt

    理解v$sql的exact_matching_signature与force_matching_signature,对于使用sql profile以及SPM有重要意义,自己对于这个一直没有很好的理解,

今天做一个测试看看,可能不全面^_^.

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

1.建立测试例子:

create table t as  select rownum id1,trunc((rownum-1)/10)+1 id2,'test' name from dual connect by level<=100;

create index  i_t_id1 on t(id1);

alter system flush shared_pool;

select /*+ findme */ name from t where id1=42;

select /*+ findme1 */ name from t where id1=42;

select /*+ findme */  Name from t where id1=42;

SELECT /*+ findme */ nAme FROM t WHERE id1 = 42;

SELECT /*+ Findme */ nAme FROM T WHERE id1 = 42 ;

SELECT /*+ Findme test */ nAme FROM T WHERE id1 = 42 ;

SELECT /*+ full(t) Findme */ nAme FROM T WHERE id1 = 42 ;

SELECT /*+ index(t) Findme */ nAme FROM T WHERE id1 = 42 ;

column format r 99

column sql_text format a60

column force_matching_signature format 99999999999999999999

column exact_matching_signature format 99999999999999999999

select rownum r,x.* from (

select sql_id,plan_hash_value,sql_text,exact_matching_signature,force_matching_signature

from v$sql where lower(sql_text) like '%/*%findme%' and sql_text not like '%v$sql%' order by exact_matching_signature) x;

  R SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

--- ------------- --------------- ------------------------------------------------------------ ------------------------ ------------------------

  1 0m8m91zz80rvh      1183254286 SELECT /*+ index(t) Findme */ nAme FROM T WHERE id1 = 42          1611223796141114091     11078870919385499790

  2 f7aadcsrf2kkp      1601196873 SELECT /*+ full(t) Findme */ nAme FROM T WHERE id1 = 42           2823236535463672246     17243933052976400209

  3 dhfmwm002g4z4      1183254286 SELECT /*+ Findme */ nAme FROM T WHERE id1 = 42                   4375557158775460903       698688741679991361

  4 85t7vsqqgjpst      1183254286 select /*+ findme */ name from t where id1=42                     4375557158775460903       698688741679991361

  5 5bcck0z12q1g6      1183254286 SELECT /*+ findme */ nAme FROM t WHERE id1 = 42                   4375557158775460903       698688741679991361

  6 7vbndspk1kt83      1183254286 select /*+ findme */  Name from t where id1=42                    4375557158775460903       698688741679991361

  7 2d7cgsth517gy      1183254286 SELECT /*+ Findme test */ nAme FROM T WHERE id1 = 42              6656879127321462824      2920394029241435021

  8 apv57y4bk2uqv      1183254286 select /*+ findme1 */ name from t where id1=42                   14445306118291341866     11192650436549747287

8 rows selected.

-- 仔细观察,可以发现:

1.EXACT_MATCHING_SIGNATURE 一样的,比如3,4,5,6 EXACT_MATCHING_SIGNATURE=4375557158775460903,其对应的FORCE_MATCHING_SIGNATURE也一样(698688741679991361).

2.如果注解仅仅是大小写的问题,其EXACT_MATCHING_SIGNATURE也是一样的,其他空格大小写也不受影响.

3.如果有注解不同,其EXACT_MATCHING_SIGNATURE就不同了.

我的感觉就是把语句换成大写,注解部分也是换成大写,去除多余的空格等字符,算出一个东西.

这个东西很容易联想oracle的一个参数cursor_sharing.

exect=>表示精确匹配

force=>估计就是绑定以后匹配的.

alter system flush shared_pool;

select /*+ findme */ name from t where id1= 42;

select /*+ Findme */ name from t where id1= 43;

SQL> @a

  R SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

--- ------------- --------------- ------------------------------------------------------------ ------------------------ ------------------------

  1 373zfvx9h7xah      1183254286 select /*+ findme */ name from t where id1= 42                    4375557158775460903       698688741679991361

  2 5nyk32fjxxqkc      1183254286 select /*+ Findme */ name from t where id1= 43                    7094585129451441419       698688741679991361

--想象把常量换成了变量,这里的FORCE_MATCHING_SIGNATURE一样.

SQL> alter system flush shared_pool;

SQL> variable v_id1 number ;

SQL> variable v_idx number ;

SQL> exec :v_id1 := 42 ;

SQL> exec :v_idx := 43 ;

SQL> select /*+ Findme */ name from t where id1= :v_id1 and id2=1;

SQL> select /*+ Findme */ name from t where id1= :v_id1 and id2=2;

SQL> @a

  R SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

--- ------------- --------------- ------------------------------------------------------------ ------------------------ ------------------------

  1 b3x2pcgkxaxft      1183254286 select /*+ Findme */ name from t where id1= :v_id1 and id2=2      7008393373276421720      7008393373276421720

  2 ckr7rh1zfrwcv      1183254286 select /*+ Findme */ name from t where id1= :v_id1 and id2=1     14799038700516685754     14799038700516685754

--但是如果语句中存在绑定变量,这个又不一样.而且这个时候EXACT_MATCHING_SIGNATURE = FORCE_MATCHING_SIGNATURE.

--有点乱,google找到一篇文章,老熊总结的,转抄如下.

http://www.laoxiong.net/sql-profiles-part.html

    有如下的结论：对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果

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

比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。但是例

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