[20240320]空格与sqlpus的sql语句.txt-牛翰网

[20240320]空格与sqlpus的sql语句.txt

–//优化sql语句时遇到的问题，自己上我发现我手工执行获得的sql_id与程序里面的sql_id不一致,原因很多sqlplus下如果是dos文本格

–//式,计算的文本会将\r\n字符变成\n,如果多行,文本结尾的空格会删除等等,通过例子说明:

1.环境：

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

2.测试1：

–//开头存在空格.

SCOTT@book>   select * from dept where deptno=10;

    DEPTNO DNAME          LOC

———- ————– ————-

        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash

HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

832478979 agw1v10stx7s3 0 40707 2852011669 319e9f03 2024-03-20 08:31:08 16777216

SCOTT@book> select * from dept where deptno=10;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

SCOTT@book> @ hash

HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

911274289 4xamnunv51w9j 0 61745 2852011669 3650f131 2024-03-20 08:31:14 16777216

SCOTT@book> select sql_text from v$sql where sql_id in (‘agw1v10stx7s3′,’4xamnunv51w9j’);

SQL_TEXT

————————————————————

select * from dept where deptno=10

select * from dept where deptno=10

–//并没有删除开头的sql语句。

3.测试2:

–//执行脚本的结尾存在空格.

SCOTT@book> select * from dept where deptno=10   ;

    DEPTNO DNAME          LOC

———- ————– ————-

        10 ACCOUNTING     NEW YORK

SCOTT@book> @ hash

HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

2103677907 8qprua5yq74ym 0 103379 2852011669 7d6393d3 2024-03-22 15:58:00 16777216

SCOTT@book> select * from dept where deptno=10;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

SCOTT@book> select sql_text||’A’ c60 from v$sql where sql_id in (‘8qprua5yq74ym’,’4xamnunv51w9j’);

C60

————————————————————

select * from dept where deptno=10A

select * from dept where deptno=10 A

–//很明显分号结尾的空格计算在内.

4.测试3:

–//建立文件b1.txt,unix文件格式。

$ cat -Ev b1.txt

select * from dept where $

deptno=20;$

–//where后面有4个空格。

SCOTT@book> @ b1.txt

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

SCOTT@book> @ hash

HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

561182986 4rj7xbhhr5y8a 0 63754 2852011669 2172f90a 2024-03-20 09:04:38 16777221

–//记下sql_id=4rj7xbhhr5y8a

–//首先看看v$sql视图sql_text与sql_fulltext的区别：

SCOTT@book> select sql_text from v$sql where sql_id=’4rj7xbhhr5y8a’;

SQL_TEXT

————————————————————

select * from dept where deptno=20

SCOTT@book> select sql_fulltext c60 from v$sql where sql_id=’4rj7xbhhr5y8a’;

C60

————————————————————

select * from dept where

deptno=20

–//可以看出v$sql视图sql_text与sql_fulltext的区别，sql_text删除结尾的空格，并且过滤了\n字符，而sql_fulltext基本原样输出。

–//另外注意一点两者sql_text，sql_fulltext数据类型不一样，前者varchar2(1000)，后者clob类型。

–//sql_id的计算问题：

SCOTT@book> spool b31.txt

SCOTT@book> @ sql_idz 4rj7xbhhr5y8a

–SQL_ID = 4rj7xbhhr5y8a

select * from dept where

deptno=20;

SCOTT@book> spool off

$ cat -Ev b31.txt

SCOTT@book> @ sql_idz 4rj7xbhhr5y8a$

$

–SQL_ID = 4rj7xbhhr5y8a$

$

select * from dept where$

deptno=20;$

$

SCOTT@book> spool off$

–//注意看where后面的空格丢失了,也许输出忘记考虑trimspool的设置问题。看看计算如何.

$ ~/bin/sql_idx.sh b1.txt 1 > bb.txt

sql_text = select * from dept where $

deptno=20\0$

full_hash_value(16) = C141E01E5D8842BB39E5FAFE98FC8176 $

hash_value(10) = 2566685046 $

sql_id(32) = 3mtguzucgt0bq$

sql_id(32) = 3mtguzucgt0bq$

sql_id(32) = 3mtguzucgt0bq$

–//很明显使用我写的脚本计算sql_id=3mtguzucgt0bq与sqlplus程序执行的sql_id=4rj7xbhhr5y8a不同.

–//改写如下：

$ cp b1.txt b2.txt

$ vi b2.txt

$ cat -Ev b2.txt

select * from dept where$

deptno=20;$

