[20211227]抽取跟踪文件中的绑定变量值.txt

[20211227]抽取跟踪文件中的绑定变量值.txt --//以前写过一个从跟踪文件抽取sql的脚本，估计也是当时从网上抄来的。 $ cat extractsql.sh #! /bin/bash awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT' --//尝试自己写一个抽取跟踪文件绑定变量值的脚本。 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试建立： variable v_deptno number ; variable v_dname varchar2(20) ; variable v_loc varchar2(20) ; exec :v_deptno :=10; exec :v_dname :=''; exec :v_loc :='aaa123'; SCOTT@book> @10046on 12 Session altered. SCOTT@book> select * from dept where deptno = :v_deptno or dname = :v_dname or loc = :v_loc;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @10046off Session altered. SCOTT@book> @ ttt tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12780.trc --//跟踪文件内容如下： ===================== PARSING IN CURSOR #140692216598400 len=79 dep=0 uid=83 oct=3 lid=83 tim=1640575733992020 hv=588998677 ad='7d57ffc8' sqlid='6nbgqn0jjqu0p' select * from dept where deptno = :v_deptno or dname = :v_dname or loc = :v_loc END OF STMT PARSE #140692216598400:c=1000,e=882,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1640575733992015 BINDS #140692216598400:  Bind#0   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=00 csi=00 siz=88 off=0   kxsbbbfp=7ff57597ca80  bln=22  avl=02  flg=05   value=10  Bind#1   oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=852 siz=0 off=24   kxsbbbfp=7ff57597ca98  bln=32  avl=00  flg=01  Bind#2   oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=852 siz=0 off=56   kxsbbbfp=7ff57597cab8  bln=32  avl=06  flg=01   value="aaa123" EXEC #140692216598400:c=999,e=1206,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3383998547,tim=1640575733993348 WAIT #140692216598400: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1640575733993422 FETCH #140692216598400:c=0,e=73,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=3383998547,tim=1640575733993542 WAIT #140692216598400: nam='SQL*Net message from client' ela= 295 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1640575733993890 FETCH #140692216598400:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=3383998547,tim=1640575733993961 STAT #140692216598400 id=1 cnt=1 pid=0 pos=1 obj=87106 op='TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=60 us cost=3 size=20 card=1)' WAIT #140692216598400: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1640575733994096 *** 2021-12-27 11:28:56.487 WAIT #140692216598400: nam='SQL*Net message from client' ela= 2493215 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1640575736487337 CLOSE #140692216598400:c=0,e=12,dep=0,type=0,tim=1640575736487462 ===================== --//难点在于bind#1值为NULL。没有value=行.视乎avl表示字符或者整形变量长度。 $ sed  -n "/^ Bind#/p;/^  value=/p;s/^  kxsbbbfp=.* avl=00 .*$/  value=NULL/p" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12780.trc | paste -d: - - Bind#0:  value=10 Bind#1:  value=NULL Bind#2:  value="aaa123" --//我前面使用awk抽取sql语句，如何两者配合起来呢？ $ awk   '/^ Bind#/;/^  value=/;/^  kxsbbbfp=.* avl=00 .*$/{print "  value=NULL"};/PARSING IN CURSOR/,/END OF STMT/' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12780.trc | egrep -v '^PARSING|^END OF STMT' select * from dept where deptno = :v_deptno or dname = :v_dname or loc = :v_loc  Bind#0   value=10  Bind#1   value=NULL  Bind#2   value="aaa123" alter session set events '10046 trace name context off' --//不是太好应该拼接在一行。 $ awk   '/^ Bind#/{printf "%s",$0};/^  value=/;/^  kxsbbbfp=.* avl=00 .*$/{print "  value=NULL"};/PARSING IN CURSOR/,/END OF STMT/' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12780.trc | egrep -v '^PARSING|^END OF STMT' select * from dept where deptno = :v_deptno or dname = :v_dname or loc = :v_loc  Bind#0  value=10  Bind#1  value=NULL  Bind#2  value="aaa123" alter session set events '10046 trace name context off' --//ok,建立脚本如下： $ cat extractbindvalue.sh #!/bin/bash awk   '/^ Bind#/{printf "%s",$0};/^  value=/;/^  kxsbbbfp=.* avl=00 .*$/{print "  value=NULL"};/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT' $ extractbindvalue.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12780.trc select * from dept where deptno = :v_deptno or dname = :v_dname or loc = :v_loc  Bind#0  value=10  Bind#1  value=NULL  Bind#2  value="aaa123" alter session set events '10046 trace name context off' --//补充一点可以出现bind#1没有赋值，可以不必执行exec :v_dname :='';,这样就是null。一些应用会导致执行计划畸形。 --//另外kxsbbbfp表示怎么那位知道。