Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************Got the opportunity to pen a Book on Database Cloud Services.......More details to follow.***************

Title : Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2021


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Azure Certified Architect Expert

Azure Certified Architect

Azure Certified Administrator

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Monday, September 10, 2012

cursor: mutex X cursor: mutex S

Symtons:  Bad Performance, Query running long. Not using optimal plan so on..or  New upgrade to 11g

In AWR you see.

It is evident that most of the db time is spent on Mutex X and S

What is a Mutex:

Mutexes are a lighter-weight and more granular concurrency mechanism than latches. Mutexes take advantage of CPU architectures that offer the compare and swap instructions (or similar). The reason for obtaining a mutex in the first place, is to ensure that certain operations are properly managed for concurrency. E.g., if one session is changing a data structure in memory, then another session must wait to acquire the mutex before it can make a similar change - this prevents unintended changes that would lead to corruptions or crashes if not serialized. 

·  The library cache mutex is acquired for similar purposes that the library cache latches were acquired in prior versions of Oracle. In 10g, mutexes were introduced for certain operations in the library cache.  Starting with 11g, the library cache latches were replaced by mutexes, hence this new wait event. 

·  This wait event is present whenever a library cache mutex - X is held in exclusive mode by a session and other sessions need to wait for it to be released.  There are many different operations in the library cache that will require a mutex.

In my case: 

I was seeing huge wait events on Cursor Mutex X exclusive so on further analysis. 

In Library cache analysis we can clearly see there 53% Misses, ahaa.......

 In my version count I can clearly see there are 4000 different plans or versions for one single query which is very bad and this will cause library cache contention and eventually exhaustion and too much CPU use so on..
In the above screen shot we can see that 45% of the DB time is spent on parsing which is bad..  

From the above it is evident that some thing is not right and its time to work with Oracle Support.
 Since patch for Bug 10187168 is already applied. we were told make some parameter changes.

alter system set "_cursor_features_enabled"=1026 scope=spfile SID='*';
alter system set event="106001 trace name context forever,level 50" scope=spfile SID='*';
shared_pool_reserved_size 268435456 # set to 10% of SHARED_POOL_SIZE or 512M explicitly

These parameter changes fixed the Mutex and hard parsing issues for us.
Known Bugs:

      Bug 10270888 - ORA-600[kgxEndExamine-Bad-State] / mutex waits after a self deadlock
      Bug 9591812 - Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X")
      Bug 9499302 - Improve concurrent mutex request handling
      Bug 7441165 - Prevent preemption while holding a mutex (fix only works on Solaris)
      Bug 8575528 - Missing entries in V$MUTEX_SLEEP.location


Anonymous said...

SQL AREA missed is most likely 0.53% as SQL Area STATS is not 1115% ?

Khem Chhetri said...

Hi Sameer, Thanks for this great article on Cursor Mutex X/S .

I have query related to our database performance issue. Recently we migrated our database from 11g to 12c , we suddenly started facing the system performance issue - CPU spike and lots of WAITS.
There is one Query thats has high Elapse time & High CPU time.
For this particular Query has Version count 1000+. Is this version count of all plan currently in instance or Is it giving for particular Snap window?