ITPub博客

首页 > 数据库 > Oracle > [20261113]跟踪特定进程sql语句的执行2.txt

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

原创 Oracle 作者:lfree 时间:2026-11-22 19:42:33 0 删除 编辑

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

--//以前写的有点复杂,写一个简单的版本:

$ cat sqlmon.bt
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kksParseCursor
/ $1 == 0 || pid == $1 /
{
         printf("[%s][comm=%s pid=%d]: %s\n\n", strftime("%H:%M:%S", nsecs),comm,pid,str(arg1,reg("r8")));
}

--//注:我看到一些版本取的寄存器不同,先使用gdb跟踪kksParseCursor看看。

--//执行如下:
--//BPFTRACE_MAX_STRLEN=32768 bpftrace sqlmon.bt <spid> ,这样最大32768字符,基本满足大多数情况.

--//简单测试:
SCOTT@book01p> select * from emp where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

SCOTT@book01p> @ desc dept
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      DEPTNO                          NOT NULL NUMBER(2)
    2      DNAME                                    VARCHAR2(14)
    3      LOC                                      VARCHAR2(13)

SCOTT@book01p> @ desc dept
       Name                            Null?    Type
       ------------------------------- -------- ----------------------------
1      DEPTNO                          NOT NULL NUMBER(2)
2      DNAME                                    VARCHAR2(14)
3      LOC                                      VARCHAR2(13)


# BPFTRACE_MAX_STRLEN=32768 bpftrace sqlmon.bt 5544
Attached 1 probe
[09:38:42][comm=oracle_5544_boo pid=5544]: select * from emp where empno=7369

[09:38:50][comm=oracle_5544_boo pid=5544]: select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,nvl(t.spare7,0),ts.cachedblk,ts.cachehit,ts.logicalread,ts.im_imcu_count,ts.im_block_count,ts.im_sys_incarnation,ts.im_stat_update_time,ts.scanrate,nvl(t.acdrflags, 0),nvl(t.acdrtsobj#, 0),t.acdrdefaulttime,nvl(t.acdrrowtsintcol#, 0) from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)

[09:38:50][comm=oracle_5544_boo pid=5544]: select audit$ from tab$ where obj# = :1

[09:38:50][comm=oracle_5544_boo pid=5544]: select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1),nvl(i.unusablebefore#,0),nvl(i.unusablebeginning#,0), ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(intcols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

[09:38:50][comm=oracle_5544_boo pid=5544]: select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1

[09:38:50][comm=oracle_5544_boo pid=5544]: select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1

--//比如第2次执行desc dept就没有输出,根本没有执行相应的sql语句。

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/267265/viewspace-3189815/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    4020
  • 访问量
    8005049