© 2013 oracle corporation –proprietary and confidential 1 · agenda •overview •wait events...

<Insert Picture Here>

2

Mutexes in Oracle

Balavignesh ArumugamGlobal Customer Services

© 2013 Oracle Corporation – Proprietary and Confidential

Agenda

• Overview

• Wait Events for Mutexes

• Information to Gather

• How to identify blocker

• Potential Solutions / Workaround

3

• Potential Solutions / Workaround

• Demo on cursor: pin S

• References


Overview

• A mutex, similarly to a latch, is a low-level serialization mechanism used to control access to a shared data structure in the SGA.

• More granular than latches.

• Serialization is required to avoid an object being:

4

• Serialization is required to avoid an object being:• deallocated while someone is accessing it

• read while someone is modifying it

• modified while someone is modifying it

• modified while someone is reading it


Overview Cont.

• Mutexes can be defined and used in many different ways. For example:• Each structure being protected by a mutex can have its own mutex (e.g. a parent cursor has its own mutex, and each child cursor has its own mutex)

• Each structure can be protected by more than one mutex,

5

• Each structure can be protected by more than one mutex, with each mutex protecting a different part of the structure


Benefits

• Smaller and Faster

• More granular than latches

• Less potential for false contention

6

• Less potential for false contention

• Replacements for Latches and Pins


Wait Events for Mutexes

• Cursor: pin S

• Cursor: pin X

• Cursor: pin S wait on X

• Cursor: mutex S

• Cursor: mutex X

7

• Cursor: mutex X

• Library cache: mutex X


Cursor: pin S

• When pinning a cursor for execute, and the cursor is currently being examined by another S.

• Re-executing SQL statements already loaded in the library cache.

• Too many sessions are trying to execute the cursor

8

• Too many sessions are trying to execute the cursor concurrently.

• Typically, a cursor must be pinned by each session before execution.

• Mutex is in the cursor or child cursor.


Cursor: pin X

• A session waits on this event when it needs to get a mutex in X mode for serialized operations such as building a cursor.

• When performing exclusive operation on cursor like dbms_shared_pool.purge, keep / unkeep etc.

9

dbms_shared_pool.purge, keep / unkeep etc.

• Mutex is in the cursor or child cursor.


Cursor: pin S wait on X

• A session waits for "cursor: pin S wait on X" when it wants a mutex in S (share) mode on a specific cursor but some other session holds that same mutex in X (exclusive) mode. Hence the current session has to wait for the X mode holder to release the mutex.

10

• Occurs when hard parse time for a cursor is too long for the session holding the X mutex and at the same time another session is trying to execute the cursor.

• For SQL with long parse times.

• For SQL with high version counts.

• Frequent hard parses.


Cursor: mutex S

• Parent examination

• When finding a cursor to execute, the parent must be examined. The examination of the parent is performed using the mutex, cursor: mutex S.

11

• When the parent cursor has many child cursors involved, this waits will come as the server process has to traverse the entire list of child cursors under the parent to find a match.

• Mutex is in the parent cursor.


Cursor: Mutex X

• Building a new cursor under a parent• Although this operation is cheaper, building many cursors under a parent cursor is not recommended.

• Capture SQL bind data

• Build or Update statistics blocks

12

• Build or Update statistics blocks

• Mutex is in the parent cursor.


Library cache: mutex X

• Replacement for library cache latch

• One Symptom, Multiple Causes

• The "library cache: mutex X" symptom is very common and can be caused by a number of issues, so it is very important

13

can be caused by a number of issues, so it is very important to fully understand the root cause in order to determine the right action.

• Mutex is in the KGL bucket.


Library cache: mutex X cont.

• Other Common causes • Frequent Hard Parses

• If the frequency of Hard Parsing is extremely high, then contention can occur on this pin.

• High Version Counts

14

• When Version counts become excessive, a long chain of versions needs to be examined and this can lead to contention on this event

• Invalidations and reloads

• Known Bugs

• Bug:7441165 - CPU Pre-emption can cause problems on Solaris (this fix only applies to this OS)


Pictorial Representation


Information to Gather for Diagnosis

• Following diagnostic information has to be collected:-

• AWR Report

• ASH Report

• Hanganalyze trace

16

• Systemstate dump


How to identify blocker using SQLs?

• To find the blocking session, use the top bytes of v$session.p2raw e.g.,

SQL> select p2raw from v$session

where event = 'cursor: pin S wait on X';

P2RAW


P2RAW

---------------

0000139700000000

<Sid> <RefCnt>

the top bytes of p2raw is the blocker 0x00001397 which when converted to decimal, is Session Id 5015.

Cont.

• SQL similar to the following might also be useful:

SQL> SELECT count (*),

to_number(substr(to_char(rawtohex(p2raw)), 1, 8),

'XXXXXXXX') blocking_sid FROM v$session


'XXXXXXXX') blocking_sid FROM v$session

WHERE event = 'library cache: mutex X‘

group by to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX');

Potential Solutions / Workaround

• Increase the SGA if the shared pool is too small and heavy hard parse occurs due to reloads.

• For 11g, make sure cursor_sharing is not similar, as it has been deprecated. This may also cause cursor: mutex S waits: Recommended to use FORCE.

19

Recommended to use FORCE.

• The holder of the resource being scheduled Off CPU by the OS/Resource Manager.

• For 11.2.0.2, apply 11.2.0.2.3 PSU 12419331 and above

• For 11.2.0.3, apply 11.2.0.3.3 PSU 13923374 and above

• Many mutex fixes are included in the above PSUs


