The difference between cache and NoCache in Oracle sequence; appropriate use

Oracle has a parameter when creating a sequence (sequence) you can choose either cache or NoCache, and here's the difference:

First look at the statement that creates the sequence:

Create sequence seq_id
MinValue 1
MaxValue 99999999
Start with 1
Increment by 1
Cache N/nocache--where n represents an integer, the default value is 20
Order

If you specify the cache value, Oracle can pre-place some sequence in memory so that it accesses faster. After the cache is finished, Oracle automatically takes another set to the cache. Using the cache may jump, such as the database suddenly abnormal down (shutdown abort), the cache sequence will be lost. For example: For example, in your sequence cache 100, when you sequence to 90 o'clock suddenly power off, then after you restart the database, the value of sequence will start from 101.

If you specify the NoCache value, Oracle does not pre-store sequence in memory, and of course this avoids the loss of sequence that the database is not properly down. However, there are some problems: the creation of NoCache sequence is prone to the row cache lock wait event when high concurrent access occurs, mainly because the dictionary information in Rowcache needs to be modified each time the Nextval is acquired. Using NoCache sequence also causes the following problems:
Because a commit is required each time the dictionary information is modified, it may cause log file sync to wait, NoCache sequence in a RAC environment, an index created from a sequence-generated column causes a large number of index block contention between instances
Avoid creating NoCache sequence based on the above problems.

Take a look at sequence related protection mechanisms:
Row Cache Lock: Occurs when a data dictionary needs to be modified in the case of a sequence.nextval call, corresponding to the row cache lock event
SQ Lock: Occurs when fetching sequence.nextval on memory cache (not rowcache), corresponding to Enq:sq-contention event
SV Lock: Occurs when obtaining the sequence.nextval of the Cache+order property in a RAC environment, corresponding to the DFS lock handle event

What is the use of NoCache when using the cache?

I personally feel that I should try to use the cache, because many of the current database is running in high concurrency, first this can be performance, and will not produce a row cache lock wait event. Some people may worry that the database is not normal down will produce serial number discontinuity, but this is also very few cases. Of course, if your business requirements are not to produce a discontinuous serial number, then use Nochache.

The above text also explains why the sequence of Oracle can have a jump sign.

This article is from the "design mode" blog, so be sure to keep this source http://1657167.blog.51cto.com/1647167/1560725

The difference between cache and NoCache in Oracle sequence; appropriate use