Re: TX wait on I_OBJ# and C_OBJ#

Notes in-line

    marked with >>

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: Cc: "oracle-l" Sent: Saturday, December 24, 2005 7:57 PM Subject: Re: TX wait on I_OBJ# and C_OBJ#

Dear Jonathan,

Thank you so much for your response. As always you guessed right. The database has been newly created with AUTO undo tablespace.

The script will start 300 new sessions and each session will update a random row in one of the application tables(The application table has 600+ rows)
WITHOUT COMMIT. We are making sure that random selection of row is always unique to avoid row locks.

>>
>> Does this mean you are updating an indexed column
>> that represents something like a 'processstate' flag.
>> i.e. lots of rows change from (say) 'X' to 'Y'.
>>

While I was doing this test I saw so many US(Undo Segment Type) lock type in v$lock and it got vanished when new undo segment has been created. However TX
locks on I_OBJ# and C_OBJ# remains there as I showed you earlier.

>> Automatic Undo tries to keep down to one transaction per
>> undo segment if possible - so when you start 300 concurrent
>> transactions in a new database, you are going to find each
>> session creating a new undo segment, which requires a US
>> lock to be held temporarily - so a massive queue at this point
>> is not a big surprise.
>>
>> The TX locks you showed us are not "on" I_OBJ# and C_OBJ# -
>> the value 2 or 3 in the ID2 columns is simply stating that this is the
>> 2nd or 3rd time an undo slot has been used. It is only in TM
>> locks that you see object IDs.
>>

I got approx 2 of these locks when the concurrency is closer to 100 and it increases when the concurrency increases.

>> I think we need a proper tree of relevant locks to get
>> a clear picture of what is happening. There is a scrip
>> utllockt.sql that builds a utility for reporting a lock
>> tree. Set this up, and run a report the next time you
>> can make the block happen.
>>

Since the index is on sequence number (DEF$_TRANORDER index on AQ$CALL table)
and "right growing index", we saw all the 250+ updates had gone to only 2 of the leaf nodes. We almost proved ITL contention on this index with large number of concurrency.

>>
>> Does that mean in your test you are doing things like
>> update tabX set seq_no = seq.nextval ? Moving data from
>> the left hand end of the btree to the right-hand end ?
>>

 However we are not clear about why there are so many TX locks on data dictionary objects like I_OBJ# and C_OBJ#.

BTW, your new book is very useful to understand everything about optimizer behaviour..

>>
>> Thank you
>>

Thanks
Sami

--
http://www.freelists.org/webpage/oracle-l