APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
PURPOSE
This paper is intended to explain how sequences behave in a RAC environment
SCOPE
DBA and system designers
DETAILS
1. Sequence setup
Different sequence setups are available.
a. CACHE + NOORDER
This setting has the least performance impact on RAC, and it is the default when creating a new sequence without options. Each instances caches a distinct set of numbers in the shared pool. Sequences will not be globally ordered, and bigger gaps can occur when the shared pool is refreshed (e.g. instance shut down) like on single instance databases.
b. CACHE + ORDER
Each instances caches the same set of numbers. Ordering of sequence numbers is guaranteed, and bigger gaps can occur. Performance is better than with NOCACHE sequences. Gaps in the sequence numbering occur when the sequence cache is lost e.g. any shared pool flush or instance shutdown like an single instance databases.
c. NOCACHE + NOORDER
Use this setting when e.g. government regulations or laws legally require sequence numbers with limited gaps (see point 2). Ordering is not guaranteed. It has a better performance than NOCACHE / ORDER.
d. NOCACHE + ORDER
Use these settings when ordered sequences are required; no gaps will occur (except for point 2 examples) and ordering is guaranteed. This setting though has the most negative performance impact on RAC.
2. Gap in sequences
If your application can never lose sequence numbers, then you cannot use Oracle Database sequences, and you may choose to store sequence numbers in database tables. Be careful when implementing sequence generators using database tables. Even in a single instance configuration, for a high rate of sequence values generation, a performance overhead is associated with the cost of locking the row that stores the sequence value.
Gap sequences are always possible even on non RAC databases for any kind of sequence setup, e.g.
a. when any kind of failure (like ora-600) or transaction rollbacks occur
1. user A want a nextval and get 111
2. user B want a nextval and get 112
3. user B commit
4. user A session failed and/or his transaction is rollbacked.
5. user C want a nextval and get 113. There will be a gap from 110 to 112.
b. when the sequence caching is used and the cached values are flushed from the shared pool. The same happen in RAC as in single instance databases. Any flush on any shared pool is enough to invalidate the cache value on RAC systems.
3. Performance impact when using CACHE or NOCACHE
When caching is used, then the dictionary cache (the row cache) is updated only once with the new high watermark, e.g. when a caching of 20 is used and a nextval is requested the first time, then the row cache value of the sequence is changed in the dictionary cache and increased by 20. The LAST_NUMBER of the DBA_SEQUENCES get increased with the cache value or 20. The extracted 20 values, stored in the shared pool, will be distributed to the sessions requesting the nextval of it. View v$_sequences permits to know whether the sequence has been cached in the shared pool or not.
When no caching is used, then the dictionary cache has to be updated for any nextval request. It means the row cache has to be locked and updated with a nextval request. Multiple sessions requesting a nextval will hence be blocked on a 'row cache lock' wait.
When caching + ordering is used and RAC is enabled (cluster_database = true), then the session wanting to get the NEXTVAL of a sequence need to get an exclusive instance SV lock before inserting or updating the sequence values in the the shared pool. When multiple sessions want the nextval of the same sequence, then some sessions will wait on 'DFS lock handle' wait event with id1 equal to the sequence number.
As a consequence of those serialisation mechanisms, the sequence throughput, i.e. the maximum speed at which it is possible to increment a sequence, doesn't scale with the number of RAC nodes, when the sequence is not cached and/or when the sequence is ordered. The sequence throughput is always better for cached sequences compared to non cached sequences.
In summary, due to the serialization mechanisms for non-cached ordered sequences, the sequence throughput doesn’t scale with the number of RAC nodes, i.e. the throughput doesn't increase. The throughput even decreases a bit with the number of nodes since e.g. it takes more time to get a row cache lock or the SQ enqueue when more nodes are involved. That decrease effect is certainly visible when going from 1 to 2 nodes and is still perceptible to a lower extent when going from 2 to 3 nodes, but flat away with more nodes.
Beginning in 18c, Scalable sequences significantly reduce the sequence and index block contention and provide better data load scalability compared to the solution of configuring a very large sequence cache using the CACHE clause of CREATE SEQUENCE or ALTER SEQUENCE statement.
Warning: rearranging OLAP AW Sequences (Those end with S$) will corrupt the OLAP AW.
