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.

This is related one of the question you responded earlier.

This is what we are trying to do. We are having an ITL contention issue in Oracle Advanced Replication's Queue table's Index. We are trying to reproduce the same issue by using scripts.

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.

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.

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

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.

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

Thanks
Sami

On 12/24/05, Jonathan Lewis wrote:
>
>
> 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.

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