In an Oracle Real Application Clusters (RAC) environment, batch jobs or maintenance tasks that typically run in minutes can occasionally encounter severe bottlenecks. A common yet elusive culprit is the gc freelist wait event.

The Scenario

A routine batch job—responsible for disabling and enabling constraints across schemas normally finishes within 1 to 2 minutes. Suddenly, the runtime degrades drastically to 2–3 hours.

System performance reports (AWR and ADDM) highlight a massive cluster-level contention event dominating DB Time: gc freelist.

What is the gc freelist Wait Event?

In Oracle RAC, Cache Fusion relies on the Global Cache Service (GCS) to manage block concurrency across all cluster instances. GCS uses Cache Lock Elements (LEs) in the Shared Global Area (SGA) to track the ownership and transfer state of cached data blocks.

• Available lock elements reside in a local structure called the GCS Freelist.
• When a local instance processes high-concurrency DML/DDL or rapid block access, it allocates lock elements from this freelist.
• If lock elements are consumed faster than they can be released or reused, the freelist becomes exhausted.
• Sessions requiring new lock elements are forced to wait on gc freelist while the instance attempts to downgrade or reclaim resources.

Step-by-Step Diagnostic Approach

When diagnosing high gc freelist waits, identify the sessions, SQL statements, and database objects driving the lock consumption.

1. Identify Active Waiting Sessions

SQL

SELECT inst_id, sid, serial#, username, program, module, sql_id, event, state, blocking_instance, blocking_session
FROM gv$session
WHERE event = 'gc freelist';

2. Query Active Session History (ASH) for Objects and SQL

SQL

SELECT ash.con_id, ash.inst_id, ash.sql_id, o.owner, o.object_name, o.object_type, COUNT(*) AS wait_samples
FROM gv$active_session_history ash
JOIN dba_objects o ON o.object_id = ash.current_obj#
WHERE ash.event = 'gc freelist'
GROUP BY ash.con_id, ash.inst_id, ash.sql_id, o.owner, o.object_name, o.object_type
ORDER BY wait_samples DESC;

3. Inspect Current GCS Lock Allocation

Check the default allocation percentage for GCS elements:

SQL

SELECT a.ksppinm AS parameter_name, b.ksppstvl AS current_value
FROM x$ksppi a
JOIN x$ksppsv b ON a.indx = b.indx
WHERE LOWER(a.ksppinm) LIKE '%_gc_element%';

The default setting is typically around 100–105%.

Root Cause Analysis

Under bursty or high-concurrency workloads (such as mass constraint operations, intense DML, or large batch jobs), the fixed ratio of lock elements relative to the buffer cache proves insufficient.

Because the local freelist drains rapidly, new processes stall waiting for resource reclamation rather than doing actual work.

Resolution & Parameter Fix

To resolve GCS lock exhaustion, increase the total lock element pool by adjusting the hidden parameter _gc_element_percent.

Increasing this parameter allocates additional lock elements relative to the buffer cache, providing a larger buffer to absorb concurrency spikes.

Applying the Fix:

Because _gc_element_percent is a static initialization parameter, it requires an instance restart to take effect:

SQL

ALTER SYSTEM SET "_gc_element_percent"=200 SCOPE=SPFILE;

Note: Schedule a maintenance window to restart the RAC instances gracefully (rolling restart supported).

Key Takeaways

• gc freelist indicates GCS resource exhaustion, not necessarily slow network or storage hardware.
• Inspect ASH and AWR to correlate wait bursts with specific jobs or high-volume inserts (e.g., auditing tables, bulk DDL/DML).
• Expanding GCS Lock Elements via _gc_element_percent=200 provides the necessary headroom in RAC environments experiencing high lock concurrency.