Large Usage Sequence Analysis (dseqbig.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script identifies database sequences that have reached a high usage threshold, helping prevent sequence exhaustion issues before they occur. It analyzes sequence definitions and current values to find sequences approaching their maximum limits, which is critical for maintaining application availability.

Script

rem  dseqbig.sql
rem
rem  linesize = 80
rem
ttitle 'Large Usage Sequence Definitions'
rem
col sequence_owner format a12 heading 'OWNER'
col sequence_name format a25 heading 'SEQUENCE NAME'
col min_value format 9999 heading 'MIN|VALUE'
col max_value format 999999999 heading 'MAX|VALUE'
col increment_by format 999 heading 'INC'
col cycle_flag format a1 heading 'C'
col order_flag format a1 heading 'O'
col cache_size format 9999 heading 'CACHE|SIZE'
col last_number format 99999999 heading 'LAST|NUMBER'
rem
break on sequence_owner
rem
select sequence_owner, sequence_name, min_value, max_value,
       increment_by, cycle_flag, order_flag, cache_size, last_number
  from sys.dba_sequences
 where sequence_owner like upper('&owner')
   and sequence_name like upper('&sequence')
   and last_number >= &last_number
 order by sequence_owner, sequence_name;

Usage

-- Run the script in SQL*Plus or SQLcl
@dseqbig.sql

-- When prompted, enter:
-- owner: Schema owner pattern (% for all)
-- sequence: Sequence name pattern (% for all)
-- last_number: Minimum current value threshold (e.g., 1000000)

-- Example
Enter value for owner: %
Enter value for sequence: %
Enter value for last_number: 1000000

Parameters

• &owner: Schema owner pattern (use % for all schemas)
• &sequence: Sequence name pattern (use % for all sequences)
• &last_number: Minimum current value to report (threshold)

Required Privileges

• SELECT on SYS.DBA_SEQUENCES
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

Large Usage Sequence Definitions

OWNER        SEQUENCE NAME             MIN   MAX         INC C O CACHE LAST
                                       VALUE VALUE           SIZE NUMBER
------------ ------------------------- ----- ----------- --- - - ----- --------
SALES        CUSTOMER_ID_SEQ               1  2147483647   1 N N    50  1456789
SALES        ORDER_ID_SEQ                  1  9999999999   1 N N   100  2345678
HR           EMPLOYEE_ID_SEQ             100  9999999999   1 N N    20  1123456
INVENTORY    PRODUCT_ID_SEQ                1  2147483647   1 N N    25  1987654
FINANCE      TRANSACTION_ID_SEQ            1  9999999999   1 N N   200  5678901

Key Output Columns

• OWNER: Schema that owns the sequence
• SEQUENCE NAME: Name of the sequence
• MIN VALUE: Minimum value the sequence can generate
• MAX VALUE: Maximum value the sequence can reach
• INC: Increment value for each NEXTVAL call
• C: Cycle flag (Y = cycles after reaching max, N = errors)
• O: Order flag (Y = guaranteed order in RAC, N = no guarantee)
• CACHE SIZE: Number of values cached in memory
• LAST NUMBER: Current highest value generated

Understanding Sequence Risk

Critical Risk Indicators

• LAST NUMBER approaching MAX VALUE: Sequence exhaustion risk
• Non-cycling sequences (C = N): Will error when max reached
• High increment values: Faster approach to maximum
• Small cache sizes: Performance impact on high-usage sequences

Calculating Remaining Values

-- Approximate remaining values
SELECT sequence_name,
       max_value - last_number as remaining_values,
       CASE
         WHEN max_value - last_number < 1000000 THEN 'CRITICAL'
         WHEN max_value - last_number < 10000000 THEN 'WARNING'
         ELSE 'OK'
       END as risk_level
FROM dba_sequences
WHERE last_number >= 1000000;

Performance Analysis

High-Usage Sequence Characteristics

• Large LAST NUMBER values: Heavily used sequences
• Small CACHE SIZE with high usage: Potential performance bottleneck
• Order flag Y in RAC: Performance impact for globally ordered sequences

Optimization Opportunities

• Increase cache size for frequently used sequences
• Consider sequence partitioning for extremely high-volume scenarios
• Review increment values for batch processing efficiency

Common Use Cases

Proactive Monitoring

-- Weekly check for sequences approaching limits
@dseqbig.sql
-- Enter high threshold like 100000000
-- Review sequences nearing maximum values

Capacity Planning

-- Identify high-growth sequences
@dseqbig.sql
-- Track growth rates over time
-- Plan sequence modifications before exhaustion

Performance Analysis

-- Find sequences with small caches and high usage
@dseqbig.sql
-- Look for performance optimization opportunities

Troubleshooting Sequence Issues

Sequence Approaching Maximum

1. Immediate Solutions:

   -- Increase maximum value
   ALTER SEQUENCE schema.sequence_name MAXVALUE 99999999999;
   
   -- Enable cycling (if business logic allows)
   ALTER SEQUENCE schema.sequence_name CYCLE;

2. Long-term Solutions:

   -- Create new sequence with larger range
   CREATE SEQUENCE schema.new_sequence_name
   START WITH 1
   MAXVALUE 999999999999999999999999999
   INCREMENT BY 1
   CACHE 100;

Performance Optimization

1. Increase Cache Size:

   -- Reduce contention in high-concurrency scenarios
   ALTER SEQUENCE schema.sequence_name CACHE 1000;

2. Remove Order Requirement (RAC environments):

   -- Improve performance by removing global ordering
   ALTER SEQUENCE schema.sequence_name NOORDER;

Sequence Exhaustion Recovery

1. Reset Sequence (if business allows):

   -- Drop and recreate with new range
   DROP SEQUENCE schema.old_sequence;
   CREATE SEQUENCE schema.new_sequence START WITH 1;

2. Application Changes:

   • Modify application to handle larger sequence values
   • Consider alternative ID generation strategies
   • Implement sequence cycling logic if appropriate

Advanced Analysis

Growth Rate Calculation

-- Track sequence growth over time
SELECT sequence_name, last_number, SYSDATE
FROM dba_sequences
WHERE sequence_name = 'YOUR_SEQUENCE';

-- Calculate daily growth rate
-- (Run periodically and compare results)

Cross-Reference with Application Tables

-- Find which tables use specific sequences
SELECT table_name, column_name
FROM dba_tab_columns
WHERE data_default LIKE '%YOUR_SEQUENCE%';

Related Scripts

• Sequence Definitions (dseq.sql) - Complete sequence analysis
• Object Count Analysis (objcnt.sql) - Database object statistics
• Schema Analysis (dobject.sql) - Object dependency analysis
• User Account Analysis (duser.sql) - Schema ownership verification