Well done. And thanks for the follow-up/summary explaining what you did to solve the problem. -Mark -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of dd yakkali Sent: Wednesday, May 27, 2009 4:07 PM To: Tanel Poder Cc: martin.a.berger@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: Re: Tx - row lock contention after implementing transaction management in application server Tanel, etal Thanks a lot guys, It worked great on the child insert session. I could not get the bind values for the parent insert session. proactive in production is tough as we have a big connection pool size and we do not know when this issue occurs and we do not have a way of reproducing it. This way they can look at the bind values and figure out what data is causing this to happen and troubleshoot it. As people already said, I do not think it has any thing to do with not having an index on the FK column. Also I do not know why transaction mgt uisng different oracle sessions in one Java transaction. Thanks every one for their replies. Here is what I did. 1) select spid, pid from gv$process where addr = (select paddr from gv$session where sid = 1037); 9510 45 2) oradebug setospid 9510 3) oradebug dump errorstack 2 Here is what I see in the trace file Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=264 off=0 kxsbbbfp=ffffffff7b9d6258 bln=22 avl=02 flg=05 value=2 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=24 kxsbbbfp=ffffffff7b9d6270 bln=22 avl=03 flg=01 value=2008 Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=48 kxsbbbfp=ffffffff7b9d6288 bln=22 avl=05 flg=01 value=26821.62 Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=72 kxsbbbfp=ffffffff7b9d62a0 bln=22 avl=02 flg=01 value=12 Bind#4 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=96 kxsbbbfp=ffffffff7b9d62b8 bln=22 avl=00 flg=01 Bind#5 oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=120 kxsbbbfp=ffffffff7b9d62d0 bln=32 avl=00 flg=01 Bind#6 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=152 kxsbbbfp=ffffffff7b9d62f0 bln=22 avl=00 flg=01 Bind#7 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=176 kxsbbbfp=ffffffff7b9d6308 bln=22 avl=00 flg=01 Bind#8 oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=200 kxsbbbfp=ffffffff7b9d6320 bln=11 avl=00 flg=01 Bind#9 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=216 kxsbbbfp=ffffffff7b9d6330 bln=22 avl=04 flg=01 value=425377 Bind#10 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=240 kxsbbbfp=ffffffff7b9d6348 bln=22 avl=04 flg=01 value=939865 Thanks Deen On Tue, May 26, 2009 at 3:22 PM, Tanel Poder <tanel@xxxxxxxxxx> wrote: > > v$sql_bind_capture may not give you the right bind variable value as you > can't really control when exactly the capture happens (you may see an "old" > bind value or someone else's bind there). > > sql_trace would be the proactive approach if you can rerun your query and > reproduce the problem. If the problem has already happened to you, you can > attach to target with oradebug and run "oradebug dump errorstack 2" on the > target process and search for "value=" or "bfp=" in the tracefile. > > Differend Oracle versions might show the output differently but in my case it > showed the bind value 1234567890 ok: > > cursor instantiation=fffffd7ffdae6ac8 used=1243347090 > child#0(3ab6906a0) pcs=39c8b1718 > clk=3a20672a8 ci=3a51a1a18 pn=3a420c2f0 ctx=3a4518978 > kgsccflg=0 llk[fffffd7ffdae6ad0,fffffd7ffdae6ad0] idx=0 > xscflg=c0110476 fl2=5200009 fl3=42222008 fl4=100 > Bind bytecodes > Opcode = 2 Bind Twotask Scalar Sql In (may be out) Copy > oacdef = 3a6c353c0 Offsi = 48, 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=fffffd7ffdae5e28 bln=22 avl=06 flg=05 > value=1234567890 > > -- > Regards, > Tanel Poder > http://blog.tanelpoder.com > > > > ________________________________ > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On > Behalf Of Martin Berger > Sent: 26 May 2009 21:58 > To: dd.yakkali@xxxxxxxxx > Cc: oracle-l@xxxxxxxxxxxxx > Subject: Re: Tx - row lock contention after implementing transaction > management in application server > > Deen, > maybe you want to start with > http://laurentschneider.com/wordpress/2007/05/vsql-and-bind-variable.html > hh, > Martin > -- > //www.freelists.org/webpage/oracle-l > Am 26.05.2009 um 20:41 schrieb dd yakkali: > > Hello everyone, > > After our application folks implemented transaction management in the app, I > am seeing a bunch of seesions waiting with "Tx - row lock contention" on an > insert statement. we found that the parent table insert is not commited and > hence the child record insert is hanging as both these statements are using > different oracle sessions for some reason. This continues for eternity, until > the app server is killled and restarted. > > > Sun Java Enterprise Server, hibernate, oracle 10.2.0.4 RAC. > > > Now here is the question: Our java app server folks are asking me to give > them bind variable values of the statement that is hanging. We have a > connection pool which is 132 connections size. Is there any way to get the > bind variable values after the fact, i.e while it is waiting for the parent > to commit? > > > > Thanks > Deen -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l