Oracle Tips and Tricks — David Fitzjarrell

July 24, 2017

That’s Not A Latch

Filed under: General — dfitzjarrell @ 08:22

"They all looked very much like the residents of any small valley to which you've never been."
-- Norton Juster, The Phantom Tollbooth

While investigating latches another interesting process popped up that’s smaller and lighter, that performs a similar function, called a mutex. So what is a mutex? By definition it’s a mutual exclusion object that locks memory structures to prevent concurrent sessions from changing the same object at the same time, so in at least one way a mutex is like a latch. [Operating systems also have a process called a mutex that behaves in a similar way.] In other ways it isn’t; let’s look into the mutex side of Oracle and see how they differ from latches.

A mutex is smaller than a latch but what does that mean? It’s a reference to the code path each must take to be executed; in terms of size code for a latch is around 110 bytes (which contains anywhere between 150 and 200 instructions) while the code for a mutex is a mere 28 bytes (having around 30 to 35 instructions), making a mutex faster to execute. Being smaller and lighter (gee, I’ve seen that phrase before …) a mutex won’t contain the level of information a latch contains; it won’t have data on who is waiting or how about the length of the wait, it won’t have information on requests and misses but there is sleep data available (how many, how long) and a couple of other details that can help monitor them.

Let’s look at what benefits a mutex can provide:

  • Less potential for false contention.
  • Replace both latches and pins.
  • Mutex structure is located in each child cursor.

Let’s look at each one of those benefits. First is the lower potential for false contention; a latch can protect multiple hash buckets so if two users were searching two different hash buckets the possibility exists that those two buckets could be protected by the same latch and, as a result, the two sessions would contend for access to that latch. With mutexes each hash bucket could easily have it’s own mutex for protection thus eliminating the contention to lock and access the bucket.

Second, mutexes can replace both latches and pins. A mutex can act as a serialization mechanism (just like a latch, restricting access to a single session) and also as a pin (to prevent an object from ‘aging out’ of the relevant cache). A mutex has both an exclusive and a shared mode, and in shared mode multiple sessions can access (using the term ‘reference’) the same mutex and the number of sessions currently referencing a given mutex is known as the ‘reference count’. That information is stored in the mutex. And, like a latch, a mutex can also be held in exclusive mode and the reference count of 1 could indicate an exclusive hold on that mutex. Additionally an object cannot be aged out of a cache until the mutex reference count is 0.

Third, the mutex structure is located in each child cursor handle with the mutex itself acting as the pin structure for the cursor. Changing the pin status is now simply a matter of changing the mutex reference count; this eliminates the need to get the library cache latch making the mutex even more efficient than the latch/pin combination.

So what information is available about mutexes and where can it be found? Oracle provides the V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY views that can be queried to get the following information:


SQL> desc v$mutex_sleep
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MUTEX_TYPE                                         VARCHAR2(32)
 LOCATION                                           VARCHAR2(40)
 SLEEPS                                             NUMBER
 WAIT_TIME                                          NUMBER
 CON_ID                                             NUMBER

SQL> desc v$mutex_sleep_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MUTEX_IDENTIFIER                                   NUMBER
 SLEEP_TIMESTAMP                                    TIMESTAMP(6)
 MUTEX_TYPE                                         VARCHAR2(32)
 GETS                                               NUMBER
 SLEEPS                                             NUMBER
 REQUESTING_SESSION                                 NUMBER
 BLOCKING_SESSION                                   NUMBER
 LOCATION                                           VARCHAR2(40)
 MUTEX_VALUE                                        RAW(8)
 P1                                                 NUMBER
 P1RAW                                              RAW(8)
 P2                                                 NUMBER
 P3                                                 NUMBER
 P4                                                 NUMBER
 P5                                                 VARCHAR2(64)
 CON_ID                                             NUMBER

SQL>

Querying V$MUTEX_SLEEP we see:


MUTEX_TYPE                       LOCATION                                     SLEEPS  WAIT_TIME     CON_ID
-------------------------------- ---------------------------------------- ---------- ---------- ----------
Library Cache                    kglllal3 111                                      5      21885       0
Library Cache                    kglllal1 109                                      1         78       0
Library Cache                    kglhdgn2 106                                     39     136235       0
Library Cache                    kglpnal1  90                                      5      46642       0
Library Cache                    kglhdgn1  62                                      5      35087       0
Library Cache                    kglscn1   18                                      3       9972       0
Library Cache                    kglpin1   4                                       1      25173       0
Library Cache                    kglget2   2                                       2      90986       0
Cursor Parent                    kkscsPruneChild [KKSPRTLOC35]                     4       8852       0
Cursor Parent                    kksfbc [KKSPRTLOC2]                               2      45124       0
Cursor Pin                       kkslce [KKSCHLPIN2]                             296     432125       0
Cursor Pin                       kksfbc [KKSCHLFSP2]                               2       1795       0

12 rows selected.

SQL>

MUTEX_TYPE indicates the action or object the mutex protects, and in the above example there are mutexes protecting library cache objects and acting as cursor pins. LOCATION is the code location where waiters slept for the indicated mutex. SLEEPS is the number of times a requestor had to sleep before getting the mutex and WAIT_TIME is, oddly enough, the wait time in microseconds. CON_ID is the container ID, which is only relevant in 12.1 and later releases; in 11.2 and earlier the CON_ID column does not exist.

V$MUTEX_SLEEP_HISTORY provides more information:


