library cache mutex x

Reducing “library cache: mutex X” concurrency with dbms_shared_pool.markhot

Mutexes or mutual exclusion algorithms are lighter and more granular concurrency mechanism than latches and are used in concurrent programming to avoid the simultaneous use of a common resource.

Oracle started using mutexes from 10g for certain operations in the library cache but from 11g all library cache latches were replaced by mutexes which lead to the common to all DBAs “library cache: mutex X” concurrency event:

The irony here is that the reason for obtaining a mutex is to ensure that certain operations are properly managed for concurrency but the management itself leads to concurrency. These are the top 3 differences between mutexes and latches:

– A mutex can protect a single structure, latches often protect many structures
– A mutex get is about 30-35 instructions in the algorithm, compared to 150-200 instructions for a latch get
– A mutex is around 16 bytes in size, compared to 112-200 bytes for a latch

Last month, I found a very interesting and detailed article by Anju Garg on latches and mutexes. It is worth reading.

The aim of this post is to suggest on how to proceeded if you hit the “library cache: mutex X” concurrency event which simply means that a library cache operation is being performed and Oracle is trying to get the library cache mutex in eXclusive mode.

Do the following:

1. Run a query against the x$kglob table to find the names of the objects being pinned most of the time. x$kglob is the resource structure table for library cache locks and pins. Jagjeet’s note gives a very good example.

2. If you have issues with PL/SQL packages and procedures, use dbms_shared_pool.markhot() to mark them as hot. As suggested by Andrey Nikolaev, Oracle creates multiple copies of marked objects in the library cache, but frequently uses only one of them. That is why, in order to achieve uniform distribution, mark the PL/SQL objects as hot immediately after the instance restart.

The documentation for the dbms_shared_pool.markhot procedure can be found here.

3. If the issue is sequence related, then either verify with Oracle that it is not a bug, get rid of the sequence if possible or at least modify its parameters (cache, order, etc.).

As you can see from Dom Brook’s article, issues are often package related. Have a look at my screenshot from ASH. The data source is V$ACTIVE_SESSION_HISTORY.

You may review on Metalink “Waitevent: library cache: mutex X” (Document ID 727400.1) for the list of known Oracle bugs but for all I have seen, sometimes patches do not help, so finding a workaround is all the DBA is left with. An ASH report will be very helpful:

If you see “library cache: mutex X” as a top event, note that ADDM will first find that there is contention for latches related to the shared pool that were consuming significant
database time (no surprise). For the top SQL statements, ADDM will claim something like “Waiting for event library cache: mutex X in wait class ‘Concurrency’ accounted for 97% of the database time spent in processing the SQL statement with SQL_ID d2svmdafangyq.” Look then at the “Rationale” sections and hunt for the names of the PL/SQL packages ��

Tuesday, November 1, 2016

«library cache: mutex X» and Application Context

Heavy Event: «library cache: mutex X» is observed when Application Context is frequently changed. The application is using Oracle Virtual Private Database to regulate data access with driving application context, which determines which policy group is in effect for each use case.

In this Blog, Application Context is used as a concrete case to discuss Oracle «library cache: mutex X».

Note: All tests are done in Oracle 12.1.0.2 on AIX, Solaris, Linux with 6 physical processors.

1. Test

Run the appended Test Code by launching 4 Jobs:
exec ctx_set_jobs(4);

Monitor Job sessions:
Pick idn (P1): 1317011825, and query v$db_object_cache:
It shows that «library cache: mutex X» is on application context: TEST_CTX, and PINNED_TOTAL is probably increased for each access.
Although TEST_CTX is a local context and its values is stored in the User Global Area (UGA), the content of «library cache: mutex X» is globally on its definition.
After test, clean-up all jobs by:
exec clean_jobs;

2. Mutex Contention and Performance

Run the test, monitor Mutex Contention and Performance:
The above CallStack shows that kgxExclusive is triggered by kglpin via kglGetMutex.

Solaris «prstat -mL» show about 30% percentage of time the process has spent sleeping (SLP).

3. Hot library cache objects

Learning from Blog: Divide and conquer the true mutex contention
«library cache: mutex X» can be allevaited by creating multiple copies of hot objects, which are controlled by two hidden parameters:
Configure two hidden parameters:
PUBLIC SYNONYM (namespace=1) ‘PLITBLM’ is added here to show multiple library cache objects can be specified in _kgl_debug. PLITBLM is package for PLSQL Index TaBLe Mangement, i.e PLSQL Collections (Associative Arrays, Nested Table, Varrays). All its implementations are through c interface.

library cache object NAMESPACE number, NAMESPACE name and TYPE name can be listed by following queries («_kgl_debug» and dbms_shared_pool.markhot accept number as NAMESPACE):
Run the same test:
Solaris «prstat -mL» show almost 100% percentage of time the process has spent in user mode (USR).

Try with official API in dbms_shared_pool, it seems that NAMESPACE: ‘APP CONTEXT’ not yet supported.
Comparing «_kgl_debug» and markhot, it seems that «_kgl_debug» is persistent after DB restart, but not always stable after DB restart. Several sessions can still contend for the same library cache objects without creating/using HOT objects.

Whereas markhot seems stable after DB restart, but not always persistent after DB restart. Moreover, markhot does not support all NAMESPACEs of library cache objects.

When a SYNONYM is marked HOT, it can encounter core dump with Error:
ORA-00600: internal error code, arguments: [kgltti-no-dep1]
with CallStack:
This error is addressed by Oracle MOS:
ORA-00600 [kgltti-no-dep1] When Synonym Marked Hot (Doc ID 2153847.1)

4. V$MUTEX_SLEEP_HISTORY

V$MUTEX_SLEEP_HISTORY displays time-series data. Each row in this view is for a specific time, mutex type, location, requesting session and blocking session combination. The data in this view is contained within a circular buffer, with the most recent sleeps shown (Oracle V$MUTEX_SLEEP_HISTORY).

Two fields are documented as: It seems that GETS is an instance-wide accumulated historized data, whereas SLEEPS is per REQUESTING_SESSION for a specific time, mutex type, location.

We can try to edit a query to reveal Mutex contention details. Column SLEEPS and GETs_Per_MS are the points to monitor.

5. Code Path of «library cache: mutex X»

Run Application Context settings 1000 times and at the same time dtrace its SPID: 1217.

It prints out 29 «kgxExclusive» callstacks (Top 3 callstacks are listed at first). Editing them together, we can build up a small dictionary of «library cache: mutex X» to lookup different occurrences and frequencies of mutex X.

The test (on a fresh started instance) shows that ‘TEST_CTX’ was pinned 1000 times for 1000 executions.

1000 Application Context settings are implemented by 1000:

6. Mutex vs. Latch

Latch is an instance-wide centralized locking mechanism, whereas Mutex is a distributed locking mechanism, directly attached on the specific shared memory data structures. That is why there exists v$latch (v$latch_children) for all Latches, whereas Mutex is exposed as V$DB_OBJECT_CACHE.hash_value. Latch is pre-defined and limited, whereas Mutex is dynamically created/released when requested.

Blog: Reducing «library cache: mutex X» concurrency with dbms_shared_pool.markhot lists top 3 differences between mutexes and latches:
Mutex looks about 5 times slimmer and hence hopefully proportionally faster than Latch.

Further deep discussion can be found in Blog: LATCHES, LOCKS, PINS AND MUTEXES

7. Mutex Contention Test on Linux

Run the test on Linux, the same «library cache: mutex X» contentions can be observed.

Pick one Oracle session’s spid, for example, 16060.
Run Command strace, We can see continues output of lines beginning with semtimedop: Refer to Linux Documentations: timespec.tv_nsec = 10000000 ns (0.011305 Second) looks like CFS Scheduler Time Slice as 10ms.

We can try to simulate Oracle ORA-07445 by: We get database alert log and incident file of killed session:

8. No Read Consistency in Application Context (Addendum 2017.06.06)

Modify above local context as global context, and start a job to update context value by:
And then query systimestamp and global context by:
The output shows that systimestamp satisfies Read Consistency, but global context has two or more different values, hence global context does not guarantee Statement-Level Read Consistency.

Even for local context, Blog: Gotcha: Application Contexts demonstrated Non Read Consistency.

Oracle Database Tips by Donald BurlesonMarch 10, 2016

Question: What does the Library cache: mutex x concurrency wait signify? Can you explain how to reduce Library cache mutex x waits? My AWR report shows high counts for «library cache mutex x» events.

Answer: Mutexes are objects that exist within the operating system to provide access to shared memory structures. They are similar to latches, which will be covered in following chapters, as they are serialized mechanisms used to control access to shared data structures within the Oracle SGA. See my notes here on mutexes.

High waits for library cache mutex x can be due to:

• Not pinning hot PL/SQL with dbms_shared_pool.markhot.
• A too-small value for shared_pool_size (memory_target).
• Setting cursor_sharing=similar (which should be changed to exact or force).
• Settings for session_cached_cursors.

Here we run a ASH query to see the library cache mutex x events:

select *
from
(select
event,
count(1)
from
v$active_session_history
where
sample_time > (sysdate — 20/1440)
group by
event
order by 2 desc)
where rownum

We can also run this query to get the P1 (references the object) and counts:

select
event,
p1,
count(1)
from
v$active_session_history
where
sample_time > (sysdate — 20/1440)
and
event = ‘library cache: mutex X’
group by
event, p1
order by 3;

EVENT P1 COUNT(1)
—————————————- ———- ———-
library cache: mutex X 421399181 1
library cache: mutex X 3842104349 1
library cache: mutex X 1412465886 297
library cache: mutex X 2417922189 50615

In plain English, the Library cache: mutex x event simply means that Oracle is performing a library cache operation. The PL/SQL (procedures, packages) that are the target for the «library cache mutex x» can be pinned into the library cache using the dbms_shared_pool.markhot procedure, thus reducing the mutex events for the object.

exec dbms_shared_pool.markhot(schema=>’SYS’,objname=>’DBMS_RANDOM’,NAMESPACE=>1);
exec dbms_shared_pool.markhot(schema=>’SYS’,objname=>’DBMS_RANDOM’,NAMESPACE=>2);
exec dbms_shared_pool.markhot(schema=>’SYS’,objname=>’DBMS_OUTPUT’,NAMESPACE=>1);
exec dbms_shared_pool.markhot(schema=>’SYS’,objname=>’DBMS_OUTPUT’,NAMESPACE=>2);

Above, we have marked the dbms_random package in the library cache.

When you see high «library cache mutex x» events, you will want to see which library cache objects are the target of the operation. This query will expose the mutex target:

select *
from
(select
case when (kglhdadr = kglhdpar)
then ‘Parent’
else ‘Child ‘||kglobt09 end cursor,
kglhdadr ADDRESS,
substr(kglnaobj,1,20) NAME,
kglnahsh HASH_VALUE,
kglobtyd TYPE,
kglobt23 LOCKED_TOTAL,
kglobt24 PINNED_TOTAL,
kglhdexc EXECUTIONS,
kglhdnsp NAMESPACE
from
x$kglob
— where kglobtyd != ‘CURSOR’
order by
kglobt24 desc)
where
rownum

References:

— MOSC: «Wait event: library cache: mutex X» (MOSC Document ID 727400.1)

— MOSC Bug 20879889 — Fixed in 11.2.0.4

— MOSC Patch 20879889: INSERT INTO MV LOG LEAVING TOO MANY OPEN CURSORS AFTER UPGR TO 11.2.0.4

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

and include the URL for the page.