Home » RDBMS Server » Performance Tuning » How to find Bind variable values from Raw Trace file? (Oracle 10.2.0.3.0 on Red Hat Linux)
How to find Bind variable values from Raw Trace file? [message #343608] Wed, 27 August 2008 11:19 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,

In order to trace the sql query I traced the file (using dbms_system)

Now I am trying to find out the bind variable values from the raw trace file

However I am unable to find out which bind variable corresponds to which value.

Can anybody help me understand how the values in raw trace files are ordered with respect to the sql query bind variables?



select cpsm.varparam1, cap.npaytype, cap.laccpayseqnbr, capd.laccpaydtlseqnbr,
       capd.napprseqtype, capd.lapprseqnbr, kvh.varbasedocnbr,
       capd.lparentaccpaydtlseqnbr
kROM   kin_vou_hdr kvh,
       kin_vou_cshbnk kvc,
       kin_vou_appr_dtl kva,
       cgi_acc_pay_appr capa,
       cgi_acc_pay_dtl capd,
       cgi_acc_pay cap,
       cgi_param_system_m cpsm
WHERE  kvh.varbasedocnbr = :b13
AND    cpsm.iparamtypecd = :b12
AND    kvh.varvoutype = kvc.varvoutype
AND    kvh.varvouseries = kvc.varvouseries
AND    kvh.nkiscalyear = kvc.nkiscalyear
AND    kvh.lvounbr = kvc.lvounbr
AND    kvh.varbrandcd = kvc.varbrandcd
AND    kvh.varvoutype = kva.varvoutype
AND    kvh.varvouseries = kva.varvouseries
AND    kvh.nkiscalyear = kva.nkiscalyear
AND    kvh.lvounbr = kva.lvounbr
AND    kvh.varbrandcd = kva.varbrandcd
AND    kva.napprseqtype = :b11
AND    kva.lapprseqnbr = capa.laccpayapprseqnbr
AND    capa.laccpayseqnbr = cap.laccpayseqnbr
AND    capa.laccpaydtlseqnbr = capd.laccpaydtlseqnbr
AND    cap.laccpayseqnbr = capd.laccpayseqnbr
AND    cpsm.nparamcd = cap.npaytype
AND    kvc.varvoutype = :b10
AND    kvc.varvouseries = :b9
AND    kvc.varbrandcd = :b8
AND    kvc.nkiscalyear = :b7
AND    kvc.lvounbr = :b6
AND    kvc.npmtrektype = :b5
AND    kvc.varcashbankcd = :b4
AND    NVL ( kvc.varpmtreknbr, '~' ) = NVL ( :b3, '~' )
AND    NVL ( kvc.lpmtdtlseq, 0 ) = NVL ( :b2, 0 )
AND    ROWNUM = :b1

 Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=46 siz=32 off=0
  kxsbbbfp=b6e49770  bln=32  avl=07  flg=05
  value="6813136"
 Bind#1
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b7125e94  bln=22  avl=03  flg=09
  value=4043
 Bind#2
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b7124e18  bln=22  avl=02  flg=09
  value=2
 Bind#3
  oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=46 siz=2224 off=0
  kxsbbbfp=b6ded69c  bln=32  avl=02  flg=05
  value="PV"
 Bind#4
  oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=46 siz=0 off=32
  kxsbbbfp=b6ded6bc  bln=32  avl=03  flg=01
  value="PSE"
 Bind#6
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=96
  kxsbbbfp=b6ded6fc  bln=22  avl=03  flg=01
  value=2007
 Bind#7
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=120
  kxsbbbfp=b6ded714  bln=22  avl=05  flg=01
  value=95006973
 Bind#8
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=144
  kxsbbbfp=b6ded72c  bln=22  avl=02  flg=01
  value=1
 Bind#9
  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=46 siz=0 off=168
  kxsbbbfp=b6ded744  bln=32  avl=03  flg=01
  value="110"
 Bind#10
  oacdty=01 mxl=2000(200) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=46 siz=0 off=200
  kxsbbbfp=b6ded764  bln=2000  avl=08  flg=01
  value="12824296"
 Bind#11
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=2200
  kxsbbbfp=b6dedf34  bln=22  avl=00  flg=01
 Bind#12
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b7125eb8  bln=22  avl=02  flg=09
  value=1



Thanks and Regards,
OraSaket

Re: How to find Bind variable values from Raw Trace file? [message #343609 is a reply to message #343608] Wed, 27 August 2008 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the order they appear in the query.

Regards
Michel
Re: How to find Bind variable values from Raw Trace file? [message #343611 is a reply to message #343608] Wed, 27 August 2008 11:30 Go to previous message
orasaket
Messages: 70
Registered: November 2006
Member
Thanks Michel!

I too was thinking the same but some problem has really shaken my confidence

Once again thanks for the quick reply

Thanks and Regards,
OraSaket
Previous Topic: Does it mean Explain plan?
Next Topic: response time
Goto Forum:
  


Current Time: Sat Dec 10 18:41:33 CST 2016

Total time taken to generate the page: 0.09339 seconds