Modified Sequence Analyzer (dseqmod.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 sequences that have been modified from Oracle’s default settings, specifically focusing on sequences with ordering enabled or non-standard cache sizes. It helps DBAs identify sequences that may have performance implications or require special attention in RAC environments.

Script

rem  dseqmod.sql
rem
rem  linesize = 80
rem
ttitle 'Changed 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 ( order_flag <> 'N'
      or cache_size <> 20 )
 order by sequence_owner, sequence_name;

Usage

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

-- When prompted, enter parameters:
-- owner: Schema owner (% for all)
-- sequence: Sequence name pattern (% for all)

-- Examples:
Enter value for owner: %
Enter value for sequence: %

-- Check specific schema
Enter value for owner: HR
Enter value for sequence: %

-- Find specific sequence pattern
Enter value for owner: APP
Enter value for sequence: ORDER_%

Parameters

• &owner - Schema owner (supports wildcards)
• &sequence - Sequence name pattern

Required Privileges

• SELECT on DBA_SEQUENCES
• Typically requires DBA role

Sample Output

Changed Sequence Definitions

                                           MIN      MAX
OWNER        SEQUENCE NAME              VALUE     VALUE INC C O CACHE LAST
                                                              SIZE NUMBER
------------ -------------------------- ----- --------- --- - - ----- --------
HR           EMP_ID_SEQ                     1 999999999   1 N Y    50     1234
             DEPT_SEQUENCE                  1 999999999   1 N N   100     5678
             ORDER_NUMBER_SEQ               1 999999999   1 N Y     5     9012

SALES        INVOICE_SEQ                    1 999999999   1 N Y    10    15678
             CUSTOMER_ID_SEQ                1 999999999   1 N N    75    34567

Key Output Columns

• OWNER - Schema owning the sequence
• SEQUENCE NAME - Name of the sequence
• MIN VALUE - Minimum value (usually 1)
• MAX VALUE - Maximum value
• INC - Increment by value
• C - Cycle flag (Y=cycles, N=no cycle)
• O - Order flag (Y=ordered, N=not ordered)
• CACHE SIZE - Number of values cached
• LAST NUMBER - Last generated number

Understanding the Modifications

Default Oracle Settings

• ORDER_FLAG = ‘N’ (not ordered)
• CACHE_SIZE = 20 (default cache)
• CYCLE_FLAG = ‘N’ (no cycling)
• INCREMENT_BY = 1

Why Sequences Are Modified

• Ordering - Required for business logic
• Cache Size - Performance optimization
• Large Cache - Reduce contention in high-volume systems
• Small Cache - Minimize gaps after instance restart

Performance Implications

Ordered Sequences (ORDER_FLAG = ‘Y’)

• Performance Impact - Significant overhead in RAC
• Use Case - When gap-free numbers required
• Alternative - Consider application-level sequencing

Cache Size Considerations

• Large Cache (>20) - Better performance, larger gaps
• Small Cache (<20) - More overhead, smaller gaps
• Zero Cache - Maximum overhead, no gaps

Common Use Cases

Find High-Performance Sequences

SELECT sequence_owner, sequence_name, cache_size
FROM dba_sequences
WHERE sequence_owner NOT IN ('SYS','SYSTEM')
AND cache_size > 100
ORDER BY cache_size DESC;

Identify Ordered Sequences

SELECT sequence_owner, sequence_name, last_number
FROM dba_sequences
WHERE order_flag = 'Y'
AND sequence_owner NOT IN ('SYS','SYSTEM')
ORDER BY sequence_owner, sequence_name;

Check Sequence Usage

-- Find sequences near maximum value
SELECT sequence_owner, sequence_name,
       last_number, max_value,
       ROUND((last_number/max_value)*100, 2) pct_used
FROM dba_sequences
WHERE last_number/max_value > 0.8
ORDER BY last_number/max_value DESC;

RAC Considerations

Ordered Sequences in RAC

-- Check ordered sequences (problematic in RAC)
SELECT sequence_owner, sequence_name,
       'Performance issue in RAC' warning
FROM dba_sequences
WHERE order_flag = 'Y'
AND sequence_owner NOT IN ('SYS','SYSTEM');

Cache Size Recommendations

• High Volume - Cache size 1000+ for maximum performance
• Medium Volume - Cache size 100-500
• Low Volume - Default cache size 20
• Ordered Requirements - Consider ORDER flag necessity

Maintenance and Monitoring

Reset Sequence Values

-- Reset sequence to start from specific value
ALTER SEQUENCE owner.sequence_name
RESTART START WITH 10000;

Modify Cache Settings

-- Increase cache for better performance
ALTER SEQUENCE owner.sequence_name
CACHE 1000;

-- Disable caching (not recommended)
ALTER SEQUENCE owner.sequence_name
NOCACHE;

Monitor Sequence Gaps

-- Check for sequence gaps after instance restart
SELECT sequence_name,
       last_number,
       cache_size,
       'Potential gap: ' || cache_size || ' numbers' gap_info
FROM dba_sequences
WHERE cache_size > 20
AND sequence_owner = '&owner';

Best Practices

Performance Optimization

1. Use large cache sizes for high-volume sequences
2. Avoid ORDER flag unless absolutely required
3. Monitor sequence usage patterns
4. Consider NOCYCLE for most sequences

Design Guidelines

• Set cache size based on usage volume
• Document business reasons for ordering
• Plan for maximum value limits
• Consider impact of instance restarts

Troubleshooting

Sequence Exhaustion

-- Find sequences approaching maximum
SELECT sequence_owner, sequence_name,
       last_number, max_value,
       max_value - last_number remaining_numbers
FROM dba_sequences
WHERE (max_value - last_number) < cache_size * 10
AND sequence_owner NOT IN ('SYS','SYSTEM');

Gap Analysis

-- Calculate potential gaps
SELECT sequence_name,
       cache_size,
       'Max gap on restart: ' || cache_size gap_analysis
FROM dba_sequences
WHERE cache_size > 50
ORDER BY cache_size DESC;

Related Scripts

• Sequence Definitions (dseq.md) - All sequence details
• Large Usage Sequences (../administration/dseqbig.md) - High usage analysis
• Database Objects (dobject.md) - Object overview

Advanced Analysis

Sequence Usage Patterns

-- Monitor sequence progression over time
WITH seq_snapshot AS (
  SELECT sequence_name, last_number,
         SYSDATE snapshot_time
  FROM dba_sequences
  WHERE sequence_owner = '&owner'
)
SELECT sequence_name, last_number,
       LAG(last_number) OVER (ORDER BY sequence_name) prev_number,
       last_number - LAG(last_number) OVER (ORDER BY sequence_name) usage_delta
FROM seq_snapshot;

Performance Impact Assessment

-- Estimate performance impact of ordering
SELECT s.sequence_name,
       s.order_flag,
       s.cache_size,
       CASE
         WHEN s.order_flag = 'Y' THEN 'High overhead in RAC'
         WHEN s.cache_size < 20 THEN 'Potential contention'
         ELSE 'Optimal'
       END performance_assessment
FROM dba_sequences s
WHERE s.sequence_owner = '&owner'
ORDER BY s.order_flag DESC, s.cache_size;