[20220111]該語句的sql_id如何計算的.txt

[20220111]該語句的sql_id如何計算的.txt

--//昨天定位一個資料庫問題,我發現使用我的計算sql_idx.sh的指令碼計算不對,做一個記錄並分析.

> select sql_id,prev_sql_id from v$session where sid=14382;
SQL_ID        PREV_SQL_ID
------------- -------------
50qyqnykr15au 9m7787camwh4m

> @ sql_id 9m7787camwh4m
--SQL_ID = 9m7787camwh4m
begin :id := sys.dbms_transaction.local_transaction_id; end;
;

--//該語句來自於plsql develop模組.
--//實際上讓我感興趣的是輸出帶有結尾的分號.注意下一行的分號是我輸出指令碼加上的.而一般的sql語句計算時不包括結尾的分號並且在結
--//尾加入\0.

$ sql_idx.sh "begin :id := sys.dbms_transaction.local_transaction_id; end;"  0
sql_text = begin :id := sys.dbms_transaction.local_transaction_id; end;\0
full_hash_value(16) = 4A67DF6F14018C5774E83D71EF85B8E5
hash_value(10) = 4018518245
sql_id(32) = 79u1xf7rsbf75
sql_id(32) = 79u1xf7rsbf75
sql_id(32) = 79u1xf7rsbf75

--//計算不對,再仔細檢視,發現下一個分號出現在下一行,這個分號是我執行指令碼輸出時加上的,可以確定真正的計算應該是包括\n:

$ sql_idx.sh "begin :id := sys.dbms_transaction.local_transaction_id; end;\n"  0
sql_text = begin :id := sys.dbms_transaction.local_transaction_id; end;\n\0
full_hash_value(16) = 3C11BF82C67CCD91999CE83B153E4093
hash_value(10) = 356401299
sql_id(32) = 9m7787camwh4m
sql_id(32) = 9m7787camwh4m
sql_id(32) = 9m7787camwh4m

--//我自己在sqlplus測試時突然明白為什麼.看具體測試:

SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> variable id number ;
SCOTT@book> begin :id := sys.dbms_transaction.local_transaction_id; end;
  2  /
PL/SQL procedure successfully completed.


SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ------------------- -----------
4018518245 79u1xf7rsbf75            0     112869               0 2022-01-11 08:26:51    16777216

--//注意看這裡並不一致，這也是我當時困惑的地方，實際上在plsqldev.exe上執行的是。

SCOTT@book> begin :id := sys.dbms_transaction.local_transaction_id; end;
  2
  3  /
PL/SQL procedure successfully completed.
--//分號後加一個回車再加一個斜線。實際上到這裡我還是很困惑，理論講應該是兩個回車，為什麼計算時1個，也是PLSQL程式碼執行的特
--//殊性。

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ------------------- -----------
 356401299 9m7787camwh4m            0      16531               0 2022-01-11 08:28:47    16777216

--//打入完成最後的分號時並不執行,要回車+回車+/ 後加入執行,對於PL/SQL語句是這樣執行的,這樣sql_id的計算就出現前面的情況.

--//附上指令碼：
$ cat sql_id.sql
SET LINESIZE 32767
--SET LINESIZE 4000
VAR V_SQL_FULLTEXT CLOB
COL SQL_FULLTEXT FOR A4000 WORD_WRAP

SET FEEDBACK OFF
SET SERVEROUTPUT ON

PROMPT
PROMPT --SQL_ID = &&1
PROMPT

DECLARE
    V_SQL_FULLTEXT   CLOB;
    V_COUNT          NUMBER;
BEGIN
    SELECT COUNT(*) INTO V_COUNT  FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM=1;
    IF  V_COUNT=1
    THEN
        SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;
        --SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), chr(13)) SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;
        DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
    ELSE
        SELECT COUNT(*)  INTO V_COUNT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
        IF  V_COUNT=1
        THEN
            SELECT REPLACE (SQL_TEXT||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
            --SELECT REPLACE (SQL_TEXT||';',CHR(13),chr(13))  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
            DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
        END IF;
    END IF;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        NULL;
END;
/

PROMPT
SET SERVEROUTPUT OFF
SET FEEDBACK 6
SET LINESIZE 277

$ cat sql_idx.sh
#! /bin/bash
# calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32).
# argv1 sql statement or sql of text file
# argv2 flag: 0= sql statement 1=sql of text file

odebug=${ODEBUG:-0}
oflag=${2:-0}

if [ $oflag -eq 0 ]
then
    sql_text=${1}'\0'
fi

if [ $oflag -eq 1 ]
then
#   sql_text="$( cat $1 | dos2unix | sed '$s/;\s*//')"'\0'
$//" | sed '$s/;\s*//')"'\0' "s/
#   sql_text="$( cat $1 | sed '$s/;\s*//')"'\0'
fi

v1=$(echo -e -n "$sql_text" | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 |  sed   -n  -e 's/^0\+ //' -e 's/ //gp' | tr 'a-z' 'A-Z')
v2=${v1:(-16):16}
v3=${v2:(-8):8}
# v2=$(echo "obase=16;ibase=16; $v1 % 10000000000000000" | bc| tr -d '\\\r\n')
# v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d '\\\r\n')

if [ $odebug -eq 1 ] ; then
        echo v1=$v1 v2=$v2 v3=$v3
fi

echo "sql_text = $sql_text"
echo "full_hash_value(16) = $v1 "
echo "hash_value(10) = $(( 16#$v3 )) "

BASE32=($(echo {0..9} {a..z} | tr -d 'eilo'))
res=''
for i in $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n')
do
        res=${res}${BASE32[$(( 10#$i ))]}
done
echo "sql_id(32) = $(printf "%13s" $res | tr ' ' '0')"
echo "sql_id(32) = $(printf "%013s" $res)"

res1=$(eval $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n' | awk 'BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]}", $1}' ))
echo "sql_id(32) = $(printf "%013s" $res1)"