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

--//以前如果sql语句出现绑定变量与常量混合的sql语句,计算force_matching_signature与EXACT_MATCHING_SIGNATURE是一样的.21c做
--//了一些改进,这样可以使用sql profile优化固定这样一类sql语句,以前没有使用21c,现在可以验证看看.

1.环境:
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

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

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

  COUNT(1)
----------
         1
--//= 前后没有空格.
SCOTT@book01p> @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  2024-09-01 10:37:25    16777216

SCOTT@book01p> select count(1) from emp where empno = :empnum and deptno=20;

  COUNT(1)
----------
         0

SCOTT@book01p> @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  2024-09-01 10:37:35    16777216

SCOTT@book01p> 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     12531360796234248997     13157356546279728935
9kmf47x7byqq8     12531360796234248997     12268692852591778641

--//可以发现现在2条sql语句FORCE_MATCHING_SIGNATURE相同等于12531360796234248997.
--//使用我写的sql_idz.sh脚本验证看看.

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

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

SCOTT@book01p> @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  2024-09-01 10:40:25    16777216

SCOTT@book01p> 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"
--//有许多要求,=两边要有空格,count ( 之间也要有空格.
--//小写变成大写.注意计算结尾没有chr(0).

$ sql_idz.sh 'SELECT COUNT ( :"SYS_B_0" ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = :"SYS_B_1"' 3
sql_text = SELECT COUNT ( :"SYS_B_0" ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = :"SYS_B_1"
full_hash_value(16) = BEB759D45ACFAE8EADE856400BCC7325 or beb759d45acfae8eade856400bcc7325
xxxxx_matching_signature(10) = -5915383277475302619 or  12531360796234248997
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
hash_value(10) = 197948197
sql_id(32) = avu2q805wswt5
sql_id(32) = avu2q805wswt5
sql_id(32) = avu2q805wswt5

--//如果前面为负数,以第2列为主,反之第1列.等于12531360796234248997完全能对上.
--//full_hash_value(16) = beb759d45acfae8eade856400bcc7325,取后16位 ade856400bcc7325 = 12531360796234248997.

$ sql_idz.sh 'SELECT COUNT ( 1 ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = 10' 3
sql_text = SELECT COUNT ( 1 ) FROM EMP WHERE EMPNO = :EMPNUM AND DEPTNO = 10
full_hash_value(16) = 07896D82773C0975AA432725A91B5B51 or 07896d82773c0975aa432725a91b5b51
xxxxx_matching_signature(10) = -6178051221117772975 or  12268692852591778641
hash_value(10) = 2837142353
sql_id(32) = anht74qnjqquj
sql_id(32) = anht74qnjqquj
sql_id(32) = anht74qnjqquj

--//9kmf47x7byqq8  12531360796234248997   12268692852591778641,EXACT_MATCHING_SIGNATURE完全一致.

--//另外oracle提供dbms_sqltune.sqltext_to_signature函数FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE.
--//直接带入字符串就可以计算.

SCOTT@book01p> @ desc_proc sys dbms_sqltune sqltext_to_signature
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME          SEQUENCE ARGUMENT_NAME DATA_TYPE      IN_OUT DEFAULTED
----- ------------ -------------------- -------- ------------- -------------- ------ ----------
SYS   DBMS_SQLTUNE SQLTEXT_TO_SIGNATURE        1               NUMBER         OUT    N
                                               2 SQL_TEXT      CLOB           IN     N
                                               3 FORCE_MATCH   PL/SQL BOOLEAN IN     Y
                                               1               NUMBER         OUT    N
                                               2 SQL_TEXT      CLOB           IN     N
                                               3 FORCE_MATCH   BINARY_INTEGER IN     N
6 rows selected.

SCOTT@book01p> set numw 20
select dbms_sqltune.sqltext_to_signature('select count(1) from emp where empno = :empnum and deptno = 10') exact_match_signature,
  dbms_sqltune.sqltext_to_signature('select count(1) from emp where empno = :empnum and deptno = 10',force_match=>1) force_match_signature from dual;
--//= 前后没有空格.
EXACT_MATCH_SIGNATURE FORCE_MATCH_SIGNATURE
--------------------- ---------------------
 12268692852591778641  12531360796234248997