[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/ ,如需转载,请注明出处,否则将追究法律责任。