$ ~/bin/sql_idx.sh b2.txt 1 >| bb1.txt

$ cat -Ev bb1.txt

sql_text = select * from dept where$

deptno=20\0$

full_hash_value(16) = E375F34FDCD15D104BC4FD5C2172F90A $

hash_value(10) = 561182986 $

sql_id(32) = 4rj7xbhhr5y8a$

sql_id(32) = 4rj7xbhhr5y8a$

sql_id(32) = 4rj7xbhhr5y8a$

–//很明显sql_id计算使用的是b2.txt的文件计算的，where后面的空格取消了。

5.测试4：

–//再来看看文件格式问题。

$ unix2dos b1.txt

unix2dos: converting file b1.txt to DOS format …

SCOTT@book> @ b1.txt

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

–//还是一样，说明还是按照b2.txt的文件计算sql_id。

6.测试5:

–//建立文件bx.sql,unix文件格式。

$ cat -Ev bx.sql

select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20;$

SCOTT@book> @ bx.sql

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

SCOTT@book> @ hash

HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

1668358914 cymmqupjr28s2 0 74498 2852011669 63712302 2024-03-20 15:16:34 16777221

SCOTT@book> select sql_text from v$sql where sql_id=’cymmqupjr28s2′;

SQL_TEXT

————————————————————

deptno=20

SCOTT@book> select sql_fulltext from v$sql where sql_id=’cymmqupjr28s2′;

SQL_FULLTEXT

———————————————————–

deptno=20

–//^M的输出导致输出看的非常怪异!!

SCOTT@book> @ sql_idx cymmqupjr28s2

–SQL_ID = cymmqupjr28s2

deptno=20;

SCOTT@book> @ sql_idz cymmqupjr28s2

–SQL_ID = cymmqupjr28s2

deptno=20;

SCOTT@book> @ sql_id cymmqupjr28s2

–SQL_ID = cymmqupjr28s2

select * from dept where deptno=20;

–//注:我写的3个查看sql_id的版本,sql_id.sql版本过滤掉^M.sql_idz.sql的保持原样输出.

SCOTT@book> spool by.txt

SCOTT@book> select sql_fulltext from v$sql where sql_id=’cymmqupjr28s2′;

SQL_FULLTEXT

————————————————————————-

deptno=20

SCOTT@book> spool off

–//编辑by.txt后如下：

$ cat -Ev by.txt

select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20$

$ ~/bin/sql_idx.sh by.txt 1 | cat -Ev

sql_text = select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20\0$

full_hash_value(16) = 0E6C6BF775D7AA8DCF4E76D563712302 $

hash_value(10) = 1668358914 $

sql_id(32) = cymmqupjr28s2$

sql_id(32) = cymmqupjr28s2$

sql_id(32) = cymmqupjr28s2$

$ ~/bin/sql_idx.sh bx.sql 1 | cat -Ev

sql_text = select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20\0$

full_hash_value(16) = 0E6C6BF775D7AA8DCF4E76D563712302 $

hash_value(10) = 1668358914 $

sql_id(32) = cymmqupjr28s2$

sql_id(32) = cymmqupjr28s2$

sql_id(32) = cymmqupjr28s2$

–//可以看出中间出现的^M不过过滤.

7.继续测试：

–//重新编辑bx.sql，内容如下：

$ cat -Ev bx.sql

select ^M$

* $

^M $

from ^M $

dept ^Mwhere ^Mdeptno=20;$

SCOTT@book> SET SQLBLANKLINES OFF

SCOTT@book> @ bx.sql

SP2-0042: unknown command “from” – rest of line ignored.

wher…” – rest of line ignored.ing “dept

–//存在一个空行。

SCOTT@book> SET SQLBLANKLINES ON

SCOTT@book> @ bx.sql

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

SCOTT@book> @ hash

HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

987480685 a9mmzpcxdrhmd 0 115309 2852011669 3adbc26d 2024-03-20 15:27:59 16777220

SCOTT@book> select sql_text from v$sql where sql_id=’a9mmzpcxdrhmd’;

SQL_TEXT

————————————————————

deptno=20* from dept

SCOTT@book> select sql_fulltext c60 from v$sql where sql_id=’a9mmzpcxdrhmd’;

C60

————————————————————

select

*

from

deptno=20

SCOTT@book> @ sql_id a9mmzpcxdrhmd

–SQL_ID = a9mmzpcxdrhmd

select

*

from

dept where deptno=20;

SCOTT@book> @ sql_idx a9mmzpcxdrhmd

