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 and
Oracle bug 5184776 and bug 5928271 can affect 11g databases that
deploy the mutex latching, 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
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
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
