Re: trying to interpret bind variable section in 100046 trace

From: Stefan Koehler <contact_at_soocs.de>
Date: Thu, 21 May 2015 10:53:10 +0200 (CEST)
Message-ID: <793236373.43200.1432198390740.JavaMail.open-xchange_at_app01.ox.hosteurope.de>



Hi Anthony,

> Is it just that the bind for #26 is null?
Yes, it is caused by NULL values. Here is just a short demo case for verification:

SQL> create table TAB (a number, b number);
SQL> variable B1 number;
SQL> variable B2 number;
SQL> exec :B1 := NULL;
SQL> exec :B2 := 1;
SQL> insert into tab values (:B1, :B2);

PARSING IN CURSOR #140475471789448 len=33 dep=0 uid=91 oct=2 lid=91 tim=1432197681406040 hv=1010674988 ad='7c7617d0' sqlid='752c1u0y3vb9c' insert into tab values (:B1, :B2)

BINDS #140475471789448:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0   kxsbbbfp=7fc2fe987928 bln=22 avl=00 flg=05  Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24   kxsbbbfp=7fc2fe987940 bln=22 avl=02 flg=01   value=1

By the way you can also reason this due to avl (= actual value length).

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> anthony Sanchez <anthonycsanchez_at_gmail.com> hat am 21. Mai 2015 um 00:36 geschrieben:
>
> Hi folks,
>
> 11.2.0.4 on Windows 2008R2
>
> I'm trying to troubleshoot an issue and need help deciphering the bind variables for an update statement in my 10046 trace file.
>
> I'm trying to figure out why some of the binds have no "value=" part at the bottom. For example:
>
> Bind#26
> oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
> oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
> kxsbbbfp=00000000 bln=22 avl=00 flg=09
>
> Bind#51
> oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
> oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
> kxsbbbfp=0645223f bln=22 avl=01 flg=09
> value=0
>
> I did learn that oacdty=02 means its of data type number. Is it just that the bind for #26 is null?
>
>
> thanks,
> Anthony

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 21 2015 - 10:53:10 CEST

Original text of this message