–SQL_ID = a9mmzpcxdrhmd

select

*

from

deptno=20;

SCOTT@book> @ sql_idz a9mmzpcxdrhmd

–SQL_ID = a9mmzpcxdrhmd

select

*

from

deptno=20;

SCOTT@book> spool by.txt

SCOTT@book> select sql_fulltext c60 from v$sql where sql_id=’a9mmzpcxdrhmd’;

C60

————————————————————

select

*

from

deptno=20

SCOTT@book> spool off

$ cat -Ev by.txt

SCOTT@book> select sql_fulltext c60 from v$sql where sql_id=’a9mmzpcxdrhmd’;$

C60$

————————————————————$

select$

*$

$

from$

dept ^Mwhere ^Mdeptno=20$

$

$

SCOTT@book> spool off$

–//为了对比方便,复制上面的cat -Ev bx.sql输出:

$ cat -Ev bx.sql

select ^M$

* $

^M $

from ^M $

dept ^Mwhere ^Mdeptno=20;$

–//对比bx.sql的内容:

–//第1行select后面的空格,^M消失。

–//第2行*后面的空格消失。

–//第3行全部删除。

–//第4行from后面的空格以及^M删除.

–//第5行保留中间出现的的^M。

–//编辑by.txt

$ cat -Ev by.txt

select$

*$

$

from$

dept ^Mwhere ^Mdeptno=20$

$ ~/bin/sql_idx.sh by.txt 1 | cat -Ev

sql_text = select$

*$

$

from$

dept ^Mwhere ^Mdeptno=20\0$

full_hash_value(16) = D830D3E1D201C57AA4CE7FAB3ADBC26D $

hash_value(10) = 987480685 $

sql_id(32) = a9mmzpcxdrhmd$

sql_id(32) = a9mmzpcxdrhmd$

sql_id(32) = a9mmzpcxdrhmd$

–//可以确定sql_id计算使用的是v$sql.sql_fulltext计算的。

SCOTT@book> spool bz.txt

SCOTT@book> @ sql_idz a9mmzpcxdrhmd

–SQL_ID = a9mmzpcxdrhmd

select

*

from

deptno=20;

SCOTT@book> spool off

$ cat -Ev bz.txt

select$

*$

$

from$

dept ^Mwhere ^Mdeptno=20;$

$ ~/bin/sql_idx.sh bz.txt 1 | cat -Ev

sql_text = select$

*$

$

from$

dept ^Mwhere ^Mdeptno=20\0$

full_hash_value(16) = D830D3E1D201C57AA4CE7FAB3ADBC26D $

hash_value(10) = 987480685 $

sql_id(32) = a9mmzpcxdrhmd$

sql_id(32) = a9mmzpcxdrhmd$

sql_id(32) = a9mmzpcxdrhmd$

8.总结:

–//我以前一直认为都是按照sqlplus的方式计算的，实际上并不是这样的情况，其它程序比如PB开发的程序或者w3wp.exe的程序，里面

–//sql语句是保持”原样”,大部分应该如此.

–//这样就好解析我生产系统遇到的问题。

–//我生成的脚本过滤掉chr(13)变成unix格式，在这种情况下计算的sql_id肯定不一样，另外结尾的空格也保留下来，总之sqlplus下执

–//行获得sql_id 与其它程序的sql_id不同是很正常的情况。

–//你可以测试最新的toad下结尾的空格是保留的,早期的toad版本是在执行前在最后一行加入1个空格,最新的版本没有这个问题.

–//参考:

–//[20120327]toad与sqlplus下执行sql语句的一个细节.txt ->https://blog.itpub.net/267265/viewspace-719592/

–//[20150803]toad 12版本1个小变化.txt -> https://blog.itpub.net/267265/viewspace-1760071/

9.附上相关脚本:

$ cat sql_idz.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 SQL_FULLTEXT||’;’ SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = ‘&&1’ AND ROWNUM = 1;

        –SELECT REPLACE (SQL_FULLTEXT||’;’, ”, ”) SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = ‘&&1’ AND ROWNUM = 1;

        –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 SQL_TEXT||’;’ INTO V_SQL_FULLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID=’&&1′ AND ROWNUM=1;

            –SELECT REPLACE (SQL_TEXT||’;’,”,”) INTO V_SQL_FULLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID=’&&1′ AND ROWNUM=1;

            –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 ~/bin/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’d “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)”

本站所有资源来自于网络，仅供学习与参考，请勿用于商业用途，否则产生的一切后果将由您(转载者)自己承担！
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。

THE END