![[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql_id的计算.txt](/upload/202205/01/1bi132mys3a.jpg)
[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql_id的计算.txt
--//以前写的，使用DBMS_SHARED_POOL.MARKHOT标记热的sql_id,这样相同的sql语句使用不同的sql_id.
--//链接：http://blog.itpub.net/267265/viewspace-2147197/ => [20171110]sql语句相同sql_id可以不同吗.
--//好奇心想知道,oracle这种情况下如何计算的sql_id的。
1.环境:
SCOTT@book> @ &r/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> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//sql_id="4xamnunv51w9j",可以执行多次,避免sql语句退出共享池.
SELECT name
,hash_value
,full_hash_value
,namespace
,child_latch
,property hot_flag
,executions
,invalidations
FROM v$db_object_cache
WHERE name = "select * from dept where deptno=10";
NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- -------------
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 0 8 0
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 8 0
--//确定FULL_HASH_VALUE="1431c45dbddbb9e74eaa74d53650f131".
$ echo -e -n "select * from dept where deptno=10" | md5sum | sed "s/ -//" | xxd -r -p | od -t x4 | sed -n -e "s/^0000000 //" -e "s/ //gp"
1431c45dbddbb9e74eaa74d53650f131
--//对比完全能对上。
2.建立gdb脚本:
--//参考链接：http://blog.itpub.net/267265/viewspace-2665902/=>[20191127]表 full Hash Value的计算.txt
$ cat md5.gdb
set pagination off
break kggmd5Update
commands
printf "Length: %d
",$rdx
x/40xc $rsi
c
end
break kglComputeHash
commands
c
end
break kggmd5Process
commands
c
end
break kggmd5Finish
commands
c
end
3.测试分析:
--//首先使用DBMS_SHARED_POOL.MARKHOT标记。
SYS@book> exec dbms_shared_pool.markhot( hash=>"1431c45dbddbb9e74eaa74d53650f131", namespace=>0, global=>true);
PL/SQL procedure successfully completed.
--//以scott登录
--//session 1:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
58 49 54621 DEDICATED 54622 28 23 alter system kill session "58,49" immediate;
--//spid=54622
--//session 2:
$ gdb -p 54622 -x md5.gdb
--//session 1:
--//测试前可以先执行Select * from dept where deptno=10;注意S大写sql语句与原来不同。
Select * from dept where deptno=10
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
@ dpc "" ""
--//输出略，查询sql_id="7sqgfqarnwk8h",与原来的完成不同。
4.观察gdb的输出:
--//session 3:
SYS@book> select sql_id,sql_text,executions,length(sql_text),ora_hash(sql_text) from v$sqlarea where sql_text = "select * from dept where deptno=10" ;
SQL_ID SQL_TEXT EXECUTIONS LENGTH(SQL_TEXT) ORA_HASH(SQL_TEXT)
------------- ----------------------------------- ---------- ---------------- ------------------
7sqgfqarnwk8h select * from dept where deptno=10 3 34 156172166
4xamnunv51w9j select * from dept where deptno=10 8 34 156172166
--//sql_id=7sqgfqarnwk8h.
SELECT name
,hash_value
,full_hash_value
,namespace
,child_latch
,property hot_flag
,executions
,invalidations
FROM v$db_object_cache
WHERE name = "select * from dept where deptno=10";
NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- -------------
select * from dept where deptno=10 2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA 0 HOTCOPY11 3 0
select * from dept where deptno=10 2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA 18704 HOTCOPY11 3 0
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 0 HOT 8 0
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 HOT 8 0
--//可以发现新建立的sql语句FULL_HASH_VALUE=5196d0b7fe72e5ea7c59eeb2af4e4910,注意后面的HOT_FLAG="HOTCOPY11".
--//0x4910 = 18704
--//session 2:
(gdb) c
Continuing.
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 35
0x7fffff1de9d8: 115 "s" 101 "e" 108 "l" 101 "e" 99 "c" 116 "t" 32 " " 42 "*"
0x7fffff1de9e0: 32 " " 102 "f" 114 "r" 111 "o" 109 "m" 32 " " 100 "d" 101 "e"
0x7fffff1de9e8: 112 "p" 116 "t" 32 " " 119 "w" 104 "h" 101 "e" 114 "r" 101 "e"
0x7fffff1de9f0: 32 " " 100 "d" 101 "e" 112 "p" 116 "t" 110 "n" 111 "o" 61 "="
0x7fffff1de9f8: 49 "1" 48 "0" 0 "00" -13 "? 108 "l" 92 "\" 107 "k" -64 "?
--//拼接起来就是 select * from dept where deptno=10;
Breakpoint 4, 0x00000000097f0830 in kggmd5Finish ()
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 21
0xbefbe20
0xbefbe28
0xbefbe30
0xbefbe38
0xbefbe40
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 8
0x7fffff1dc910: 24 "30" 1 "01" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00"
0x7fffff1dc918: -40 "? -23 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
0x7fffff1dc920: 32 " " -51 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
0x7fffff1dc928: -40 "? -23 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
0x7fffff1dc930: -64 "? -55 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
Breakpoint 2, 0x000000000984457c in kglComputeHash ()
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 35
0x7fffff1de9d8: 115 "s" 101 "e" 108 "l" 101 "e" 99 "c" 116 "t" 32 " " 42 "*"
0x7fffff1de9e0: 32 " " 102 "f" 114 "r" 111 "o" 109 "m" 32 " " 100 "d" 101 "e"
0x7fffff1de9e8: 112 "p" 116 "t" 32 " " 119 "w" 104 "h" 101 "e" 114 "r" 101 "e"
0x7fffff1de9f0: 32 " " 100 "d" 101 "e" 112 "p" 116 "t" 110 "n" 111 "o" 61 "="
0x7fffff1de9f8: 49 "1" 48 "0" 0 "00" -13 "? 108 "l" 92 "\" 107 "k" -64 "?
--//拼接起来就是 select * from dept where deptno=10;
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 1
0x9e79fec <_2__STRING.98.0>: 46 "." 0 "00" 0 "00" 0 "00" 107 "k" 116 "t" 99 "c" 110 "n"
0x9e79ff4 <_2__STRING.377.0+4>: 46 "." 99 "c" 64 "@" 49 "1" 50 "2" 54 "6" 49 "1" 55 "7"
0x9e79ffc <_2__STRING.377.0+12>: 0 "00" 0 "00" 0 "00" 0 "00" 37 "%" 42 "*" 115 "s" 102 "f"
0x9e7a004 <_2__STRING.366.0+4>: 108 "l" 103 "g" 115 "s" 58 ":" 32 " " 48 "0" 120 "x" 37 "%"
0x9e7a00c <_2__STRING.366.0+12>: 48 "0" 56 "8" 120 "x" 32 " " 0 "00" 0 "00" 0 "00" 0 "00"
--//length=1 对应"."
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 2
0x7fffff1dc460: 49 "1" 49 "1" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00"
0x7fffff1dc468: 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00"
0x7fffff1dc470: -128 "200" -57 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
0x7fffff1dc478: 49 "1" 41 ")" -125 "203" 9 " " 0 "00" 0 "00" 0 "00" 0 "00"
0x7fffff1dc480: -80 "? -55 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
--//注意看这里,Length: 2，字符正好是"11".
Breakpoint 4, 0x00000000097f0830 in kggmd5Finish ()
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 18
0xbefbe20
0xbefbe28
0xbefbe30
0xbefbe38
0xbefbe40
Breakpoint 1, 0x00000000097f09a8 in kggmd5Update ()
Length: 8
0x7fffff1dc2a0: 48 "0" 1 "01" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00"
0x7fffff1dc2a8: 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00"
0x7fffff1dc2b0: 32 " " -53 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
0x7fffff1dc2b8: 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00" 0 "00"
0x7fffff1dc2c0: 96 "`" -61 "? 29 "35" -1 "" -1 "" 127 "177" 0 "00" 0 "00"
$ echo -e -n "select * from dept where deptno=10.11" | md5sum | sed "s/ -//" | xxd -r -p | od -t x4 | sed -n -e "s/^0000000 //" -e "s/ //gp"
5196d0b7fe72e5ea7c59eeb2af4e4910
--//^_^，正好对上。
SELECT name
,hash_value
,full_hash_value
,namespace
,child_latch
,property hot_flag
,executions
,invalidations
FROM v$db_object_cache
WHERE name = "select * from dept where deptno=10";
NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- -------------
select * from dept where deptno=10 2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA 0 HOTCOPY11 3 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from dept where deptno=10 2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA 18704 HOTCOPY11 3 0
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 0 HOT 8 0
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 HOT 8 0
--//也就是语句生成FULL_HASH_VALUE计算是原来sql语句(注意oracle会做一些格式化操作),后面加上 . v$db_object_cache.HOT_FLAG字段后面的数字。
--//讲的再通俗一点就是在原来基础上加入 . v$db_object_cache.HOT_FLAG字段后面的数字。
--//使用我写的脚本测试（注意要安装zsh)：
$ ./sql_id.zsh "select * from dept where deptno=10.11"
sql_text = select * from dept where deptno=10.11
full_hash_value(16) = 5196D0B7FE72E5EA7C59EEB2AF4E4910
hash_value(10) = 2941143312
sql_id(32) = 7sqgfqarnwk8h
sql_id(32) = 7sqgfqarnwk8h
--//sql_id=7sqgfqarnwk8h，也与前面查询v$sqlarea视图的结果一致。
4.继续探究，至于后面为什么加入11，估计与会话sid之类有关(猜测)。
--//退出gbd程序，继续分析。
--//首先我尝试退出再登录，我的测试环境sid会保持不变，serial#会发生变化。我执行select * from dept where deptno=10，查询
--//v$db_object_cache并没有生成新的sql_id.
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
58 49 54621 DEDICATED 54622 28 23 alter system kill session "58,49" immediate;
SCOTT@book> show parameter cpu_count
NAME TYPE VALUE
--------- ------- -----
cpu_count integer 24
SCOTT@book> select mod(58,24),mod(58,16) from dual ;
MOD(58,24) MOD(58,16)
---------- ----------
10 10
--//是否就是这个sid与某个参数取模 10+1，因为没有见过HOT_FLAG="HOTCOPY0"的情况.至少我没有见过。
--//建立新的会话:
--//session 4:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
30 115 55199 DEDICATED 55200 26 44 alter system kill session "30,115" immediate;
SCOTT@book> select mod(30,24),mod(30,16) from dual ;
MOD(30,24) MOD(30,16)
---------- ----------
6 14
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 3:
SELECT name
,hash_value
,full_hash_value
,namespace
,child_latch
,property hot_flag
,executions
,invalidations
FROM v$db_object_cache
WHERE name = "select * from dept where deptno=10";
SYS@book> /
NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- -------------
select * from dept where deptno=10 2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA 0 HOTCOPY11 3 0
select * from dept where deptno=10 2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA 18704 HOTCOPY11 3 0
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 0 HOT 8 0
select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 HOT 8 0
select * from dept where deptno=10 3106222595 642c74f9bf38538acec7e363b9253203 SQL AREA 0 HOTCOPY7 1 0
select * from dept where deptno=10 3106222595 642c74f9bf38538acec7e363b9253203 SQL AREA 78339 HOTCOPY7 1 0
6 rows selected.
--//生成新的HOT_FLAG=HOTCOPY7.正好等于是mod(sid,cpu_count)+1.
SYS@book> select sql_id,sql_text,executions,length(sql_text),ora_hash(sql_text) from v$sqlarea where sql_text = "select * from dept where deptno=10" ;
SQL_ID SQL_TEXT EXECUTIONS LENGTH(SQL_TEXT) ORA_HASH(SQL_TEXT)
------------- ------------------------------------------------------------ ---------- ---------------- ------------------
7sqgfqarnwk8h select * from dept where deptno=10 3 34 156172166
4xamnunv51w9j select * from dept where deptno=10 8 34 156172166
cxjz3cfwkach3 select * from dept where deptno=10 1 34 156172166
~~~~~~~~~~~~~
--//sql_id=cxjz3cfwkach3,HOT_FLAG="HOTCOPY7"
$ echo -e -n "select * from dept where deptno=10.7" | md5sum | sed "s/ -//" | xxd -r -p | od -t x4 | sed -n -e "s/^0000000 //" -e "s/ //gp"
642c74f9bf38538acec7e363b9253203
$ ./sql_id.zsh "select * from dept where deptno=10.7"
v1=642C74F9BF38538ACEC7E363B9253203 v2=CEC7E363B9253203 v3=3106222595
sql_text = select * from dept where deptno=10.7
full_hash_value(16) = 642C74F9BF38538ACEC7E363B9253203
hash_value(10) = 3106222595
./sql_id.zsh:1: number truncated after 15 digits: CEC7E363B9253203
sql_id(32) = 0tv3y6sxt4nt0
sql_id(32) = tv3y6sxt4nt0
--//脚本有错，先放一放.好像是V2变量里面第1个字符C不是数字，我记忆里好像是我使用服务器这个zsh版本的bug，晚上在家里测试看看。
--//使用另外的sql_id.sh脚本测试：
$ ./sql_id.sh "select * from dept where deptno=10.7"
v1=642C74F9BF38538ACEC7E363B9253203 v2=CEC7E363B9253203 v3=B9253203
sql_text = select * from dept where deptno=10.7
full_hash_value(16) = 642C74F9BF38538ACEC7E363B9253203
hash_value(10) = 3106222595
sql_id(32) = cxjz3cfwkach3
sql_id(32) = cxjz3cfwkach3
sql_id(32) = cxjz3cfwkach3
--//正好对上。大家可以再打开新的会话测试，可以确定sql语句就是在原来基础上加入. mod(sid,cpu_count)+1数字的字符串。
总结：
--//上班太安静，无聊探究这个问题。
--//sql语句使用使用DBMS_SHARED_POOL.MARKHOT标记热的sql_id时，full_hash_value和sql_id的计算,就是在原来sql语句的基础上加上
--// . mod(sid,cpu_count)+1数字的字符串。
--//使用DBMS_SHARED_POOL.MARKHOT 标记热的sql语句，实际上就是使用空间换时间的做法，打散开来。但是我上午的测试可能存在太多争用
--//反而使用它更慢，有机会测试更多会话的情况。
--//也许因为加入运算的字符串采用 . mod(sid,cpu_count)+1数字的字符串，也许存在太多的冲突，反而更慢。
--//附上sql_id.sh脚本，里面包含几个从full_hash_value计算sql_id的方法:
$ cat sql_id.sh
#! /bin/bash
# calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32).
odebug=${ODEBUG:-0}
#sql_text=${1}""
sql_text=${1}
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 "\
")
# v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d "\
")
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 "\
")
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 "\
" | awk "BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]} ", $1}" ))
res1=$(tr -d " " <<< $res1)
echo "sql_id(32) = $(printf "%013s" $res1)"
![[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql_id的计算.txt](/upload/202205/01/a1z2yujtpad.jpg)
