[20180301]FORCE_MATCHING_SIGNATURE的计算-CSDN博客

本文探讨了Oracle数据库中SQL语句的FORCE_MATCHING_SIGNATURE和EXACT_MATCHING_SIGNATURE的计算原理。通过实际案例，详细分析了不同SQL格式化及绑定变量对这两种签名的影响。

摘要由CSDN通过智能技术生成

[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一样的生成标准。