[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表示怎麼那位知道。