Database sequence not synchronized due to sequential cache in RAC Environment

I. [Problem description]

Recently, the customer suggested that the usage balance of the KC70 medical account is inconsistent with the account balance in KC04.

Ii. [analysis process]

According to the database backend package, the backend package obtains the current balance of each person based on the largest OAE001 in KC70. According to the normal idea, the sequence increases progressively and there should be no problem.

Query the KC70 (for example) of the person and find that there are two records with the same amount before the change. After careful observation, we found that the OAE001 of the KC70 records generated at, was larger than the OAE011 generated. This results in an error.

1. Is there a call sequence?

Check the background package process and find that the sequence seq_a_rowid is the unified called by KC70. There is no need for sequence calling.

2. Because the database cluster is used, will the same sequence be different in different database instances?

PL/SQL observation shows that the starting values of the seq_a_rowid sequence of the two database instances are the same.

3. Use PL/SQL to connect two instances (1 and 3) at the same time, execute SELECT seq_a_rowid.nextval FROM dual, and find that the two instances are not consecutive, and compare the sequence values generated by the two instances, sometimes instance 1 produces a sequence greater than instance 3, and sometimes instance 3 is larger than instance 1.

Iii. [solution]

In fact, the problem is that the database is a RAC environment, the sequence is to be shared, and the sequence creation statement is viewed. The sequence cache is 8000, And there is cache. In the rac two nodes, if the sequence cache is 20, 1-20 are cached on node 1, and 20-40 are cached on node 2. When the sequence value is set from different nodes, the value obtained first may not be small, and the value obtained later may not be large. The default sequence is noordered. Therefore, this situation is entirely possible:

Run SELECT seq_a_rowid.nextval FROM dual on node 1. The value of SELECT seq_a_rowid.nextval FROM dual on node 2 is greater than that on node 2.

Specifically, there are two solutions:

1. Set the cache value to null. (Display as 0 through PLSQL );

2. When creating a sequence, add the order attribute and use the cache + order attribute;

Simple Method:

Use PL/SQL DEVELOPER to open the sequence, set the cache size of the sequence to 0, or set the ORDER option.

Oracle uses the following three locks to manage sequence:

* Row cache lock: nocache during the sequence. nextval call Process)

* SQ lock: cache + noorder during sequence. nextval call)

* Dfs lock handel: it is owned when sequence. nextval is called on the premise that the sequence between nodes on RAC is ensured. The sequence is assigned to the cache + order attribute. (Cache + order)

Row cache lock is designed to ensure the sequence in the sequence. nextval call process when sequence specifies nocache;

The cache parameter tells oracle to allocate a set of sequence numbers in advance and keep it in memory so that the sequence number can be quickly accessed. The memory size is the size specified by the cache. When multiple users access a sequence at the same time, it reads the current reasonable value of sequence in oracle SGA. If the concurrent access is too large, if the cache size is not enough, a sequence cache-related wait (enq: SQ-contention) will be generated, affecting system performance.

In a multi-node RAC environment, the Cache attribute of Sequence has a great impact on performance. Assign the cache + noorder attribute as much as possible and assign enough cache values.

To ensure the order, you must assign the cache + order attribute. However, in order to ensure the order, instances need to constantly exchange data. Therefore, the performance is slightly poor.

Iv. [Experience Summary]

Although setting the cache value to null can solve the problem, it will greatly reduce the performance of the program in the cluster environment. Therefore, use the Add ORDER Method in general.

In the cluster environment, when there is a requirement on the sequence size sequence, the sequence must be controlled in the above way.