大家好,我是考100分的小小码 ,祝大家学习进步,加薪顺利呀。今天说一说[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql_id的计算.txt,希望您对编程的造诣更进一步.
[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 “