Re: TX wait on I_OBJ# and C_OBJ#

For a lock of type TX, the ID2 value is not an object id, it's the sequence number of the slot taken by the transaction in the transaction table in an undo segment header. ID1 is the undo segment number * 65536 + undo slot number. (You can cross-check this against v$transaction, columns XIDUSN, XIDSLOT, XIDSQN). It is a little odd that you have re-used transaction slots so little - have you just created the database, or a new undo tablespace. If not, it is possible that some recent action has created a lot of new undo segments (assuming you are using automatic undo) - this could be contributing indirectly to your problems.

If you have lots of blocked transactions, then you could run a query against v$lock where BLOCK = 1, which will identify the sessions (and locks) doing the blocks, this may take you a step forward in solving the problem. In this case, with TX locks requested in mode 6, the commonest cause is simply waiting for another session to release some locked rows.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 29th Nov 2005

• Original Message ----- From: "Sami Seerangan" To: "oracle-l" Sent: Friday, December 23, 2005 8:52 PM Subject: TX wait on I_OBJ# and C_OBJ#

While we are updating one of the application table with high number of concurrency(update) we started seeing
TX wait on I_OBJ# and C_OBJ# (object id 2 and 3) as below.

select * from gv$lock where request !=0;

   INST_ID      SID
TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

• ---------- 1 50 TX 2424846 2 0 6 519 0 1 116 TX 7733248 2 0 6 4 0 1 24 TX 7667720 3 0 6 16 0 1 110 TX 7798790 3 0 6 5 0

The above locks are increased by increasing the number of concurrency.

SQL> select id2,count(*) from gv$lock where lmode=6 and id2 in (2,3) group by id2;

       ID2 COUNT(*)
---------- ----------

         2        132
         3        132

132, is it a limit to acquire number of TX locks on I_OBJ# and C_OBJ#?

Thanks in advance.
Sami

--

http://www.freelists.org/webpage/oracle-l Received on Sat Dec 24 2005 - 20:36:29 CST