[20260902]跟踪特定进程sql语句的执行.txt

[20260902]跟踪特定进程sql语句的执行.txt

--//利用oracle每次执行前都计算hash值的特性，使用bpftrace写一个跟踪sql语句执行的脚本,
--//有点小问题，无论使用str，buf,缺省最大仅仅显示1024字节，无法抓取更长的sql语句。
--//可以加入 BPFTRACE_MAX_STRLEN=32768 bpftrace sql <spid> ,这样最大32768字符,基本满足大多数情况.

$ cat sql.bt
BEGIN
{
    printf("Tracing Oracle kggmd5Update... Target PID: %d\n", $1);
    printf("Hit Ctrl-C to stop.\n\n");
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kggmd5Finish
/ $1 == 0 || pid == $1 /
{
    @is_fin[pid] = 1;
//  printf("\nkggmd5Finish Begin\n");
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kggmd5Finish
/ $1 == 0 || pid == $1 /
{
    delete(@is_fin[pid]);
//  printf("kggmd5Finish End\n");
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgscComputeHash
/ $1 == 0 || pid == $1 /
{
    @is_kgscComputeHash[pid] = 1;
    printf("\nkgscComputeHash Begin\n");
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgscComputeHash
/ $1 == 0 || pid == $1 /
{
    printf("\033[1;32m[%s] kgscComputeHash #%02d pid=%d return hash_valuer=%08lx %lu \033[0m\n", strftime("%H:%M:%S", nsecs), @kgg, pid,retval,retval);
    printf("kgscComputeHash END\n");
    delete(@is_kgscComputeHash[pid]);
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kggmd5Update
/ $1 == 0 || pid == $1 /
{
    @kgg++;
    @is_kgg[pid] = 1;
    if (@is_fin[pid] != 1 && @is_kgscComputeHash[pid] == 1 ){
        printf("[%s] kggmd5Update #%02d pid=%d ptr=%016lx length=%d hash_text=\n%s\n", strftime("%H:%M:%S", nsecs), @kgg, pid,arg1,arg2,str(arg1,arg2+1));
    }
}

// uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kggmd5Update
// / $1 == 0 || pid == $1 /
// {
//     if (@is_kgg[pid] == 1) {
//         printf("[%s] kggmd5Update #%02d pid=%d return hash_value=%016lx %lu\n", strftime("%H:%M:%S", nsecs), @kgg,pid, retval, retval);
//         delete(@is_kgg[pid]);
//     }
// }

END
{
    printf("\nTotal kggmd5Update: %d\n", @kgg);
    delete(@kgg);

}

--//简单测试：
SCOTT@book01p> select * from dept where deptno=34;
no rows selected

SCOTT@book01p> variable a varchar2(32);
SCOTT@book01p> exec :a := '1111';
PL/SQL procedure successfully completed.

--//测试结果:
# BPFTRACE_MAX_STRLEN=32768 bpftrace sql.bt 3190
Attached 7 probes
Tracing Oracle kggmd5Update... Target PID: 3190
Hit Ctrl-C to stop.


kgscComputeHash Begin
[09:35:39] kggmd5Update #01 pid=3190 ptr=00007ffe205a7ec8 length=35 hash_text=
select * from dept where deptno=34;
[09:35:39] kgscComputeHash #03 pid=3190 return hash_valuer=7aab7385 2058056581
kgscComputeHash END


kgscComputeHash Begin
[09:36:13] kggmd5Update #04 pid=3190 ptr=00007ffe205a7ed0 length=26 hash_text=
BEGIN :a := '1111'; END;
;
[09:36:13] kgscComputeHash #06 pid=3190 return hash_valuer=d1c69037 3519451191
kgscComputeHash END