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

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

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

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

1 comment:

Anonymous said...

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