Sequence Usage Analysis (vseq.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 analyzes sequence usage and configuration by:

• Displaying current usage statistics for all sequences
• Showing configuration flags (active, replenish, wrap, cycle, order)
• Indicating next values and high water marks
• Grouping sequences by owner for organized output

Script

rem  vseq.sql
rem
ttitle 'Sequence Usage'
rem
col sequence_owner format a8 heading 'OWNER'
col sequence_name format a30 heading 'SEQUENCE'
col active_flag format a1 heading 'A'
col replenish_flag format a1 heading 'R'
col wrap_flag format a1 heading 'W'
col cycle_flag format a1 heading 'C'
col order_flag format a1 heading 'O'
col nextvalue format 99999999 heading 'NEXT|VALUE'
col highwater format 99999999 heading 'HIGHWATER'
col cache_size format 9999 heading 'CACHE|SIZE'
rem
break on sequence_owner
rem
select sequence_owner,
       sequence_name,
       decode( active_flag, 'N', null, active_flag ) active_flag,
       decode( replenish_flag, 'N', null, replenish_flag ) replenish_flag,
       decode( wrap_flag, 'N', null, wrap_flag ) wrap_flag,
       decode( cycle_flag, 'N', null, cycle_flag ) cycle_flag,
       decode( order_flag, 'N', null, order_flag ) order_flag,
       nextvalue,
       highwater,
       cache_size
  from v$_sequences
 order by sequence_owner, sequence_name;

Usage

SQL> @vseq.sql

Required Privileges

• SELECT on V$_SEQUENCES (may require SYSDBA privileges)

Sample Output

Sequence Usage

OWNER    SEQUENCE                       A R W C O NEXT VALUE HIGHWATER CACHE SIZE
-------- ------------------------------ - - - - - ---------- --------- ----------
HR       EMPLOYEE_SEQ                   Y           1001       1050        20
         DEPARTMENT_SEQ                 Y             101        150        10
         SALARY_HIST_SEQ                Y           10001      10200        50

SCOTT    EMP_SEQ                        Y             501        550        20
         DEPT_SEQ                       Y              51         60        10

SYS      AUDIT_TRAIL_SEQ                Y       Y   999901     999950        20
         SYSTEM_SEQ                     Y           50001      50100        100

Key Output Columns

• OWNER: Schema that owns the sequence
• SEQUENCE: Name of the sequence
• A (Active): Y if sequence is currently active
• R (Replenish): Y if sequence cache needs replenishing
• W (Wrap): Y if sequence is configured to wrap around
• C (Cycle): Y if sequence cycles when reaching max value
• O (Order): Y if sequence guarantees order in RAC
• NEXT VALUE: Next value that will be returned
• HIGHWATER: Highest value allocated in cache
• CACHE SIZE: Number of values cached in memory

Understanding Sequence Flags

Active Flag (A)

• Y: Sequence is active and can be used
• Blank: Sequence may be inactive or newly created

Replenish Flag (R)

• Y: Cache needs to be replenished
• Blank: Cache has sufficient values

Wrap Flag (W)

• Y: Sequence will wrap to minimum when maximum reached
• Blank: Sequence will error when maximum reached

Cycle Flag (C)

• Y: Sequence is configured to cycle
• Blank: Sequence will not cycle

Order Flag (O)

• Y: Sequence guarantees order in RAC environments
• Blank: No ordering guarantee (better performance)

Sequence Analysis

Cache Efficiency

• High Cache Size: Better performance, may leave gaps
• Low Cache Size: More consistent values, potential bottleneck
• Gap Analysis: HIGHWATER - NEXT VALUE shows unused cached values

Usage Patterns

• Rapid Growth: High difference between current and next values
• Infrequent Use: Small gaps between high water and next value
• Heavy Usage: Large cache sizes and frequent replenishment

Common Use Cases

1. Performance Analysis

   • Identify sequences with inadequate cache sizes
   • Find sequences causing contention
   • Analyze usage patterns

2. Capacity Planning

   • Monitor sequence consumption rates
   • Plan for sequence rollovers
   • Assess cache sizing needs

3. Troubleshooting

   • Investigate sequence-related errors
   • Check for wrap-around issues
   • Validate sequence configuration

Performance Tuning

Cache Size Optimization

-- Increase cache size for high-usage sequences
ALTER SEQUENCE hr.employee_seq CACHE 1000;

-- Reduce cache for infrequently used sequences
ALTER SEQUENCE hr.department_seq CACHE 10;

RAC Considerations

-- Enable ordering for sequences requiring it
ALTER SEQUENCE hr.employee_seq ORDER;

-- Disable ordering for better performance
ALTER SEQUENCE hr.salary_hist_seq NOORDER;

Monitoring Usage

-- Check sequence progression over time
SELECT sequence_owner, sequence_name, last_number
FROM dba_sequences
WHERE sequence_name = 'EMPLOYEE_SEQ';

Common Issues

ORA-08004: Sequence Exceeded MAXVALUE

• Sequence has reached maximum value
• Need to either increase MAXVALUE or enable CYCLE

Sequence Gaps

• Normal behavior with caching
• Increase cache size to reduce frequency
• Consider NOCACHE for critical sequences

RAC Sequence Contention

• Multiple instances competing for sequence values
• Consider using ORDER or increasing cache size
• Evaluate partitioned sequences for extreme cases

Best Practices

1. Cache Sizing

   • Size cache based on usage patterns
   • High-volume sequences: larger cache
   • Low-volume sequences: smaller cache

2. Configuration

   • Use CYCLE carefully (data integrity concerns)
   • Enable ORDER only when necessary (performance impact)
   • Consider MAXVALUE settings for capacity planning

3. Monitoring

   • Regular checks on high-usage sequences
   • Alert when approaching MAXVALUE
   • Track performance impact of sequence operations

Related Scripts

• Sequence Definitions
• Large Usage Sequences
• Modified Sequences