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" <dba.orcl@xxxxxxxxx>
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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


Other related posts: