ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20241128]21c下测试软解析人为修改cursor pin S的mutext值.txt

[20241128]21c下测试软解析人为修改cursor pin S的mutext值.txt

原创 Oracle 作者：lfree 时间：2024-11-30 19:27:57 0 删除 编辑

[20241128]21c下测试软解析人为修改cursor pin S的mutext值.txt

--//21c下测试软解析人为修改cursor pin S的mutext值会出现怎么情况。

1.环境：
SCOTT@book01p> @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.

2.建立测试环境：
SCOTT@book01p> create table deptxxx as select * from dept;
Table created.


$ uniq -c m2.sh
      1 #! /bin/bash
      1 sleep $(echo $1/500 | bc -l )
      1 sqlplus -s -l scott/book@book01p <<EOF  >/dev/null
      1 set feedback off
      1 select count(1) from deptxxx;
  20002 /
      1 quit
      1 EOF


3.测试：
$ /usr/local/bin/zzdate ; seq 10 | xargs -IQ -P 10  ./m2.sh Q ; /usr/local/bin/zzdate
trunc(sysdate)+08/24+39/1440+17/86400 -1732754357.243716630
trunc(sysdate)+08/24+39/1440+49/86400 1732754389.135336931
--//需要31.891620301秒。

SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+08/24+39/1440+17/86400 and ts<=trunc(sysdate)+08/24+39/1440+49/86400"
-- MutexProf by Tanel Poder (http://www.tanelpoder.com)
-- Showing profile of top 20 sleeps...
-- column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp
--               req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr

SUM_SLEEPS      GETS_DIFF MUTEX_TYPE             IDN       HASH GET_LOCATION                      mutex_addr           OBJECT_NAME
---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- --------------------------------------------------------------------------------
         3         322926 Cursor Pin      2945576271 2945576271 kksfbc [KKSCHLPIN1]               0000000060E01730     select count(1) from deptxxx
         2          92252 Cursor Pin      2945576271 2945576271 kksLockDelete [KKSCHLPIN6]        0000000060E01730     select count(1) from deptxxx
--//主要目的获得mutex_addr=0000000060E01730

SYS@book> @ opeek 0000000060E01730 24 0
[060E01730, 060E01748) = 00000000 00000000 00061AC6 00000009 AF91ED4F 00000000
--//前面8位是mutex的值。前4位为持有mutex的数量，后4位为阻塞的sid，注意ashtop输出p2raw参数大小头问题显示是反过来的。

$ mod_addr.sh 0000000060E01730 0 100
0x60e01730:     0x00000100

SYS@book> @ opeek 0000000060E01730 24 0
[060E01730, 060E01748) = 00000100 00000000 00061AC6 00000009 AF91ED4F 00000000

--//session 1:
SCOTT@book01p> @ spid
==============================
SID                           : 404
SERIAL#                       : 11390
PROCESS                       : 3534
SERVER                        : DEDICATED
SPID                          : 3536
PID                           : 63
P_SERIAL#                     : 2
KILL_COMMAND                  : alter system kill session '404,11390' immediate;
PL/SQL procedure successfully completed.

--//window 1:
$ strace -f -p 3536  -y -Ttt 2>&1 | tee /tmp/test.txt

--//session 1:
SCOTT@book01p> select count(1) from deptxxx;

  COUNT(1)
----------
         4
--//完全不阻塞。

4.继续测试：
$ mod_addr.sh 0000000060E01730 4 101
0x60e01734:     0x00000101

SYS@book> @ opeek 0000000060E01730 24 0
[060E01730, 060E01748) = 00000100 00000101 00061AC8 00000009 AF91ED4F 00000000

--//session 1，退出重新登录：
SCOTT@book01p> @ spid
==============================
SID                           : 404
SERIAL#                       : 27532
PROCESS                       : 3747
SERVER                        : DEDICATED
SPID                          : 3749
PID                           : 63
P_SERIAL#                     : 3
KILL_COMMAND                  : alter system kill session '404,27532' immediate;
PL/SQL procedure successfully completed.

--//window 1:
$ strace -f -p 3749  -y -Ttt 2>&1 | tee /tmp/test.txt

--//session 1:
SCOTT@book01p> select count(1) from deptxxx;
--//挂起!!

--//window 2:
$ mod_addr.sh 0000000060E01730 4 0
0x60e01734:     0x00000000

SYS@book> @ opeek 0000000060E01730 24 0
[060E01730, 060E01748) = 00000100 00000000 00061ACA 00000E22 AF91ED4F 00000000

--//session 1:
SCOTT@book01p> select count(1) from deptxxx;
COUNT(1)
----------
         4

$ awk '{print $2}' /tmp/test.txt | uniq -c
      1 3749
      1 read(16<socket:[23424]>,
      2 getrusage(0x1
    247 select(0,
      1 getrusage(0x1
    361 select(0,
      1 getrusage(0x1
    338 select(0,
      1 getrusage(0x1
    357 select(0,
      1 getrusage(0x1
    709 select(0,
      1 getrusage(0x1
    404 select(0,
      1 getrusage(0x1
    833 select(0,
      1 getrusage(0x1
    360 select(0,
      1 getrusage(0x1
      1 write(16<socket:[23424]>,
      1 read(16<socket:[23424]>,
      1 getrusage(0x1
      1 write(16<socket:[23424]>,
      1 read(16<socket:[23424]>,

 $ grep getrusage /tmp/test.txt
08:48:19.496961 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 32003}, ru_stime={0, 11734}, ...}) = 0 <0.000023>
08:48:19.497149 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 32060}, ru_stime={0, 11755}, ...}) = 0 <0.000026>
08:48:20.778744 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 38627}, ru_stime={0, 18177}, ...}) = 0 <0.000013>
08:48:22.788879 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 48600}, ru_stime={0, 30375}, ...}) = 0 <0.000151>
08:48:24.797299 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 56541}, ru_stime={0, 37266}, ...}) = 0 <0.000017>
08:48:26.801396 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 66621}, ru_stime={0, 46776}, ...}) = 0 <0.000191>
08:48:28.802363 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 83307}, ru_stime={0, 72304}, ...}) = 0 <0.000014>
08:48:30.804658 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 85094}, ru_stime={0, 94211}, ...}) = 0 <0.000029>
08:48:32.804952 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 100286}, ru_stime={0, 127348}, ...}) = 0 <0.000014>
08:48:33.471489 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 102827}, ru_stime={0, 134961}, ...}) = 0 <0.000008>
08:48:33.472671 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 102973}, ru_stime={0, 135152}, ...}) = 0 <0.000007>
--//间隔2秒调用getrusage。

$ grep select  /tmp/test.txt | head -10
08:48:19.497351 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001294>
08:48:19.498783 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.003449>
08:48:19.502327 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001882>
08:48:19.504299 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.002276>
08:48:19.506666 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.002341>
08:48:19.509249 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001735>
08:48:19.511126 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.002005>
08:48:19.513223 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001741>
08:48:19.515055 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001938>
08:48:19.517085 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001795>
--//select  调用休眠时间是1毫秒。

5.小结：
--//说明软解析是持有数量mutext值前4位，不阻塞sql语句执行。

6.mod_addr.sh 代码如下：
$ cat mod_addr.sh
#/bin/bash
# modify address value
# arg1=address arg2=offset (default 0) arg3=value (default 0)

offset=${2:-0}
value=${3:-0}
gdb -q -batch -p $(pgrep -f ora_mmon_${ORACLE_SID}) -ex "set *(  int *)(0x${1}+0x${offset})=0x${value}"  -ex "x /wx 0x${1}+0x${offset}"  -ex "quit" |  grep "^0x" | grep -v "^0x0"

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3053894/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20241127]奇葩的问题.txt
下一篇： [20241129]21c下测试软软解析改cursor pin S的mutext gets的变化.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969299

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593