TX wait on I_OBJ# and C

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: 2005-12-23 21:52:07
Message-id: f09dd6280512231252k108febffqc282dd6c88deeee@mail.gmail.com

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 Received on Fri Dec 23 2005 - 21:52:07 CST