Re: TX wait on I_OBJ# and C_OBJ#
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 24 Dec 2005 21:26:15 -0000
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" <dba.orcl@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
