2023-01-24 一、NoSQL資料庫 1、NoSQL資料庫的簡介 NoSQL（NoSQL=Not Only SQL）,即“不僅僅是SQL”，泛指非關係型的資料庫。NosQL不依賴業務邏輯方式存儲，而以簡單的key-value模式存儲。因此大大的增加了資料庫的擴展能力。 （1）不遵循SQL標準 ...
[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語句帶來好處.
