[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
 
posted @   lfree  阅读(7)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示