By default, when a sequence is defined, if the options “cache” or “nocache” is specified, Oracle will cache 20 sequence numbers. Usually that will be fine for most of circumstances. However you may see sometimes the following query running slow when getting the next value:
select sequence_test.nextval from dual;
It could take dozens of seconds to run this simple statement. When looking at the AWR reports for sequence related metric, they may just look fine. Chances are there are multiple users or applications trying to access the sequence simultaneously which leads to contention. When cached values is not enough to serve multiple requests, Oracle has to read it from the disk which slows down things.
To solve this, you can try a bigger value than the default 20. Try a small value first, then increase it till you don’t see slowness on the statment of NEXTVAL. Of curse make sure you don’t have any slow disk issues.
alter sequence <sequence_name> cache 50;
From Oracle Guide:
Caching Sequence Numbers
Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence numbers can be accessed more quickly in the sequence cache than they can be read from disk. The sequence cache consists of entries. Each entry can hold many sequence numbers for a single sequence. Follow these guidelines for fast access to all sequence numbers:
— Be sure the sequence cache can hold all the sequences used concurrently by your applications.
— Increase the number of values for each sequence held in the sequence cache.
The Number of Entries in the Sequence Cache
When an application accesses a sequence in the sequence cache, the sequence numbers are read quickly. However, if an application accesses a sequence that is not in the cache, then the sequence must be read from disk to the cache before the sequence numbers are used.
If your applications use many sequences concurrently, then your sequence cache might not be large enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to all sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications.
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.
JoeLi's TechLife 