[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql_id的计算.txt

来源：这里教程网时间：2026-03-03 15:00:35 作者：

[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的。 --//更正：sql语句使用使用DBMS_SHARED_POOL.MARKHOT标记热的sql_id时，full_hash_value和sql_id的计算,就是在原来sql语句的基础 --//上加上 . mod(sid,cpu_count/2)+1数字的字符串。 --//如果设置隐含参数_kgl_hot_object_copies，变为加上 . mod(sid,_kgl_hot_object_copies)+1数字的字符串。 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\0' | 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\n",$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 '\000' -13 '? 108 'l' 92 '\\' 107 'k' -64 '? --//拼接起来就是 select * from dept where deptno=10\0; Breakpoint 4, 0x00000000097f0830 in kggmd5Finish () Breakpoint 1, 0x00000000097f09a8 in kggmd5Update () Length: 21 0xbefbe20 <kggmd5padding.0>: -128 '\200' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe28 <kggmd5padding.0+8>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe30 <kggmd5padding.0+16>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe38 <kggmd5padding.0+24>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe40 <kggmd5padding.0+32>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' Breakpoint 1, 0x00000000097f09a8 in kggmd5Update () Length: 8 0x7fffff1dc910: 24 '\030' 1 '\001' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0x7fffff1dc918: -40 '? -23 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' 0x7fffff1dc920: 32 ' ' -51 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' 0x7fffff1dc928: -40 '? -23 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' 0x7fffff1dc930: -64 '? -55 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' 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 '\000' -13 '? 108 'l' 92 '\\' 107 'k' -64 '? --//拼接起来就是 select * from dept where deptno=10\0; Breakpoint 1, 0x00000000097f09a8 in kggmd5Update () Length: 1 0x9e79fec <_2__STRING.98.0>: 46 '.' 0 '\000' 0 '\000' 0 '\000' 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 '\000' 0 '\000' 0 '\000' 0 '\000' 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 '\000' 0 '\000' 0 '\000' 0 '\000' --//length=1 对应'.' Breakpoint 1, 0x00000000097f09a8 in kggmd5Update () Length: 2 0x7fffff1dc460: 49 '1' 49 '1' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0x7fffff1dc468: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0x7fffff1dc470: -128 '\200' -57 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' 0x7fffff1dc478: 49 '1' 41 ')' -125 '\203' 9 '\t' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0x7fffff1dc480: -80 '? -55 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' --//注意看这里,Length: 2，字符正好是'11'. Breakpoint 4, 0x00000000097f0830 in kggmd5Finish () Breakpoint 1, 0x00000000097f09a8 in kggmd5Update () Length: 18 0xbefbe20 <kggmd5padding.0>: -128 '\200' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe28 <kggmd5padding.0+8>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe30 <kggmd5padding.0+16>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe38 <kggmd5padding.0+24>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0xbefbe40 <kggmd5padding.0+32>: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' Breakpoint 1, 0x00000000097f09a8 in kggmd5Update () Length: 8 0x7fffff1dc2a0: 48 '0' 1 '\001' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0x7fffff1dc2a8: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0x7fffff1dc2b0: 32 ' ' -53 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' 0x7fffff1dc2b8: 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0x7fffff1dc2c0: 96 '`' -61 '? 29 '\035' -1 '' -1 '' 127 '\177' 0 '\000' 0 '\000' $ echo -e -n 'select * from dept where deptno=10\0.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会做一些格式化操作),后面加上\0 . v$db_object_cache.HOT_FLAG字段后面的数字。 --//讲的再通俗一点就是在原来基础上加入 . v$db_object_cache.HOT_FLAG字段后面的数字。 --//使用我写的脚本测试（注意要安装zsh)： $ ./sql_id.zsh 'select * from dept where deptno=10\0.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\0.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\0.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\0.7' v1=642C74F9BF38538ACEC7E363B9253203 v2=CEC7E363B9253203 v3=B9253203 sql_text = select * from dept where deptno=10\0.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}'\0' 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 '\\\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}' )) res1=$(tr -d " " <<< $res1) echo "sql_id(32) = $(printf "%013s" $res1)"