Re: TX wait on I_OBJ# and C_OBJ#
- From: Sami Seerangan <dba.orcl@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sat, 24 Dec 2005 14:57:51 -0500
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 <jonathan@xxxxxxxxxxxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
