[20240902]验证sql_idz.sh计算PLSQL代码块.txt

[20240902]验证sql_idz.sh计算PLSQL代码块.txt --//测试验证sql_idz.sh计算PLSQL代码块是否正确. 1.环境: SYS@book> @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. $ cat -Av mn.txt DECLARE$     l_count PLS_INTEGER;$ BEGIN$     FOR i IN 1..&&1$     LOOP$        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;$     END LOOP;$ END;$ /$ $ 2.测试: SCOTT@book01p> @ mn.txt Enter value for 1: 1e8 SYS@book> @ ashtop sql_id,TOP_LEVEL_SQL_ID    1=1 &1min     Total                                                                                       Distinct Distinct    Distinct   Seconds     AAS %This   SQL_ID        TOP_LEVEL_SQL FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1 --------- ------- ------- ------------- ------------- ------------------- ------------------- ---------- -------- -----------        45      .8   73% | b7zpx6jm6mdpy 8uqtnw78t2sa4 2024-09-02 16:35:10 2024-09-02 16:36:09         35       45          45         9      .2   15% |               8uqtnw78t2sa4 2024-09-02 16:35:20 2024-09-02 16:36:08          1        9           9         6      .1   10% | 8uqtnw78t2sa4 8uqtnw78t2sa4 2024-09-02 16:35:14 2024-09-02 16:36:01          2        6           2         2      .0    3% |                             2024-09-02 16:35:42 2024-09-02 16:36:03          1        2           2 --//sql_id=8uqtnw78t2sa4对应plsql代码块. SYS@book> @ sql_id 8uqtnw78t2sa4 --SQL_ID = 8uqtnw78t2sa4 DECLARE     l_count PLS_INTEGER; BEGIN     FOR i IN 1..1e8     LOOP        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;     END LOOP; END;; --//注:我写的sql_id脚本自动在结尾加上一个分号.PL/sql变成2个分号. $ cp mn.txt aa2.txt --//适当编辑aa2.txt,替换&&1=>1e8,取消结尾的/以及后面字符，在结尾的;在加上一个;主要原因我写的计算脚本计算时会丢弃结尾的1 --//个分号. $ cat -Av aa2.txt DECLARE$     l_count PLS_INTEGER;$ BEGIN$     FOR i IN 1..1e8$     LOOP$        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;$     END LOOP;$ END;;$ $ sql_idz.sh aa2.txt 2 sql_text = DECLARE     l_count PLS_INTEGER; BEGIN     FOR i IN 1..1e8     LOOP        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;     END LOOP; END;\0 full_hash_value(16) = 4A569457CB346ADE8D5B34E1D1916144 or 4a569457cb346ade8d5b34e1d1916144 hash_value(10) = 3515965764 sql_id(32) = 8uqtnw78t2sa4 sql_id(32) = 8uqtnw78t2sa4 sql_id(32) = 8uqtnw78t2sa4 --//计算完成正确. SYS@book> @ sharepool/shp4x 8uqtnw78t2sa4 0 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address   0000000070210BB8 000000006A7BB380 DECLARE     l_count PLS_INTEGER; BEGIN            1          2          0 000000006A7B9C50 000000006A7BA638       4032      16464       3455     23951      23951 3515965764 8uqtnw78t2sa4          0 parent handle address  000000006A7BB380 000000006A7BB380 DECLARE     l_count PLS_INTEGER; BEGIN            1          0          0 0000000070211158 00                     4064          0          0      4064       4064 3515965764 8uqtnw78t2sa4      65535 --//如果脚本如下,结尾仅仅1个分号. $ cat aa2.txt DECLARE     l_count PLS_INTEGER; BEGIN     FOR i IN 1..1e8     LOOP        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;     END LOOP; END; --//加入参数3补上';\0' $ sql_idz.sh aa2.txt 2 ';\0' sql_text = DECLARE     l_count PLS_INTEGER; BEGIN     FOR i IN 1..1e8     LOOP        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;     END LOOP; END;\0 full_hash_value(16) = 4A569457CB346ADE8D5B34E1D1916144 or 4a569457cb346ade8d5b34e1d1916144 hash_value(10) = 3515965764 sql_id(32) = 8uqtnw78t2sa4 sql_id(32) = 8uqtnw78t2sa4 sql_id(32) = 8uqtnw78t2sa4 --//计算完成正确. 3.附上源代码: --//^M 在vim for linux下按ctrl+v ctrl+M输入. $ cat -v ~/bin/sql_idz.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 for sqlplus  2=sql of text file for other 3=original # argv3 default = '\0' add tailstr odebug=${ODEBUG:-0} oflag=${2:-0} tailstr=${3:-'\0'} if [ $oflag -eq 0 ] then     sql_text=${1}${tailstr} fi # sqlplus format sql_text if [ $oflag -eq 1 ] then sql_text="$( cat $1 | sed -e "s/^M$//" -e "s/\s*$//" -e '$s/;$//')""${tailstr}" # sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed -e "s/^M$//" -e sed '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed '$s/;\s*$//')"'\0' fi # other format sql_text if [ $oflag -eq 2 ] then sql_text="$( cat $1 | sed '$s/;\s*$//')""${tailstr}" # sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed -e "s/^M$//" -e '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed '$s/;\s*//')"'\0' fi # exact_matching_signature, force_matching_signature if [ $oflag -eq 3 ] then     sql_text=${1} 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 or ${v1,,}" if [ $oflag -eq 3 ] ; then         echo "xxxxx_matching_signature(10) = $(( 16#$v2 )) or " $(echo $(( 16#$v2 )) + 2^64|bc ) fi 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)" $ cat shp4x.sql column N0_6_16 format 99999999 column handle_type format a22 SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ DECODE (kglhdadr,                kglhdpar, 'parent handle address',                'child handle address')            handle_type,        kglhdadr,        kglhdpar,        --//substr(kglnaobj,1,40) c40,        substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40)  c40,            KGLHDLMD,            KGLHDPMD,            kglhdivc,        kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,        kglobhs0+kglobhs6+kglobt16 N0_6_16,            kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,            kglnahsh,            kglobt03,            kglobt09   FROM x$kglob  WHERE kglobt03 = lower('&1') or KGLNAHSH= &2; $ 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