Potential Solutions Cont.

• Increase SESSION_CACHED_CURSORS to higher value if there are too many parses and waits on cursor: pin S.

• Use CURSOR_SHARING to FORCE to avoid waits on cursor: mutex S & cursor: mutex X due to huge version counts.

20

• If possible run with _LIBRARY_CACHE_ADVICE = FALSE and see if that helps (It is possible that a system which has many execs per second will benefit by turning off the overhead of the advisory).

• On HP platforms ensure HPUX_SCHED_NOAGE parameter is set to 178.



• Obsoleting parent cursors if VERSION_COUNT exceeds a threshold (cursor: mutex S).

• In 11gR2, an issue of Child cursors growing very long was introduced. An enhancement request was filed to address this issue Bug 10187168.

21

issue Bug 10187168.

• When the child cursors grow beyond certain count be it 20 or 100, it obsoletes the parent cursors. In order to activate this enhancement bug set following:



• For 11.2.0.2, apply the 11.2.0.2.2 PSU 12431716 atleast and do the following.• _CURSOR_FEATURES_ENABLED to 1026

• event 106001 with value 100 (as the parameter _CURSOR_OBSOLETE_THRESHOLD is not present)

22

• Set , event= 106001 trace name context forever, level 100 in init.ora

• For 11.2.0.3 and above, do the following.• _CURSOR_OBSOLETE_THRESHOLD to 100 (this is the number of child cursor after which we obsolete it)


Hot Copy Mutex

• In some cases there may be genuine mutex contention on a single object where multiple sessions want to get a KGL mutex to execute or access the same object.

• The fix enables the DBA to specify that certain objects

23

• The fix enables the DBA to specify that certain objects should be copied and then have different sessions map to different copies thereby reducing contention on the original single object.


Cont.

• To identify if the issue is a hot mutex use the following SQL :

SQL> select KGLNAOBJ, KGLNAOWN, KGLHDNSP, KGLOBTYP, KGLNAHSV, KGLOBT23, KGLOBT24 from X$KGLOB where KGLOBT23 > 100000 or KGLOBT24 > 100000 order by KGLOBT24;

24

• Run the above SQL 4 or 5 times in 3 minutes interval during the problem.

• If the number for KGLOBT23 or KGLOBT24 has risen sharply in the short period of time then there is a hot object.


KGLOBT24;

Cont.

• Another way to monitor the specific mutex being contended on is to run the following at intervals.

• If you have a particularly hot mutex then the same P1 will come

SQL> select sid,p1 from V$SESSION where event like '%mutex%';

25

• If you have a particularly hot mutex then the same P1 will come back for a large proportion of selects.

• You can then run the following to see what the actual object is:


SQL> select KGLNAOBJ, KGLNAOWN, KGLHDNSP, KGLOBTYP, KGLNAHSV from X$KGLOB where KGLNAHSH= {value of P1};

Cont.

• You can apply the hot copy fix from 11.2.0.2 onwards w/o any interim patches.

• Set the following parameters in init.ora:-

_KGL_HOT_OBJECT_COPIES={a}

26

or (where the object is a cursor)

NOTE: Only with Oracle Support Guidance


_KGL_DEBUG="name='{b}' schema='{c}' namespace={d} debug=33554432"

_KGL_DEBUG="hash='{e}' debug=33554432"

Cont.

Where:

{a} - half the CPU count{b} - KGLNAOBJ from SQL {c} - KGLNAOWN from SQL {d} - KGLHDNSP from SQL{e} - KGLNAHSV from SQL.

In the case of a cursor {e} can be used which is the


In the case of a cursor {e} can be used which is the FULL hash value of the SQL.

Cont.

• The same can be performed using dbms_shared_pool APIs in 11.2.0.2+.

SQL> EXEC DBMS_SHARED_POOL.MARKHOT('username','object_name',KGLHDNSP);

28

• NOTE: dbms_shared_pool.markhot is an instance life only change. Any restart of the database will result in the mechanism no longer applied. The same has to be repeated every time once instance starts up.


<or, for a cursor>

SQL> EXEC DBMS_SHARED_POOL.MARKHOT('KGLNAHSV', 0);

D E M O N S T R A T I O N


D E M O N S T R A T I O N

References

• Bug 10187168 - Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold (Doc ID 10187168.8) – 11g

• Bug 10411618 - Enhancement to add different "Mutex" wait schemes (Doc ID 10411618.8) – 11g

• FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events (Doc ID 1356828.1)

• How to Collect Diagnostics for Database Hanging Issues [ID 452358.1]

30

• How to Collect Diagnostics for Database Hanging Issues [ID 452358.1]

• How to Generate an AWR Report and Create Baselines [ID 748642.1]

• Init.ora Parameter "_KGL_DEBUG" [Hidden] Reference Note [ID 1388197.1]

• Troubleshooting: Waits for Mutex Type Events (Doc ID 1377998.1)


Summary – Take away

• Different wait events for mutexes

• Important diagnostics to be gathered

• Identifying blockers directly using SQLs

• Potential Solutions for the common mutex problems

• Hot copy mutex


FAQ

• 1. Would there be any blocker for “cursor: pin S” waits?

• 2. What is the difference between using _kgl_debug and dbms_shared_pool.markhot?

• 3. Can this hot copy fix be used for all types of mutex waits?

33

• 3. Can this hot copy fix be used for all types of mutex waits?

• 4. How to monitor the top waits without AWR as it is licensed?

• 5. What is Version Count?


© 2013 oracle corporation –proprietary and confidential 1 · agenda •overview •wait events...

Documents