RE: TX wait on I_OBJ# and C_OBJ#

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <dba.orcl@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 24 Dec 2005 12:44:42 -0500

By "with high number of concurrency (update)" do you mean several different
client processes are running in parallel updating a single table?

Does the gathering of the data to perform the update involve the creation of
temporary system or user objects on the fly?

I haven't lately scrutinized what they may have piled on to C_OBJ#, but I
would think that merely updating a table would not cause anything except
reads to find out references to the clusters, tables, indexes, lobs, and
user-defined objects. Now if the overall process of getting to the update
forces it to allocate objects on the fly, I suppose there could be a
problem. Hmm, all the processes aren't doing something that can't thread
like queuing up to truncate the same hard-wired scratch pad table, or
something like that?

Also, someone has to be blocking, so toss the query on page 178 of
SHEE/DESHPANDE/GOPALALKRISHNAN (maybe with a little more filtering to see
just this contention you're intereested in) at it and you should be

(If you don't have that book, drop what you're doing and acquire a copy, and
no, I don't get money to say that. The index alone is worth the price of the
book so you remember the name of the thing you forgot to remember you used
to know.)


  -----Original Message-----
  From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sami Seerangan
  Sent: Friday, December 23, 2005 3:52 PM
  To: oracle-l
  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
  ---------- -------- -- ---------- ---------- ---------- ---------- -------
--- ----------
           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.

Other related posts: