1、使用场景

在日常的运维工作中，当数据库发生意外宕机后，我们一般会使用dmrdc工具，扫描堆栈文件得出原始的sql语句，但面对比较大型的系统时，尝试的堆栈文件会比较大，扫描会消耗相当的时间，同时还需要从扫描出的多条sql中通过线程号找到对应sql，下面介绍一种从堆栈文件中直接查出问题sql的方法供参考

2、操作步骤

1、调试堆栈文件

gdb /home/dmdbms/dmserver xxx.core

2、执行 bt 命令。

3、f * 进入对应行数的栈帧，选择这两个方法 ntsk_process_exec_low 、ntsk_process_exec 对应行数的栈帧。

4、通过以下命令调整设置，否则 sql 太长无法显示完整。

Copy
(gdb) set print element 0 

5、获取完整 sql。有3 种方法可以获取到完整的 SQL。

从 sess 里获取完整 sql。

Copy
(gdb) p sess ->sqls

从 stmt 里获取完整 sql。

Copy
(gdb) p stmt ->sql_str

从 pln 里获取完整 sql。

Copy
(gdb) p pln->sqlstr

例如：

执行 bt 命令。

Copy
(gdb)bt
#0  assert_fun (env=<optimized out>) at /home/dmops/build/svns/1666183702277/mgr/nsvr.c:3872
#1  0x0000000001aa6f83 in sigterm_handler (signum=11) at /home/dmops/build/svns/1666183702277/mgr/nsvr.c:3921
#2  <signal handler called>
#3  bifun_aav_s_no_null_opt (vm=0x9ecb00f8, tmp_col_no=4, bifun_real=<optimized out>) at /home/dmops/build/svns/1666183702277/pub/bifun.c:7887
#4  0x0000000000e22be6 in binv_internal_exec (vm=0x9ecb00f8, tmp_col_no=4, bifun_id=<optimized out>) at /home/dmops/build/svns/1666183702277/bop/binv.c:53
#5  0x000000000159db2f in vm_nexp_run_low (vm=<optimized out>, cmds=<optimized out>, cmd_len=<optimized out>, ign_cancel=1)
    at /home/dmops/build/svns/1666183702277/op/vm.c:5693
#6  0x0000000001536cfb in prjt2_exec_process_bdta (prjt2_vm=0x9d5c2be8) at /home/dmops/build/svns/1666183702277/op/prjt2.c:712
#7  0x000000000153743c in prjt2_exec_after_fetch (prjt2_vm=0x9d5c2be8) at /home/dmops/build/svns/1666183702277/op/prjt2.c:968
#8  0x00000000015375c0 in prjt2_exec (prjt2_vm=0x9d5c2be8) at /home/dmops/build/svns/1666183702277/op/prjt2.c:1011
#9  0x000000000159f79d in vm_run_low (vm=0x9ecb00f8) at /home/dmops/build/svns/1666183702277/op/vm.c:5177
#10 0x000000000159fbb1 in vm_run (vm=<optimized out>) at /home/dmops/build/svns/1666183702277/op/vm.c:5252
#11 0x00000000015a0d83 in vm_run_pln_low (env=0x7f19b23fec20, stmt=0x9ecaf8f8, pln=0x9be00860, ret_ident_flag=<optimized out>, n_ret_col=<optimized out>, 
    ret_col_ident=<optimized out>, err_desc=0x0) at /home/dmops/build/svns/1666183702277/op/vm.c:9325
#12 0x00000000015a0f58 in vm_run_pln (env=<optimized out>, stmt=<optimized out>, pln=<optimized out>, ret_ident_flag=<optimized out>, n_ret_col=<optimized out>, 
    ret_col_ident=<optimized out>) at /home/dmops/build/svns/1666183702277/op/vm.c:9407
#13 0x0000000001adc258 in ntsk_process_exec_low (env=0x7f19b23fec20, stmt=0x9ecaf8f8, pln=0x9be00860, ret_ident_flag=0 '\000', n_ret_col=0, ret_col_ident=0x0, 
    dlck_reprepare=0x7f19b23fe3bc) at /home/dmops/build/svns/1666183702277/mgr/ntsk.c:10684
