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

[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"