| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Blocking Lock
Here is the v$lock, right now we have a blocking lock on the same table for
Insert:
SQL> select * from v$lock where sid=282;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
01B5B340 01B5B40C 282 TX 1703961 2763 6 0
8725 1
B1E1D830 B1E1D844 282 TM 19652 0 3 0
8725 0
B1E1D038 B1E1D04C 282 TM 19451 0 3 0
8725 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
B1E1CE58 B1E1CE6C 282 TM 19446 0 3 0
8725 0
B1E1CC78 B1E1CC8C 282 TM 19442 0 3 0
8725 0
B1E1C5E8 B1E1C5FC 282 TM 19443 0 3 0
8725 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
B1E1BD00 B1E1BD14 282 TM 40451 0 3 0
8725 0
B1E19C30 B1E19C44 282 TM 19656 0 3 0
8725 0
B1E17458 B1E1746C 282 TM 19794 0 3 0
8725 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
B1E17200 B1E17214 282 TM 19655 0 3 0
8725 0
B1E17110 B1E17124 282 TM 19777 0 3 0
8725 0
B1E16FA8 B1E16FBC 282 TM 19697 0 3 0 8725 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
B1E16C60 B1E16C74 282 TM 19449 0 3 0
8725 0
13 rows selected.
SQL> select * from v$session_wait where sid=282;
SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
282 32391 SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
SQL*Net message from client
SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
driver id 675562835 SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
28444553 #bytes
SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
1 00000001 SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
0 SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
00 -1 8804 WAITED SHORT TIME SID SEQ#
EVENT
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
P2 P2RAW
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
-----Original Message-----
From: Diego Cutrone [mailto:diegocutrone_at_yahoo.com.ar]
Sent: Friday, September 17, 2004 1:11 PM
To: Hamid Alavi; terrysutton_at_usa.net; 'Oracle-L (E-mail)
Subject: Re: Blocking Lock
Good to know
but what I'd like to know, in order to be able to try to help you out, are
the details of that wait.
Just a select to v$lock when the wait is taking place would work.
Thanks
Diego.
> yes it's "enqueue wait" and the SQL statement is an Insert one record into a
> table. > > -----Original Message----- > From: Diego Cutrone [mailto:diegocutrone_at_yahoo.com.ar] > Sent: Friday, September 17, 2004 12:52 PM > To: Hamid Alavi; terrysutton_at_usa.net; 'Oracle-L (E-mail) > Subject: Re: Blocking Lock > > > Hi Hamid, > > Were you able to get the wait event the sessions are waiting on? > Regarding your comments, most surely it should have been "enqueue wait" > event, but the interesting thing here should be the details from > v$session_wait and v$lock views when the wait is taking place. > > With that information we may have a better idea of the cause of the wait. > > HTH > Regards > Diego. > > > > > ----- Original Message ----- > From: "Hamid Alavi" <hamid.alavi_at_quovadx.com> > To: <terrysutton_at_usa.net>; "'Oracle-L (E-mail)" <oracle-l_at_freelists.org> > Sent: Friday, September 17, 2004 8:34 AM > Subject: RE: Blocking Lock > > > > NO we don't have any bitmap index, this is not happen all the times may > once > > or twice a day > > > > -----Original Message----- > > From: Terry Sutton [mailto:terrysutton_at_usa.net] > > Sent: Thursday, September 16, 2004 9:52 PM > > To: 'Oracle-L (E-mail) > > Subject: Re: Blocking Lock > > > > > > Is there a bitmap index on the table being inserted into? > > > > ----- Original Message ----- > > We have an application running on Oracle database 8.1.7.4 on sun Solaris > > 2.8, recently we have some problem with blocking locks one transaction > > (simple insert statement) blocking all other transactions and waitingfor
> > ever, is there any bug related to this issue announced by oracle. > > Any Idea appreciated. > > Thanks, > > > > > > Hamid Alavi > > > > Office : 818-737-0526 > > Cell phone : 818-416-5095 > > -- > > http://www.freelists.org/webpage/oracle-l > > > > -- > > http://www.freelists.org/webpage/oracle-l > > -- > > http://www.freelists.org/webpage/oracle-l > -- > http://www.freelists.org/webpage/oracle-l ======================= Confidentiality Statement =======================The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system.
===================== End Confidentiality Statement =====================
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 17 2004 - 12:16:09 CDT
 |
 |