[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