[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)"