Insight 02.1
Oracle sequences use a cache in memory (SGA) to avoid reading the 'next value' from disk for every single request. A cache value that is too low (the default is often only 20) leads to massive 'row cache objects' enqueues under high load. In high-concurrency environments or when using RAC, a cache value of 1000 or higher is recommended.
The problem: If an instance crashes or the Shared Pool is flushed, the values currently held in the cache are lost. This creates gaps in the numbering sequence, which is fine for technical IDs but may be an issue for accounting purposes. Important: A sequence guarantees uniqueness, but never a gapless or strictly chronological order. A common mistake is to rely on the sequence number for chronological sorting (ORDER BY sequence_id). Because different sessions are assigned different cache blocks, a session that starts later might receive a smaller ID. For chronological queries, you should always use a timestamp instead of the sequence ID. Additionally, using the 'ORDER' flag on sequences can drastically degrade performance in RAC environments.
In summary: Set the cache high for performance, but always rely on timestamps for temporal logic.
Oracle Sequences
Oracle Sequences: Performance boosting through caching and the sorting trap.
Oracle sequences use a cache in memory (SGA) to avoid reading the 'next value' from disk for every single request. A cache value that is too low (the default is often only 20) leads to massive 'row cache objects' enqueues under high load. In high-concurrency environments or when using RAC, a cache value of 1000 or higher is recommended.
The problem: If an instance crashes or the Shared Pool is flushed, the values currently held in the cache are lost. This creates gaps in the numbering sequence, which is fine for technical IDs but may be an issue for accounting purposes. Important: A sequence guarantees uniqueness, but never a gapless or strictly chronological order. A common mistake is to rely on the sequence number for chronological sorting (ORDER BY sequence_id). Because different sessions are assigned different cache blocks, a session that starts later might receive a smaller ID. For chronological queries, you should always use a timestamp instead of the sequence ID. Additionally, using the 'ORDER' flag on sequences can drastically degrade performance in RAC environments.
In summary: Set the cache high for performance, but always rely on timestamps for temporal logic.