RE: TX wait on I_OBJ# and C_OBJ#

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 enlightened.

(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.)

Regards,

mwf
  -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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
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 Sat Dec 24 2005 - 18:44:42 CST