OraFAQ Forum: Performance Tuning » How to find Bind variable values from Raw Trace file?

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to find Bind variable values from Raw Trace file? [message #343608]

Wed, 27 August 2008 11:19

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

Report message to a moderator

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

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In the order they appear in the query.

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	Does it mean Explain plan?
Next Topic:	response time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 16:13:44 CDT 2024