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

[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找到一篇文章,老熊總結的,轉抄如下.

    有如下的結論：對SQL語句，去掉重複的空格（不包括字元常量），將大小寫轉換成相同，比如均為大寫（不包括字元常量）後，如果

SQL相同，那麼SQL語句的exact_matching_signature就是相同的。對SQL語句，去掉重複的空格（不包括字元常量），將大小寫轉換成相同，

比如均為大寫（不包括字元常量），然後去掉SQL中的常量，如果SQL相同，那麼SQL語句的force_matching_signature就是相同的。但是例

外的情況是：如果SQL中有繫結變數，force_matching_signature就會與exact_matching_signature一樣的生成標準。