Errorstack dumpを使ってバインド値をわかる

もう実行が終わったQueryのバインド値を獲得するのは技術的に不可能なものです。唯一な方法は１００４６イベントをレベル４以上で活性化したり、Auditingを活性化することのみです。しかし、１００４６トレースとAuditingはoverheadがかなり大きくて実際の運営環境では適用しにくいです。

一つの適用可能な方法はerrorstack（レベル＞＝２）です。Errorstack dumpはcallstackや他の情報の外にもバインド変数の値を出力してくれます。

----------------------------------------
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

Errorstackの問題点の一つは必ず現在問題が発生している中に実行されなければならないということです。すなわち、残業が必要になることです。これは私たちが願うものではありませんでしょう。

ここで、いくつのアイディアを出すことができます。
1. ORA-00001 イベントトレース
下のようにすればORA-00001(unique key violation)が発生するたびにerrorstackを実行できます。

alter system set events '1 trace name errorstack level 2, forever';
or
alter session events '1 trace name errorstack level 2, forever';

ORA-00001エラーを起こすバインド値を分かることが目的なら、上の方法で簡単にできます。

2. システムをmonitoringしながら自動的にerrorstackを実行
例えば、下のような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

このScriptはv$lockビューをmonitoringしながら、TX Lockを30秒以上待っているSessionについてerrorstackを実行します。結果は下のような問題のバインド値です。

----------------------------------------
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

たとえerrorstackがバインド値を分かる完璧な方法ではないですが、overheadが小さいことを考慮すればとても魅力的な方法だと言えます。