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

[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