Question: I recently migrated from Oracle 10g
to Oracle 11g and I now see excessive deadlocks in Oracle 11g that
did not appear in 10g.
What has changed in 11g that causes additional deadlocks?
Answer: Oracle 10g release 2 and beyond
replaced some latch mechanisms with the mutex approach, claiming
that they are faster and more efficient than traditional locking
mechanisms. To improve cursor execution speed and hard pare time
within the library cache, mutexes replace library cache latches and
library ache pins.
Oracle claims that mutexes are faster and have improved
concurrency over library cache latches (and therefore less
deadlocks) because a mutex has a shorter code path. Oracle also say
that a mutex uses less CPU, which is important for CPU-bound
database where large data buffers remove I/O as a primary source of
contention.
The mutex memory structure is quite different from the older
latch structure and all 131,072 library cache buckets in 11g are now
protected by an exclusive mutex.
However, some shops complain that their 11g systems are more
sensitive to deadlocks than they were in Oracle 10g.
Beware, there are some early release issues with mutexes causing excessive 'cursor pin s wait on
x' wait events.
The cursor pin S wait on X wait event is mostly related to mutex
and hard parse and it happens when a session waits for this event
when it is requesting a shared mutex pin and another session is
holding an exclusive mutex pin on the same cursor object.
These are the parameters associated with this mutex wait event:
- P2 Mutex value (top 2 bytes contains SID holding mutex
in exclusive mode, and bottom two bytes usually hold the value
0)
- P3 Mutex where (an internal code locator) OR'd with
Mutex Sleeps
Beware, Oracle bug
5184776 can affect 11g databases that deploy the mutex
latching, causing excessive 'cursor pin s wait on x' wait
events.
If you suspect that you have a bug, you can revert to the
pre-mutex method with these commands that bounce your database into
the older latching mode, which may result in less deadlocks:
alter system set
"_kks_use_mutex_pin"=false scope=spfile;
shutdown immediate
startup
This hidden parameter should only be used with
the consent of Oracle support, and this parameter should NOT ever be
set in Oracle 12c.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
