[20241124]测试软软解析人为修改cursor pin S的mutext值.txt
--//测试软软解析人为修改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.建立测试环境：
create table job_times (sid number, time_ela number,method varchar2(20));
drop table t purge ;
create table t as select rownum id ,'test' pad from dual connect by level<=5e5;
create unique index pk_t on t(id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
$ cat m9.txt
set verify off
variable v_method varchar2(20)
exec :v_method := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:v_method) ;
commit ;
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t where id = :j ' INTO l_count USING i;
END LOOP;
END;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method= :v_method;
commit;
quit
3.测试：
--//delete from JOB_TIMES ;
--//commit ;
--//删除前面的测试结果。
$ zzdate;seq 20 | xargs -P 20 -IQ sqlplus -s -l scott/book@book01p @m9.txt 1e4 AAAA > /dev/null;zzdate
trunc(sysdate)+15/24+42/1440+00/86400
trunc(sysdate)+15/24+42/1440+03/86400
SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+15/24+42/1440+00/86400 and ts<=trunc(sysdate)+15/24+42/1440+03/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
---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- --------------------------------------------------------------------------------
25 258891 Cursor Pin 321664448 321664448 kksLockDelete [KKSCHLPIN6] 00000000611FCD58 Select /*+ AAAA */ count(*) from t where id = :j
3 123257 Cursor Pin 321664448 321664448 kksfbc [KKSCHLFSP2] 00000000611FCD58 Select /*+ AAAA */ count(*) from t where id = :j
--//主要目的获得mutex_addr=00000000611FCD58。
SYS@book> @ opeek 00000000611FCD58 24 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3440_0001.trc
[0611FCD58, 0611FCD70) = 00000000 00000000 00061A98 000000C1 132C35C0 00000000
--//前面8位是mutex的值。前4位为持有mutex的数量，后4位为阻塞的sid，注意ashtop输出p2raw参数大小头问题显示是反过来的。
$ mod_addr.sh 00000000611FCD58 0 100
0x611fcd58: 0x00000100
SYS@book> @ opeek 00000000611FCD58 24 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3440_0002.trc
[0611FCD58, 0611FCD70) = 00000100 00000000 00061A99 000000C1 132C35C0 00000000
--//session 1:
SCOTT@book01p> @ spid
==============================
SID : 145
SERIAL# : 5816
PROCESS : 4007
SERVER : DEDICATED
SPID : 4009
PID : 45
P_SERIAL# : 7
KILL_COMMAND : alter system kill session '145,5816' immediate;
PL/SQL procedure successfully completed.
--//window 1:
$ strace -f -p 4009 -y -Ttt 2>&1 | tee /tmp/test.txt
--//session 1:
SCOTT@book01p> @ m9.txt 1 AAAA
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
PL/SQL procedure successfully completed.
1 row updated.
Commit complete.
4.测试2：
--//修改后4位。
$ mod_addr.sh 00000000611FCD58 4 1
0x611fcd5c: 0x00000001
SYS@book> @ opeek 00000000611FCD58 24 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3440_0003.trc
[0611FCD58, 0611FCD70) = 00000000 00000001 00061AAD 00004CA5 132C35C0 00000000
--//window 1:
$ strace -f -p 4557 -y -Ttt 2>&1 | tee /tmp/test2.txt
--//session 1:
SCOTT@book01p> @ m9.txt 1 AAAA
--//挂起!!
SYS@book> @ ashtop event,p1raw,p1,p2raw 1=1 trunc(sysdate)+16/24+04/1440+18/86400 sysdate
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P1 P2RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ----------------- ---------- ----------------- ------------------- ------------------- ---------- -------- -----------
18 .1 43% | cursor: pin S 00000000132C35C0 321664448 0000000100000000 2024-11-24 16:06:29 2024-11-24 16:06:46 1 18 18
12 .1 29% | 100 2024-11-24 16:04:36 2024-11-24 16:07:12 1 11 11
9 .0 21% | 3 2024-11-24 16:04:36 2024-11-24 16:05:56 1 9 9
2 .0 5% | 1 2024-11-24 16:05:04 2024-11-24 16:05:25 1 2 2
1 .0 2% | 0 2024-11-24 16:05:58 2024-11-24 16:05:58 1 1 1
SYS@book> @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+16/24+04/1440+18/86400 and maddr='00000000611FCD58'"
-- 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
---------- -------------- --------------- ---------- ---------- --------------------------------- -------------------- -------------------------------------------------
5276 Cursor Pin 321664448 321664448 kkslce [KKSCHLPIN2] 00000000611FCD58 Select /*+ AAAA */ count(*) from t where id = :j
$ egrep "sched_yield|getrusage|select" /tmp/test2.txt | awk '{print $2}'| uniq -c| head -60
187 select(0,
1 getrusage(0x1
652 select(0,
1 getrusage(0x1
400 select(0,
1 getrusage(0x1
754 select(0,
1 getrusage(0x1
321 select(0,
1 getrusage(0x1
486 select(0,
1 getrusage(0x1
528 select(0,
1 getrusage(0x1
963 select(0,
1 getrusage(0x1
290 select(0,
9 getrusage(0x1
$ egrep "sched_yield|getrusage|select" /tmp/test2.txt | grep getrusage
16:06:32.546801 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 57279}, ru_stime={0, 31503}, ...}) = 0 <0.000013>
16:06:34.553095 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 73031}, ru_stime={0, 50457}, ...}) = 0 <0.000022>
16:06:36.558399 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 79758}, ru_stime={0, 61748}, ...}) = 0 <0.000018>
16:06:38.561963 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 109984}, ru_stime={0, 77131}, ...}) = 0 <0.000054>
16:06:40.563244 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 123649}, ru_stime={0, 81969}, ...}) = 0 <0.000009>
16:06:42.573588 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 134842}, ru_stime={0, 95343}, ...}) = 0 <0.000013>
16:06:44.577038 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 153945}, ru_stime={0, 108586}, ...}) = 0 <0.000047>
16:06:46.578335 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 172698}, ru_stime={0, 127025}, ...}) = 0 <0.000054>
16:06:46.970193 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 178005}, ru_stime={0, 129856}, ...}) = 0 <0.000013>
16:06:46.971183 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 178220}, ru_stime={0, 130013}, ...}) = 0 <0.000012>
16:06:46.971384 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 178315}, ru_stime={0, 130082}, ...}) = 0 <0.000042>
16:06:46.971767 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 178492}, ru_stime={0, 130211}, ...}) = 0 <0.000023>
16:06:46.972097 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 178530}, ru_stime={0, 130239}, ...}) = 0 <0.000012>
16:06:46.972316 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 178609}, ru_stime={0, 130297}, ...}) = 0 <0.000011>
16:06:46.973629 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 179287}, ru_stime={0, 130297}, ...}) = 0 <0.000017>
16:06:46.977169 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 179287}, ru_stime={0, 130297}, ...}) = 0 <0.000011>
16:06:46.978066 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 179750}, ru_stime={0, 130297}, ...}) = 0 <0.000031>
--//调用getrusage 2秒间隔。
$ egrep "sched_yield|getrusage|select" /tmp/test2.txt | grep select | head -10
16:06:31.865221 select(0, [], [], [], {0, 167}) = 0 (Timeout) <0.001087>
16:06:31.866541 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001832>
16:06:31.868463 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001889>
16:06:31.870420 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001885>
16:06:31.872362 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001827>
16:06:31.874246 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001098>
16:06:31.875419 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001811>
16:06:31.877299 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.002742>
16:06:31.880132 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.002035>
16:06:31.882230 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001717>
--//休眠的时间是1毫秒。
5.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"