通過ERRORSTACK找出正在執行的SQL中的繫結變數值

 

 

oradebg中的ERRORSTACK可以幫我們找到被open中的SQL的繫結變數值。

舉例如下：

session A:

SQL> select spid from v$process where addr in (
2 select paddr from v$session where sid in (
3 select sid from v$mystat where rownum=1));

SPID
————————————
29397

SQL> var cnt number
SQL> exec :cnt :=1000000

PL/SQL procedure successfully completed.

SQL> select * from dba_objects where rownum<:cnt>

這個session正在執行時，我們對他做一個level 3的ERRORSTACK

SQL> oradebug setospid 29397
Oracle pid: 58, Unix process pid: 29397, image: oracle@qadb17 (TNS V1-V3)
SQL> oradebug dump errorstack 3
Statement processed.

從trace file中我們可以看到這一段資訊：

Cursor#2(ffffffff7b831758) state=ROW curiob=ffffffff7b849e10
curflg=46 fl2=400 par=0 ses=419176ae8
sqltxt(3b421a118)=select * from dba_objects where rownum<:cnt>hash=939f4a697bb3b0f40484e0e412dc34b1
parent=3b3b69db0 maxchild=01 plk=3b8156b20 ppn=n
cursor instantiation=ffffffff7b849e10 used=1247324332
child#0(3b4219f98) pcs=3b3b699c0
clk=3b8157478 ci=3b3b690a8 pn=3b44355f0 ctx=3b3b687f8
kgsccflg=0 llk[ffffffff7b849e18,ffffffff7b849e18] idx=0
xscflg=c0110476 fl2=5200009 fl3=42222008 fl4=100
Bind bytecodes
Opcode = 2 Bind Twotask Scalar Sql In (may be out) Copy
oacdef = 3b3b65378 ffsi = 48, ffsi = 0
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=ffffffff7b8499c8 bln=22 avl=02 flg=05
value=1000000 — 繫結變數值
Frames pfr ffffffff7b849d78 siz=20096 efr ffffffff7b849c48 siz=20024
Cursor frame. dump
enxt: 8.0×00000010 enxt: 7.0x00000cc8 enxt: 6.0x00000b58 enxt: 5.0×00001000
enxt: 4.0x00000f88 enxt: 3.0×00000720 enxt: 2.0×00000020 enxt: 1.0x00000f50
pnxt: 2.0×00000038 pnxt: 1.0×00000010
kxscphp ffffffff7b840690 siz=2152 inu=1672 nps=1624
kxscdfhp ffffffff7b840490 siz=984 inu=88 nps=0
kxscbhp ffffffff7b8402b0 siz=984 inu=168 nps=48
kxscwhp ffffffff7b8405a0 siz=1287808 inu=1283808 nps=1283624
Session cached cursors

從這段trace file中可以看到繫結變數為1000000。