[20250117]记录下21c下使用gdb跟踪逻辑读遇到的问题.txt
–//在21c下使用gdb跟踪逻辑读遇到的问题，困扰好几天，做一个记录。
–//首先我以前写过1个gdb脚本跟踪逻辑读在11g下，使用遇到一些问题，发现21c下没有使用kteinpscan，kdifxs函数。
–//我先注解这部分内容，测试看看。
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.问题：
–//session 1:
SCOTT@book01p> @ spid
==============================
SID : 288
SERIAL# : 10960
PROCESS : 3443
SERVER : DEDICATED
SPID : 3445
PID : 42
P_SERIAL# : 4
KILL_COMMAND : alter system kill session ‘288,10960’ immediate;
PL/SQL procedure successfully completed.
–//window 1:
$ rlgdb -f -p 3445 -x fetch21.gdb
…
/usr/src/debug/glibc-2.17-c758a686/sysdeps/unix/syscall-template.S:81:3374:beg:0x7ff024211480
Breakpoint 1 at 0x6f55db0
Breakpoint 2 at 0x14dd2840
Breakpoint 3 at 0x150bacb0
Breakpoint 4 at 0x14dd4740
Breakpoint 5 at 0x150a2220
–//session 1:
SCOTT@book01p> select * from emp ;
–//window 1:
(gdb) c
Continuing.
2025/01/17 09:23:14.578916428 :kteinpscan_int1 0x404728
2025/01/17 09:23:14.583312308 :kteinmap 0x404728
2025/01/17 09:23:14.588318301 :kdst_fetch 0x404729
2025/01/17 09:23:14.592462416 :kdst_fetch 0x40472a
2025/01/17 09:23:14.596911200 :kdst_fetch 0x40472b
2025/01/17 09:23:14.601128379 :kdst_fetch 0x40472c
2025/01/17 09:23:14.605677445 :kdst_fetch 0x40472d
2025/01/17 09:23:14.610015416 :kdst_fetch 0x40472e
2025/01/17 09:23:14.614121148 :kdst_fetch 0x40472f
2025/01/17 09:23:14.640008858 :kdsgrp 0x407d2c
2025/01/17 09:23:14.644933305 :kdsgrp 0x40717c
2025/01/17 09:23:14.650308734 :kdsgrp 0x406cff
2025/01/17 09:23:14.656093289 :kdsgrp 0x408b19
2025/01/17 09:23:14.673224752 :kteinpscan_int1 0x400ea8
2025/01/17 09:23:14.677325767 :kteinmap 0x400ea8
2025/01/17 09:23:14.682954100 :kdst_fetch 0x400ea9
2025/01/17 09:23:14.687107439 :kdst_fetch 0x400eaa
2025/01/17 09:23:14.691293112 :kdst_fetch 0x400eab
2025/01/17 09:23:14.695392614 :kdst_fetch 0x400eac
2025/01/17 09:23:14.699351668 :kdst_fetch 0x400ead
2025/01/17 09:23:14.703374736 :kdst_fetch 0x400eae
2025/01/17 09:23:14.707523855 :kdst_fetch 0x400eaf
2025/01/17 09:23:14.711629353 :kteinpscan_int1 0x404728
2025/01/17 09:23:14.715429348 :kteinmap 0x404728
2025/01/17 09:23:14.720132701 :kdst_fetch 0x404729
2025/01/17 09:23:14.723999463 :kdst_fetch 0x40472a
2025/01/17 09:23:14.728088980 :kdst_fetch 0x40472b
2025/01/17 09:23:14.732002462 :kdst_fetch 0x40472c
2025/01/17 09:23:14.735930066 :kdst_fetch 0x40472d
2025/01/17 09:23:14.739869374 :kdst_fetch 0x40472e
2025/01/17 09:23:14.743964729 :kdst_fetch 0x40472f
2025/01/17 09:23:14.748303359 :kdsgrp 0x400662
2025/01/17 09:23:14.752879915 :kdsgrp 0x4005cb
2025/01/17 09:23:14.758176698 :kdsgrp 0x404b0c
2025/01/17 09:23:14.777982961 :kdsgrp 0x100a253
2025/01/17 09:23:14.801122919 :kteinpscan_int1 0x3000092
2025/01/17 09:23:14.816837781 :kteinmap 0x3000092
–//注：在21c下没有kteinpscan函数，发现有kteinpscan_int1，使用它代替。
(gdb) b kteinpscan
Function “kteinpscan” not defined.
Make breakpoint pending on future shared library load? (y or [n]) n
(gdb) b kdifxs
Function “kdifxs” not defined.
Make breakpoint pending on future shared library load? (y or [n]) n
–//上面的看不见kdst_fetch函数调用，感觉有点奇怪。第1次执行看见许多递归调用很正常，仔细查看前面存在kdst_fetch的调用。
–//开始我的感觉前面的调用的对象dba地址是system文件下的dba地址，而其他users表空间的数据文件地址不记录，为什么？
–//我尝试设置alter system set “_trace_pin_time”=true scope=spfile;，重启数据库没用。而且我发现pin信息并不会记录在跟踪文
–//件里面，在cdb以及pdb下问题一样，也就是设置”_trace_pin_time”=true在21c下没用。另外gdb跟踪脚本仅仅对于数据文件1才会出
–//现kdst_fetch，不理解为什么出现这样的情况。
–//再走了许多弯路以后，最终发现打开10046跟踪就可以看到kdst_fetch的函数调用，不理解为什么出现这样的情况？
–//session 1:
SCOTT@book01p> @ 10046on 12
Session altered.
SCOTT@book01p> select * from emp ;
–//window 1:
2025/01/17 09:32:20.999570936 :kteinpscan_int1 0x3000092
2025/01/17 09:32:21.004062660 :kteinmap 0x3000092
2025/01/17 09:32:21.008996090 :kdst_fetch 0x3000093
2025/01/17 09:32:21.013613286 :kdst_fetch 0x3000094
2025/01/17 09:32:21.017875027 :kdst_fetch 0x3000095
2025/01/17 09:32:21.025932281 :kdst_fetch 0x3000096
2025/01/17 09:32:21.030022441 :kdst_fetch 0x3000096
2025/01/17 09:32:21.034238364 :kdst_fetch 0x3000097
3.继续其他测试：
–//session 1:
–//INDEX FAST FULL SCAN
SCOTT@book01p> select /*+ index_ffs(emp) */ count(*) from emp;
COUNT(*)
———-
14
–//window 1:
2025/01/17 09:35:57.915491960 :kteinpscan_int1 0x300009a
2025/01/17 09:35:57.920081950 :kteinmap 0x300009a
2025/01/17 09:35:57.924230311 :kteinpscan_int1 0x300009a
2025/01/17 09:35:57.928508887 :kteinmap 0x300009a
–//避免递归，你可以执行多次，少量1个数据块。
–//session 1:
–// TABLE ACCESS BY USER ROWID
SCOTT@book01p> select * from emp where rowid=’AAASmhAAMAAAACWAAU’;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
–//window 1:
2025/01/17 09:37:58.553438472 :kdsgrp 0x3000096
–//session 1:
–//INDEX RANGE SCAN
SCOTT@book> select * from emp where empno between 7788 and 7900;
–//window 1:
2025/01/17 09:40:03.876476030 :kdsgrp 0x3000096
–//缺少了kdifxs函数，导致我写的gdb脚本记录kdsgrp仅仅出现1次。
$ strings $(which oracle) | grep -i kdifxs| head -3
kdifxs1
kmdrdbmsKdifxs
kdifxs0
–//乱猜测一下，现在使用的是kdifxs0。
–//session 1:
–//INDEX RANGE SCAN
SCOTT@book> select * from emp where empno between 7788 and 7900;
–//window 1:
2025/01/17 15:52:14.174940268 :kdifxs0 0x300009b
2025/01/17 15:52:14.180038004 :kdsgrp 0x3000096
2025/01/17 15:52:14.188875295 :kdifxs0 0x300009b
2025/01/17 15:52:14.193290174 :kdsgrp 0x3000096
–//修改gdb脚本如下，记住要使用打开10046跟踪，虽然自己不理解为什么，为什么不打开10046的情况下跟踪不到kdst_fetch函数
–//调用。
$ grep -v “^#” fetch21.gdb
set $a=0x0
set $b=0x0
break __read_nocancel
set pagination off
commands
silent
set $a=0x0
set $b=0x0
continue
end
break kteinpscan_int1
set pagination off
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kteinpscan_int1 0x%x\n”, *($rdi+4)
continue
end
break kdst_fetch if *($rsi+28) != 0x0
set pagination off
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdst_fetch 0x%x\n”, *($rsi+28)
continue
end
break kteinmap
set pagination off
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kteinmap 0x%x\n”, *($rdi+4)
continue
end
break kdifxs0 if *($rdi+28) != $a
set pagination off
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdifxs0 0x%x\n”,*($rdi+28)
set $a= *($rdi+28)
continue
end
break kdsgrp if *($rdi+28) != $b
set pagination off
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdsgrp 0x%x\n”, *($rdi+28)
set $b= *($rdi+28)
continue
end
–//简单说明一下：11g下初始化变量a,b时，设置断点在snttread，21c下修改__read_nocance。
–//执行gdb 跟踪会话，执行如下很容易确定：
(gdb) bt 3
#0 0x00007fd032ab7480 in __read_nocancel () at ../sysdeps/unix/syscall-template.S:81
#1 0x0000000015415605 in read ()
#2 0x000000001540f360 in snttread ()
(More stack frames follow…)
4.如果执行INDEX FULL SCAN呢？
–//session 1:
–//INDEX FULL SCAN
SCOTT@book01p> select /*+ index_ffs(emp) */ count(*) from emp;
–//window 1:
2025/01/17 15:56:51.284515751 :kdifxs0 0x300009b
–//仅仅看到1次。
SCOTT@book01p> @ dpc ” ” ”
PLAN_TABLE_OUTPUT
————————————-
SQL_ID 72rq2sakdcx3h, child number 1
————————————-
select count (*) from emp
Plan hash value: 2937609675
————————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 1 | 12 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 |
————————————————————————————————————-
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / “EMP”@”SEL$1”
–//逻辑读1次
SCOTT@book01p> select /*+ index_ffs(emp) */ count (*) from emp ;
COUNT(*)
———-
14
SCOTT@book01p> @ dpc ” ” ”
PLAN_TABLE_OUTPUT
————————————-
SQL_ID 1fytxx868arn9, child number 0
————————————-
select /*+ index_ffs(emp) */ count (*) from emp
Plan hash value: 1006289799
——————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
——————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| | 1 |00:00:00.02 | 4 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.02 | 4 |
| 2 | INDEX FAST FULL SCAN| PK_EMP | 1 | 12 | 2 (0)| 00:00:01 | 14 |00:00:00.02 | 4 |
——————————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / “EMP”@”SEL$1”
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
—————————————————————————
2 – SEL$1 / “EMP”@”SEL$1”
– index_ffs(emp)
–//逻辑读4次.注：在11g下测试3个逻辑读。
–//window 1:
2025/01/17 15:58:40.540827345 :kteinpscan_int1 0x300009a
2025/01/17 15:58:40.545174023 :kteinmap 0x300009a
2025/01/17 15:58:40.549210158 :kteinpscan_int1 0x300009a
2025/01/17 15:58:40.553239713 :kteinmap 0x300009a
–//少记录0x300009b，不知道调用什么函数。调用kdirfrs。
–//顺便贴上当时在11g的测试：
–//INDEX FAST FULL SCAN
select /*+ index_ffs(emp) */ count(*) from emp;
–//跟踪文件:
*** 2021-02-20 15:36:45.413
pin ktewh26: kteinpscan dba 0x100009a:4 time 2708712237
pin kdiwh100: kdircys dba 0x100009b:1 time 2708717602
~~~~~~~~~~~~~//没有kdircys函数。
–//gdb显示:
2021/02/20 15:36:45.400717820 :kteinpscan 0x100009a
2021/02/20 15:36:45.406515456 :kteinmap 0x100009a
2021/02/20 15:36:45.411622200 :kteinpscan 0x100009a
2021/02/20 15:36:45.416993772 :kteinmap 0x100009a
5.如果执行INDEX UNIQUE SCAN呢？
–//session 1:
SCOTT@book01p> select * from emp where empno = 7788 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
–//window 1：
2025/01/17 16:30:48.505308120 :kdsgrp 0x3000096
–//看不到索引块。做了一下跟踪，好像调用kdifkc函数。
–//最终修改如下，记录开始10046跟踪才能看到kdst_fetch 的调用。
$ grep -v “^#” fetch21.gdb | cat -s
set pagination off
set $a=0x0
set $b=0x0
set $c=0x0
set $d=0x0
break __read_nocancel
commands
silent
set $a=0x0
set $b=0x0
set $c=0x0
set $d=0x0
continue
end
break kteinpscan_int1
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kteinpscan_int1 0x%x\n”, *($rdi+4)
continue
end
break kdst_fetch if *($rsi+28) != 0x0
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdst_fetch 0x%x\n”, *($rsi+28)
continue
end
break kteinmap
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kteinmap 0x%x\n”, *($rdi+4)
continue
end
break kdifxs0 if *($rdi+28) != $a
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdifxs0 0x%x\n”,*($rdi+28)
set $a= *($rdi+28)
continue
end
break kdirfrs if *($rdi+28) != $c
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdirfrs 0x%x\n”,*($rdi+28)
set $c= *($rdi+28)
continue
end
break kdifkc if *($rsi+36) != $d
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdifkc 0x%x\n”,*($rsi+36)
set $d= *($rsi+36)
continue
end
break kdsgrp if *($rdi+28) != $b
commands
silent
shell echo -n $( date +”%Y/%m/%d %T.%N : “)
printf “kdsgrp 0x%x\n”, *($rdi+28)
set $b= *($rdi+28)
continue
end
–//不能作为逻辑读数量依据，仅仅作为参考。
来源链接：https://www.cnblogs.com/lfree/p/18677706
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
暂无评论内容