Document Type | Troubleshooting
Category | Administration
Applicable Product Version | 6FS07
Document Number | TADTS040
Issue
In a TAC environment, when using the NO ORDER + CACHE option for sequence, the sequence values are not assigned in order.
Cause
In a TAC environment with CACHE + NO ORDER options, after node1 skips sequence numbers equal to the cache size, node2 starts allocating from the subsequent numbers of its allocated cache size.
As a result, the sequence values are effectively not assigned sequentially.
1. Sequence creation with NO ORDER option
CREATE SEQUENCE NO_ORDER INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE CACHE 5 NOORDER ;
2. Query results
When selecting select NO_ORDER.nextval from dual; more times than the CACHE value allocated to NODE2 from NODE1, it can be confirmed that the sequence values are not assigned sequentially.
SQL> select NO_ORDER.nextval from dual; NEXTVAL 1 1 row selected. SQL> select NO_ORDER.nextval from dual; NEXTVAL 2 1 row selected. SQL> select NO_ORDER.nextval from dual; NEXTVAL 5 1 row selected. SQL> select NO_ORDER.nextval from dual; NEXTVAL 11 1 row selected.
Solutions
When using the ORDER option, the sequence numbers do not skip to the cache size allocated to other nodes and are assigned sequentially.
NoteUsing the order option may cause performance overhead. It is recommended to use CACHE and ORDER/NO ORDER options according to the site environment.