In an Oracle 11.2.0.4 RAC environment, a spike in database sessions—reaching approximately 80—was observed between 08:45 and 08:55 on December 23. The root cause was traced to the SQL statement:

SELECT TIMEKEYID.NEXTVAL FROM DUAL;

AWR reports from the same hour revealed this query executed over 510,000 times. The associated sequence was defined as:

CREATE SEQUENCE MODMGR.TIMEKEYID
  START WITH 1000
  MAXVALUE 999
  MINVALUE 0
  CYCLE
  CACHE 100
  ORDER;

Analysis using DBA_HIST_ACTIVE_SESS_HISTORY confrimed that the application module BidmMES began issuing this query at high frequency around 08:49. The sequence’s small MAXVALUE (999) combined with CYCLE caused rapid exhaustion of cached values. Each cache refill required dictionary updates under ORDER mode, triggering enq: SV – contention due to serialization across RAC instances.

Root Cause

The ORDER clause forces global coordination in RAC to guarantee sequence value ordering, which inherently limits scalability. Frequent cache exhaustion (due to low MAXVALUE and modest CACHE size) amplified contention during metadata updates.

Resolution

The sequence was altered to increase both range and cache size:

ALTER SEQUENCE modmgr.TIMEKEYID MAXVALUE 9999 CACHE 1000;

This reduced the frequency of cache reloads and associated dictionary updates. Since the business logic concatenated sequence values as three-digit strings (e.g., '2023-12-23 08:49:01' || LPAD(seq, 3, '0')), the increased MAXVALUE necessitated truncation or padding in application code to maintain format consistency.

Experimental Validation

To reproduce the issue, a test sequence was created:

CREATE SEQUENCE b7dba.seq_test
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 0
  CYCLE
  CACHE 10
  ORDER;

A PL/SQL procedure consumed the sequence:

CREATE OR REPLACE PROCEDURE p_seq_test IS
  seq_val NUMBER;
BEGIN
  SELECT seq_test.NEXTVAL INTO seq_val FROM DUAL;
END;

Four hundred concurrent jobs were launched via DBMS_JOB, each invoking p_seq_test every second. Varying the CACHE size demonstrated:

• NOCACHE: Severe enq: SV contention.
• CACHE 2 or 10: High contention persisted.
• CACHE 100 or 1000: Contention significantly reduced but not eliminated due to ORDER.

Key Observations

• enq: SV – contention is primarily caused by the ORDER attribute in RAC, not solely by small cache sizes.
• In RAC, NOORDER allows each instance to manage its own cache segment, greatly improving scalability.
• However, if strict sequence ordering is required (e.g., for time-based composite keys), NOORDER is not viable.
• Increasing CACHE under ORDER mitigates—but does not eliminate—contention by reducing dictionary update frequency.
• The MAXVALUE must be sufficiently large to support a meaningful CACHE size; otherwise, cycling occurs too frequently.