 |
|
Oracle 10g release 2 locking changes?
Oracle Tips by Burleson Consulting |
Question: I just upgraded to 10gr2 (release 10.2) and I
now notice lich locking behavior, resulting in high waits on transaction
enqueue locks (enq: TX - row lock contention). I've isolated the
update statement, and I still get deadlocks.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
Avg %Total wait Call
Event
Waits Time (s) (ms) Time
------------------------------- ------------ ----------- ------ ------
enq: TX - row lock contention 5,541
16,593 2995 61.4
Answer: Oracle has changed their locking mechanism as
noted in bug 4,969,880, a workaround for changed locking behavior in 10gr2.
While this applies to explicit locking "select for update", MOSC can give
you specific details about the changes.
First, run autotrace from SQL*Plus for both statement and confirm that the
execution plans and run statistics are the same on both releases:
set autotrace on;
update xxx . . .
Also, the "enq: TX" wait is often associated with a shortage on the
Interested Transaction List (ITL), and increasing INITRANS for the offending
table and index may improve concurrency. It can also be related to
referential integrity enforcement and the use of bitmap indexes.
Try try doing
a 10046 trace
on the update? If you do it on both databases, you will see a difference.
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
