Posts Tagged ‘bind value’
Tracking the bind value with errorstack dump
It is technically impossible to find out the bind value of the past query without proactive trace like 10046(level >= 4) or auditing enabled. But enabling 10046 event trace or auditing is too harsh requirement for that purpose.
One of the interesting solutions is errorstack(level >=2). Errorstack dump shows the bind value of the current process(not past!) along with the callstack and other important informations.
---------------------------------------- Cursor#2(08040C64) state=BOUND curiob=080479E8 ... Bind bytecodes Opcode = 2 Bind Twotask Scalar Sql In (may be out) Copy oacdef = 2bdb5bf0 Offsi = 36, Offsi = 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 off=0 kxsbbbfp=080aa6e0 bln=22 avl=02 flg=05 value=1
But the problem is that even the errorstack dump is NOT proactive solution, which means you should stay there at the right time and the right place. Night shift, huh?
Here you can make some ideas on automating the errorstack dump, like following.
1. ORA-00001 event trace
If your requirement is to get the bind value when the process got unique key violation, you can just turn on the trace for the diagnostic event 1, like this.
alter system set events '1 trace name errorstack level 2, forever'; or alter session events '1 trace name errorstack level 2, forever';
, which means that whenever(forever) you hit the ORA-00001 exception(1), dump errorstack at level 2(errorstack level 2).
2. Proactively monitor the situation and dump errorstack using oradebug
For instance, let me assume that you want to find out the bind value which causes the serious TX lock contention. In this case, simple trick with PL/SQL and SQL*Plus would meet your requirement.
For instance, following simple script
connect /as sysdba var sid number; begin for idx in 1 .. 1000000 loop dbms_lock.sleep(1); select min(sid) into :sid from v$lock where type = 'TX' and request > 0 and block = 0 and ctime > 30; if :sid > 0 then exit; end if; end loop; end; / print :sid col spid new_value v_spid select spid from v$process where addr in (select paddr from v$session where sid = :sid); oradebug setospid &v_spid oradebug dump errorstack 2
would dump errorstack for the process who has been waiting for the TX lock over 30 secs.
The result is
---------------------------------------- Cursor#2(08040C64) state=BOUND curiob=080479E8 curflg=44 fl2=0 par=00000000 ses=2EB3F224 sqltxt(2E5F0058)=insert into t1 values(:b1, 1) hash=bbc06f38e42d1542e8fdfe7363d9ab40 parent=2945F7D0 maxchild=02 plk=2C75C638 ppn=n cursor instantiation=080479E8 child#1(27DB82B0) pcs=295262B8 clk=2B138D44 ci=2AD04520 pn=2C7CEA94 ctx=2B7508A8 kgsccflg=0 llk[080479EC,080479EC] idx=0 xscflg=c0110676 fl2=d100008 fl3=42222008 fl4=0 Bind bytecodes Opcode = 2 Bind Twotask Scalar Sql In (may be out) Copy oacdef = 2bdb5bf0 Offsi = 36, Offsi = 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 off=0 kxsbbbfp=080aa6e0 bln=22 avl=02 flg=05 value=1
There would other tricks to get the bind value using errorstack at this way of emulating proactive trace. Even this is not a perfect solution, the merit of negligible overhead is not negligible.