Pros and cons of caching sequences in Shareplex replication. (51969)

There are two different options pursued when generating next values for sequences is concerned. One is CACHE option and the other one is NOCACHE. As far as replication of sequences is concerned, the use of one or the other option has certain implications. This solution explains what goes behind the scene as far as Shareplex (and to some extent Oracle) is concerned and why the CACHE option is preferred when replicating sequences in Shareplex.

General information.

In CACHE option, Oracle stores in memory the values for the next batch of sequence numbers it will generate for the sequence in question. Once the current batch is used, the next batch is available in memory to be used for the sequence number series. In a way the Oracle pre-allocates the sequence numbers it will use and does so by the order of the value as given by the CACHE parameter. In case of NOCACHE option, all sequence numbers are written to disk.
While CACHE option is prone to database failures and can result in sequences going out of sync on the target, it has the advantage of speed. This is especially true for frequently accessed sequences. Moreover, the way Shareplex replicates sequences, it results in less number of DDLs sent to the target sequence when sequences are replicated. By the same token, the NOCACHE option ensures that the sequence values are not lost when there is an Oracle instance failure, since sequence values are written to disk at all times. But there is an extra overhead since every sequence value that is selected results in a DDL sent to target for altering the target sequence as opposed to the DDL sent once every time the cache value is reached.

It should be noted that if there is insufficient memory and/or if the number of sequences cached are numerous, then there is a chance of contention resulting in large values for sequence cache waits which will show up in the form of increased service time for DML involving sequences. So caching may not always be the best solution as far as performance is concerned.

The following DDLs are sent by Shareplex to the target, once the cache value is reached (assuming that the default value of 20 is set for CACHE):

ALTER SEQUENCE <owner>.<sequence> INCREMENT BY 20 NOCACHE;
SELECT <owner>.<sequence> .NEXTVAL FROM DUAL;
ALTER SEQUENCE <owner>.<sequence> INCREMENT BY 01 CACHE 20;