[20230125]21c Force matching signature的计算.txt

--//昨天看了链接:https://hourim.wordpress.com/2023/01/22/force-matching-signature/
--//里面提到计算force_matching_signature的改变,以前如果sql语句出现绑定变量与常量混合的sql语句,计算
--//force_matching_signature与EXACT_MATCHING_SIGNATURE是一样的.21c做了一些改进,这样可以使用sql profile优化固定
--//这样一类sql语句.

--//我看了以前一些笔记,验证看看我推测的计算方法是否正确.

1.环境:
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.测试:
var   empnum number
exec :empnum := 7839

SCOTT@test01p> select count(1) from emp where empno = :empnum and deptno=10;
  COUNT(1)
----------
         1

SCOTT@test01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1321163464 9kmf47x7byqq8            0      88776      1126804136  4ebf5ac8  2023-01-25 09:31:07    16777216

SCOTT@test01p> select count(1) from emp where empno = :empnum and deptno=20;
  COUNT(1)
----------
         0

SCOTT@test01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1550218113 gz41rf5f6cww1            0      29569      1126804136  5c667381  2023-01-25 09:31:33    16777217

SCOTT@test01p> select sql_id ,force_matching_signature,EXACT_MATCHING_SIGNATURE from gv$sql where sql_id in ('9kmf47x7byqq8','gz41rf5f6cww1');
SQL_ID        FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
------------- ------------------------ ------------------------
gz41rf5f6cww1     13157356546279728935     13157356546279728935
9kmf47x7byqq8     12268692852591778641     12268692852591778641

--//不同sql_id的FORCE_MATCHING_SIGNATURE计算结果不同.但是相同sql_id的FORCE_MATCHING_SIGNATURE=EXACT_MATCHING_SIGNATURE.
--//说明这样的情况下FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE的计算方法相同.
--//FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE的计算参考链接:
--//http://blog.itpub.net/267265/viewspace-2151407/

--//12268692852591778641 = 0xaa432725a91b5b51
--//13157356546279728935 = 0xb69852117429e727

$ echo -e -n 'SELECT COUNT ( 1 ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = 10' | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 | grep "^0000000" | cut -f2,3,4,5 -d" "
07896d82 773c0975 aa432725 a91b5b51

$ echo -e -n 'SELECT COUNT ( 1 ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = 20' | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 | grep "^0000000" | cut -f2,3,4,5 -d" "
7b3b9483 3a5e529c b6985211 7429e727

--//后16位 正好对上.
--//顺便说明一下,oracle在计算FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE时,sql语句做了格式化处理的,比如等号两边存
--//在1个空格,count(1) 变成 COUNT ( 1 ) ,并且语句全部换成大写.

--//21c下计算如下:
select sql_id  ,to_char(force_matching_signature) from gv$sql where sql_id in ('9kmf47x7byqq8','gz41rf5f6cww1');

SQL_ID        TO_CHAR(FORCE_MATCHING_SIGNATURE)
------------- ---------------------------------
gz41rf5f6cww1 12531360796234248997
9kmf47x7byqq8 12531360796234248997

--//注:我自己没有21c的测试环境,不过我可以验证计算结果.
--//12531360796234248997 = 0xade856400bcc7325

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

SCOTT@test01p> select count(1) from emp where empno = :empnum and deptno=30;
  COUNT(1)
----------
         0

SCOTT@test01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1794040331 3y5y7ddpfxshb            0      57867      1126804136  6aeee20b  2023-01-25 10:01:41    16777216

SCOTT@test01p> select sql_text c90 from v$sqlarea where sql_id='3y5y7ddpfxshb';
C90
------------------------------------------------------------------------------------------
select count(:"SYS_B_0") from emp where empno = :empnum and deptno=:"SYS_B_1"

--//按照要求格式化后如下:
SELECT COUNT ( :"SYS_B_0" ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = :"SYS_B_1"

$ echo -e -n 'SELECT COUNT ( :"SYS_B_0" ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = :"SYS_B_1"' | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 | grep "^0000000" | cut -f2,3,4,5 -d" "
beb759d4 5acfae8e ade85640 0bcc7325
--//OK,完全能对上!!

$ echo -e -n 'SELECT COUNT ( :"SYS_B_0" ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = :"SYS_B_1"' | md5sum | sed 's/  -//'| xxd -r -p | od -t x4 | grep "^0000000" | cut -f4,5 -d" " | tr 'a-z' 'A-Z' |sed 's/ //;s/^/ibase=16;/'| bc
12531360796234248997

3.总结:
--//这个小小改进,至少对于使用sql profile来稳定一类这样混合绑定变量与常量的sql语句带来好处.