#14 0x0000000001ae4b14 in ntsk_process_exec (env=0x7f19b23fec20, sess=0x9ec8a0f8, msg_in=0x9d5287e8 "\002") at /home/dmops/build/svns/1666183702277/mgr/ntsk.c:13172
#15 0x0000000001af42e6 in ntsk_process_cop (env=0x7f19b23fec20, task=<optimized out>) at /home/dmops/build/svns/1666183702277/mgr/ntsk.c:17681
#16 0x00000000019a8d29 in uthr_db_main_for_sess (sess2=0x9ec8a0f8) at /home/dmops/build/svns/1666183702277/knl/uthr.c:1143
#17 0x00007f19cb37add5 in start_thread () from /usr/lib64/libpthread.so.0
#18 0x00007f19ca896ead in clone () from /usr/lib64/libc.so.6

选择这两个方法 ntsk_process_exec_low 、ntsk_process_exec 其中一个对应行数的栈帧即可，一个为 13 一个为 14。

Copy
#13 0x0000000001adc258 in ntsk_process_exec_low (env=0x7f19b23fec20, stmt=0x9ecaf8f8, pln=0x9be00860, ret_ident_flag=0 '\000', n_ret_col=0, ret_col_ident=0x0, 
    dlck_reprepare=0x7f19b23fe3bc) at /home/dmops/build/svns/1666183702277/mgr/ntsk.c:10684
#14 0x0000000001ae4b14 in ntsk_process_exec (env=0x7f19b23fec20, sess=0x9ec8a0f8, msg_in=0x9d5287e8 "\002") at /home/dmops/build/svns/1666183702277/mgr/ntsk.c:13172

调整设置。

Copy
(gdb) set print element 0 

获取完整 sql。
选择对应行数的栈帧为 14 的。

Copy
(gdb) f 14
#14 0x0000000001ae4b14 in ntsk_process_exec (env=0x7f19b23fec20, sess=0x9ec8a0f8, msg_in=0x9d5287e8 "\002") at /home/dmops/build/svns/1666183702277/mgr/ntsk.c:13172
13172	in /home/dmops/build/svns/1666183702277/mgr/ntsk.c

从 sess 里获取完整 sql。

Copy
(gdb) p sess ->sqls 
$8 = (schar *) 0x9ec8a2c4 "SELECT NVL(SUM(D1),0)||'' AS D1,\nNVL(SUM(D3),0)||'' AS D2,\nNVL(SUM(D2),0)||'' AS D3\nFROM (\nSELECT CASE WHEN C1 >= SYSDATE - 10 AND C1 < SYSDATE THEN COUNT(1)||'' ELSE 0||'' END AS D1,\nCASE WHEN C1 >= SYSDATE - 20 AND C1 < SYSDATE THEN COUNT(1)||'' ELSE 0||'' END AS D2,\nCASE WHEN C1 >= SYSDATE - 30 AND C1 < SYSDATE THEN COUNT(1)||'' ELSE 0||'' END AS D3\nFROM T1  where c2= ? and c3=? AND C4>=?\nGROUP BY T1.ID,C1\n)"

选择对应行数的栈帧为 13 的。

Copy
(gdb) f 13
#13 0x0000000001adc258 in ntsk_process_exec_low (env=0x7f19b23fec20, stmt=0x9ecaf8f8, pln=0x9be00860, ret_ident_flag=0 '\000', n_ret_col=0, ret_col_ident=0x0, 
    dlck_reprepare=0x7f19b23fe3bc) at /home/dmops/build/svns/1666183702277/mgr/ntsk.c:10684
10684	in /home/dmops/build/svns/1666183702277/mgr/ntsk.c

从 stmt 里获取完整 sql

Copy
(gdb) p stmt ->sql_str
$9 = (schar *) 0x9ecafd10 "SELECT NVL(SUM(D1),0)||'' AS D1,\nNVL(SUM(D3),0)||'' AS D2,\nNVL(SUM(D2),0)||'' AS D3\nFROM (\nSELECT CASE WHEN C1 >= SYSDATE - 10 AND C1 < SYSDATE THEN COUNT(1)||'' ELSE 0||'' END AS D1,\nCASE WHEN C1 >= SYSDATE - 20 AND C1 < SYSDATE THEN COUNT(1)||'' ELSE 0||'' END AS D2,\nCASE WHEN C1 >= SYSDATE - 30 AND C1 < SYSDATE THEN COUNT(1)||'' ELSE 0||'' END AS D3\nFROM T1  where c2= ? and c3=? AND C4>=?\nGROUP BY T1.ID,C1\n)"

从 pln 里获取完整 sql。

Copy
(gdb) p pln->sqlstrs
$10 = (schar *) 0x9bd6e450 "SELECT NVL(SUM(D1),0)||'' AS D1,\nNVL(SUM(D3),0)||'' AS D2,\nNVL(SUM(D2),0)||'' 

https://eco.dameng.com/