How Does Oracle select the parameter cache or nocache when creating a sequence ?, Sequencenocache

How Does Oracle select the parameter cache or nocache when creating a sequence ?, Sequencenocache

How Does Oracle select the parameter cache or nocache when creating a sequence?

The differences between the two are as follows:

First, let's look at the statement for creating sequence:

Create sequenceSEQ_ID

Minvalue 1

Max value 99999999

Start with 1

Increment by 1

Cachen/nocache -- n indicates an integer. The default value is 20.

Order;

If the CACHE value is specified, Oracle can place some Sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, Oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the Sequence in the cache will be lost. For example, in your sequence cache100, the sequence value starts from 101 after you restart the database.

If the NOCACHE value is specified, Oracle will not store Sequence in the memory in advance. Of course, this will avoid the loss of sequence caused by abnormal database down. However, some problems may occur: When nocache sequence is created for highly concurrent access, rowcachelock waits for events, mainly because the dictionary information in rowcache needs to be modified every time nextval is obtained. Using nocache sequence also causes the following problems:

Because commit is required to modify dictionary information each time, log file sync may wait. In the RAC environment, nocache sequence will compete for a large number of index blocks between instances for indexes created based on columns generated by sequence.

Avoid creating nocache sequence Based on the above issues.

Let's take a look at the sequence protection mechanisms:

Rowcachelock: When sequence. nextval is called, the corresponding rowcachelock event occurs when the data dictionary needs to be modified.

SQ lock: When sequence. nextval is obtained in the memory cache (not rowcache), corresponding to the enq: SQ-contention event

SV lock: the DFS lock handle event occurs when sequence. nextval of the cache + order attribute is obtained in the RAC environment.

Under what conditions will nocache be used when?

I personally feel that cache should be used as much as possible, because many of the databases currently run at high concurrency. First, performance can be achieved without generating rowcachelock wait events. Some people may worry that the database may fail down to generate continuous serial numbers, but this is also rare. Of course, if your business requires that you never generate a continuous serial number, you need to use nochache.