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

来源：这里教程网时间：2026-03-03 18:20:38 作者：

[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语句带来好处.