Well my friend ashish (A dangerous DBA at koenig) was literally asking me everyday to post some stuff on why oracle is switching to mutexes, so here i come with my post on mutexes and latches too, I have made this post simpler while not going much into details so that beginners can also cherish every moment too and also its a nice sunshine in new delhi despite its cold here, but no coffee here its sad though.. Here we go ..
As we know that mutex is a mutual exclusion and they are the concept of multiple threads, both latches and mutexes are used for low level locking, mutexes again are used to serialize access to shared structures, most importantly they can be taken in shared or exclusive mode and also getting a mutex can de done in wait or no-wait mode.
Let me post few of my notes on mutexes which i discussed with Rajeev Ranjan and also from Tanel Poder at the oracle forum :=
1) The advantages over latches is that mutexes requires less memory and are faster to get and release.
2) Why faster ? Because the code path to get and release is shorter
3) Acquired in the same mode of immediate or willing to wait as latches ? The answer is (YES)
4) To me and Mr. RANJEEV RANJAN it seams that since mutexes can do the same things as latches do by using less memory and in a more performant way, there are already enough good reason for using them....
Some notes from TANEL PODER (An Oracle GURU)
1) Library cache latching is still needed for parsing etc, the mutexes address only the pinning issue in library cache
2) Mutexes are currently used for library cache cursors (not other objects like PL/SQL stored procs, table defs etc)
3) As mutexes are a generic mechanism (not library cache specific) they're used in V$SQLSTATS underlying structures too
4) When mutexes are enabled, you won't see cursor pins from X$KGLPN anymore (as X$KGLPN is a fixed table based on the KGL pin array which wouldn't be used for cursors anymore)
AS for now few DBA's must be still not be convinced i guess if you really know how object handles,cursors,child cursors, pinning work in the shared pool, for them i have written the following lines
# The actual benefit comes is that for each child cursor handle mutex actually acts as a cursor pin structure, so if you have a cursor open (cached in session cursor cache) you dont need to get the library cache latch (which was previosuly needed for chaning cursor pin status), but you can modify the cursor's mutex refcount directly (with help of pointers in open cursor state area in sessions UGA). Therfore you have much higher scalability when pinning/unpinning cursors (no library cache/library cache pin latching needed, therfore no false contention) and no separate pin structure need to be allocated/maintained.
If you have understood what i just said above, yes it can be leading to an assumption to a false contention thing like suppose there are lots of sessions looking for statements in the shared pool then the library cache will come under contention, now this is a false contention if we just look at if sessions are looking for soft parsed cursor then its a good thing but because the looking mechanism, latch is not available so its actually under contention which will be removed with the help of mutexes as they will be allocated as per structure so there wont be any starvation issue like in latches.
In Oracle 10.2.0.2+ the library cache pin latch usage was replaced with mutexes whenever _kks_use_mutex_pin was true, also few other things like V$SQLSTATS arrays and parent cursor examination were protected by mutexes.
In 11g all library cache related latches except “library cache load lock” are gone and corresponding operations are protected by mutexes instead. The “library cache” latches have been replaced by “Library Cache” mutexes for example.
The proof is
SQL> select name from v$latch where lower(name) like '%library%';
NAME
----------------------------------------------------------------
library cache load lock
At the end of this post i would again like to write some basic concepts if some of you need a revision sort of thing ...
Mutexes, Yes they have less potential for false contention because latches typically protect multiple objects. When a latch protects one or more hot objects, the latch itself becomes a serialization point when accessing any of the objects protected by that latch. This can be a false contention point where the contention is for protection mechanism (LATCH) rather than the target object, Because mutexes can be created for each structure protected false contention is less likely.
A mutex can ce concurrently referenced by many sessions, provided all sessions refer to it in S (Shared mode). Total numver of sessions referencing a mutex in S mode is called reference count (REF COUNT). For exclusive it is X (exclusive mode) which can be held by only 1 session. Mutexes have a dual nature, they can act as a serialization mechanism like latch and also a pin (for eg:= preventing an object from aging out). the ref count of a mutex is a replacement for library cache pin, instead of each session creating and then deleting a library cache pin when executing a cursor, each session increments and decrements the ref count (so the ref count replaces n distinct pins).... Have a nice day
--------------------------------------------------------------------------------------------------------
Related links :
LATCHES LOCKS PINS AND MUTEXES
LIBRARY CACHE LOCK/PIN DEMONSTRATED
HOME
LATCHES LOCKS PINS AND MUTEXES
MUTEXES AND LATCHES
PARENT AND CHILD CURSORS IN ORACLE
QUICK OVERVIEW OF SHARED POOL TUNING
SHARED POOL ARCHITECTURE
SQL PLAN MANAGEMENT – ORACLE 11g : Part-1
http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%E2%80%93%20ORACLE%20%2011g%20%3A%20Part-1
SQL PLAN MANAGEMENT – ORACLE 11g : Part-2
http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%20%E2%80%93%20ORACLE%2011g%20%3A%20Part-2
CURSOR_SHARING DEMYSTIFIED
CURSOR_SHARING DEMYSTIFIED
LIBRARY CACHE LOCK/PIN DEMONSTRATED
SHARED POOL TUNING - A DEMONSTRATION
No comments:
Post a Comment