MUTEX_IDENTIFIER SLEEP_TIMESTAMP                                                             MUTEX_TYPE                             GETS     SLEEPS RE
QUESTING_SESSION BLOCKING_SESSION LOCATION                               MUTEX_VALUE              P1 P1RAW                    P2         P3         P4
 P5                                                                 CON_ID
---------------- --------------------------------------------------------------------------- -------------------------------- ---------- ---------- --
---------------- ---------------- ---------------------------------------- ---------------- ---------- ---------------- ---------- ---------- --------
-- ---------------------------------------------------------------- ----------
        71628033 10-JUL-17 07.00.07.021000 AM                                                Cursor Parent                    10          4
     6              133 kkscsPruneChild [KKSPRTLOC35]            0000008500000000         22 00                0          0          0
                                                         0
      3495269867 10-JUL-17 07.00.08.673000 AM                                                Cursor Pin                                1        101
           368               16 kkslce [KKSCHLPIN2]                      0000001000000000          2 00                        0          0          0
                                                                                 0
      2252038564 10-JUL-17 07.00.07.390000 AM                                                Library Cache                            90          1
           135              368 kglhdgn2 106                             0000017000000000          0 000007FF8810FDC0          0          0          0
                                                                                 0
      2252038564 10-JUL-17 07.00.07.390000 AM                                                Library Cache                            90          1
           133              368 kglhdgn2 106                             0000017000000000          0 000007FF8810FDC0          0          0          0
                                                                                 0
      4087692675 10-JUL-17 07.00.07.101000 AM                                                Library Cache                             7          4
             6              251 kglllal3 111                             000000FB00000000          0 000007FF86347838          0          0          0
                                                                                 0
      4039937844 10-JUL-17 07.00.08.581000 AM                                                Library Cache                            22          2
           251              135 kglhdgn2 106                             0000008700000000          0 000007FF86F0C1C0          0          0          0
                                                                                 0
      4039937844 10-JUL-17 07.00.08.581000 AM                                                Library Cache                            22          4
           249              135 kglhdgn2 106                             0000008700000000          0 000007FF86F0C1C0          0          0          0
                                                                                 0
      2912853027 10-JUL-17 07.00.07.277000 AM                                                Cursor Pin                                1         60
           135              368 kkslce [KKSCHLPIN2]                      0000017000000000          2 00                        0          0          0
                                                                                 0
      2912853027 10-JUL-17 07.00.07.277000 AM                                                Cursor Pin                                1         59
           133              368 kkslce [KKSCHLPIN2]                      0000017000000000          2 00                        0          0          0
                                                                                 0
      2034504591 10-JUL-17 07.00.01.555000 AM                                                Library Cache                             3          1
           123                6 kglhdgn2 106                             0000000600000000          0 000007FF86F64210          0          0          0
                                                                                 0
      3323840122 10-JUL-17 07.00.01.548000 AM                                                Library Cache                             3          1
           123                6 kglhdgn2 106                             0000000600000000          0 000007FF86F8B300          0          0          0
                                                                                 0
        71628033 10-JUL-17 07.00.06.980000 AM                                                Cursor Parent                     9          2
   133              364 kksfbc [KKSPRTLOC2]                      0000016C00000000         22 00                0          0          0
                                                         0
      1264706735 10-JUL-17 07.00.01.554000 AM                                                Library Cache                             3          1
             6              123 kglhdgn2 106                             0000007B00000000          0 000007FF86F68DD0          0          0          0
                                                                                 0
       746657336 10-JUL-17 07.00.06.893000 AM                                                Library Cache                            38          1
           364                0 kglpnal1  90                             00                        0 000007FF864AF268          0          0          0
                                                                                 0
...

Some of the same information in V$MUTEX_SLEEP also is present in v$MUTEX_SLEEP_HISTORY but WAIT_TIME is essentially replaced with SLEEP_TIMESTAMP and the number of gets is reported along with additional information on the requesting session and any blocking session for the mutex (Oracle collects that information at the time the mutex is requested; it’s not information stored internally in the mutex structure). ‘Internal use’ information will be found in the P1, P1RAW, P2, P3, P4 and P5 columns; P1, P2 and P3 can contain the hash value of the library cache objects under contention as well as other information. The PARAMETER1-3 columns in V$EVENT_NAME and the P1TEXT, P2TEXT and P3TEXT columns in V$SESSION_WAIT can shed light on what values those columns can hold and their meaning. Since such information is release-specific the following query can help identify what additional information may be available in the V$MUTEX_SLEEP_HISTORY view:


select name, parameter1, parameter2, parameter3
from v$event_name
where name in (select event from v$session_wait where (p1,p2,p3) in (select p1, p2, p3 from v$mutex_sleep_history));

The above query is provided to help identify possible additional data in the V$MUTEX_SLEEP_HISTORY view; as always you should test it on your own systems to see what results are provided.

Since it may not be obvious latches and mutexes are independent mechanisms; a process can hold both a latch and a mutex at the same time.

As of release 11.2.0.x the following latches have been replaced by mutexes:

  • library cache pin allocation
  • library cache lock allocation
  • library cache hash chains
  • library cache lock
  • library cache
  • library cache pin

The library cache waits listed below no longer exist in releases 11.2.0.x and later:

  • latch: library cache
  • latch: library cache lock
  • latch: library cache pin

As good as mutexes are there are still conditions, like excessive hard parsing and poor application cursor management (where cursors are closed after every execution and no cursor caching is in effect), that can cause hash collisions or continuous traversing of library cache hash chains and, thus, contention. On the whole, however, mutexes have improved concurrency and considerably reduced latch and pin contention.

Next time you don’t go somewhere you’ve never been, look carefully. You just might see something new.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: