How Hotcopy Can Reduce Oracle library cache: mutex X Contention

Background

When a SQL statement or object is accessed repeatedly, excessive soft parses can lead to heavy contention on the library cache: mutex X in Oracle. This contention slows down both hard and soft parsing because the mutex protects the hash‑bucket and handle structures in the shared pool.

Library Cache Structure

Compiled SQL objects are stored in the library cache as handles organized in a hash table. Each bucket contains a list of handles, and each handle may have multiple children (cursors). The lookup process works as follows:

Oracle computes a hash of the SQL text.

The hash determines the bucket (e.g., bucket 6).

All handles linked to that bucket are scanned to find a matching handle (Handle X).

If a matching handle is found, its children are examined to locate a reusable cursor.

If no matching child exists, a new child is created (hard parse).

If no handle matches the hash, a new handle and its first child are created, also resulting in a hard parse.

Why Lock Protection Is Needed

Without locking, concurrent processes could create duplicate handles or children for the same SQL, leading to inconsistent state. Therefore, a mutex protects the search‑and‑modify operations on each bucket/handle. In Oracle 10g the protection is a latch covering many buckets; in 11g and later it is a finer‑grained mutex per bucket/handle.

Identifying the Contention Source

Two queries help determine whether the wait occurs on a bucket or a handle:

select KGLNAHSH, substr(KGLNAOBJ, 1, 30) Name,
       KGLHDNSP Namespace, KGLNAHSV Hash
from x$kglob where KGLHDBID = &p1;

If this returns rows, the contention is on the bucket (handle search). A second query checks for handle‑level contention:

select kglnaown, kglnaobj
from x$kglob
where kglnahsh = &p1;

When the returned handle belongs to a specific cursor or object, the mutex wait is handle‑level.

Common Causes of library cache: mutex X Contention

Hard parses – creating new handles or children consumes time and holds the mutex longer.

CPU or OS resource shortage – processes cannot release the mutex promptly.

Long‑running mutex holders – need to be diagnosed via error stacks or process dumps.

Oracle bugs .

Hotspot SQL/object – even soft parses become serialized when many sessions repeatedly access the same handle.

Mitigation Strategies

Before applying hotcopy, try the following:

Check for connection storms that cause excessive PL/SQL execution.

Reduce high version counts that increase child traversal time.

Work with developers to vary SQL text (add spaces or comments) so identical statements hash to different handles.

Increase session_cache_cursors so child lookups stay in PGA.

If these do not help, hotcopy can be used.

Hotcopy Concept

Hotcopy creates multiple copies of a hot object (cursor or object) by adding the process ID (PID) to the hash calculation. This effectively multiplies the number of handles for the same SQL, turning a hotspot into a larger pool of resources and reducing mutex wait time. The feature was introduced by Oracle patches 9239863 and 9282521 and is available natively in 11.2.0.2 and later; older versions require the patches and hidden parameters.

Implementation Steps

1. Set the hidden parameter _kgl_hot_object_copies to the desired number of copies (e.g., 4).

2. Use the standard API (available from 11.2 onward) to enable hotcopy for a specific cursor or object. The full hash can be obtained from X$KGLOB.KGLNAHSV.

3. Re‑run the workload and observe the effect.

Experiment

A test was performed with many sessions repeatedly executing the same SQL. Initial ASH data showed library cache: mutex X as the top wait event, with a single P1 value pointing to a specific cursor handle (bucket 14503).

After setting _kgl_hot_object_copies=4 and enabling hotcopy via the API, the same load was applied again. ASH now showed the mutex wait spread across multiple P1 values, indicating that the same cursor was hashed to different handles.

Library cache dumps confirmed the creation of four additional handles for the same object. The new AWR report showed the mutex wait time dropping from 1882 seconds to 942 seconds – a roughly 50% reduction.

Conclusion

Even soft parses involve serialized searches of handles and children, which can become a hotspot under high concurrency. Hotspot mutex X contention can be mitigated with hotcopy, but only when the contention is purely due to repeated access to the same object. Comprehensive performance troubleshooting still requires gathering evidence, understanding Oracle’s internal structures, and building a logical “story” to guide further analysis.