[20180301]FORCE_MATCHING_SIGNATURE的計算.txt

--//連結的討論,http://www.itpub.net/thread-2097379-1-1.html.
--//簡單測試FORCE_MATCHING_SIGNATURE.我個人認為實際上的計算與sql_id,full_hash_value的計算相似.
--//驗證看看.

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select * from dept where deptno=     10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--//確定sql_id=gku793bznrb1h,注意deptno= 10之間有5個空格.

2.測試:
SCOTT@book> select sql_id,sql_text,EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE,hash_value from v$sql where sql_id='gku793bznrb1h';
SQL_ID SQL_TEXT EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE HASH_VALUE
------------- --------------------------------------- ------------------------ ------------------------ ----------
gku793bznrb1h select * from dept where deptno= 10 2074571472348075010 15945160967817283707 4283149360

SCOTT@book> select name c70,hash_value,full_hash_value from V$DB_OBJECT_CACHE where hash_value=4283149360;
C70                                                                    HASH_VALUE FULL_HASH_VALUE
---------------------------------------------------------------------- ---------- --------------------------------
select * from dept where deptno=     10                                4283149360 046a3c5114a847aff968e91aff4bac30
select * from dept where deptno=     10                                4283149360 046a3c5114a847aff968e91aff4bac30

--//後64bit作為sql_id,後32bit作為hash_value.另外說明intel系列cpu要考慮大小頭問題(其它cpu沒有測試)
--//0xff4bac30 = 4283149360. 與hash_value一直.
--//FULL_HASH_VALUE的結果與我上面的計算一致.
--//sql_id換成成10進位制如下:
--//0xf968e91aff4bac30 = 17971870615183535152,並沒有匹配EXACT_MATCHING_SIGNATURE,說明不是透過FULL_HASH_VALUE的後64bit計算而來.

3.繼續測試:
SCOTT@book> select * from dept where deptno=    10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
--//確定sql_id=9zsxgxshynytq.

SCOTT@book> select sql_id,sql_text,EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE,hash_value from v$sql where sql_id in ('gku793bznrb1h','9zsxgxshynytq');
SQL_ID        SQL_TEXT                                 EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE HASH_VALUE
------------- ---------------------------------------- ------------------------ ------------------------ ----------
9zsxgxshynytq select * from dept where deptno=    10        2074571472348075010     15945160967817283707 569015094
gku793bznrb1h select * from dept where deptno=     10       2074571472348075010     15945160967817283707 4283149360

--//注意看EXACT_MATCHING_SIGNATURE兩者是一樣的.說明在計算EXACT_MATCHING_SIGNATURE時格式化sql語句.

--//取後面64bit 1cca5bc3 36775402轉換10進位制.
--//0x1cca5bc336775402 = 2074571472348075010
--//^_^,從正好一致.說明EXACT_MATCHING_SIGNATURE的計算就是sql語句格式化為'SELECT * FROM DEPT WHERE DEPTNO = 10'.

4.FORCE_MATCHING_SIGNATURE如何計算呢?
--//既然與繫結變數有關,設定cursor_sharing=force測試看看.
--//退出重新整理共享池.
SCOTT@book> alter system flush shared_pool;
System altered.

SCOTT@book> alter session set cursor_sharing=force ;
Session altered.

SCOTT@book> select * from dept where deptno=     10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--//確定sql_id=cw0dpvjknsczw.

SCOTT@book> select sql_id,sql_text,EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE,hash_value from v$sql where sql_id in ('cw0dpvjknsczw');
SQL_ID SQL_TEXT EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE HASH_VALUE
------------- ----------------------------------------------- ------------------------ ------------------------ ----------
cw0dpvjknsczw select * from dept where deptno= :"SYS_B_0" 15945160967817283707 15945160967817283707 1699492860

--//sql語句被轉換為select * from dept where deptno= :"SYS_B_0".
--//注意EXACT_MATCHING_SIGNATURE變化了,而FORCE_MATCHING_SIGNATURE與前面一樣.

--//15945160967817283707 = 0xdd48976b4d2c487b

--//取後面64bit dd48976b 4d2c487b 轉換10進位制.
--//0xdd48976b4d2c487b = 15945160967817283707
--//正好對上FORCE_MATCHING_SIGNATURE的值.

5.總結:
--//許多情況很複雜,我並不想知道EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE如何計算,大致演算法應該差不多.
--//另外在連結提到一種情況,繫結變數與常量同時出現時:
--//http://blog.itpub.net/267265/viewspace-743928/

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);

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

--//這裡常量就沒有轉換.
--//14799038700516685754 = 0xcd60bf4a198e37ba

$ echo -e -n 'SELECT /*+FINDME*/ NAME FROM T WHERE ID1 = :V_ID1 AND ID2 = 1' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | grep "^0000000" | cut -f2,3,4,5 -d" "
65ab78da a0f84b77 cd60bf4a 198e37ba

--//0xcd60bf4a198e37ba = 14799038700516685754
--//注意幾個細節,計算提示的空格取消了.變數與等號之間存在空格.小寫變成了大寫.我當時的結論:
--//http://blog.itpub.net/267265/viewspace-743928/
有如下的結論：對SQL語句，去掉重複的空格(不包括字元常量)，將大小寫轉換成相同，比如均為大寫(不包括字元常量)後，如果
SQL相同，那麼SQL語句的exact_matching_signature就是相同的。對SQL語句，去掉重複的空格(不包括字元常量)，將大小寫轉換成相同，
比如均為大寫(不包括字元常量)，然後去掉SQL中的常量，如果SQL相同，那麼SQL語句的force_matching_signature就是相同的。但是例
外的情況是：如果SQL中有繫結變數，force_matching_signature就會與exact_matching_signature一樣的生成標準。

[20180301]FORCE_MATCHING_SIGNATURE的計算

相關文章