Database sequence numbers

Sterling Order Management System Software uses Oracle sequence numbers to quickly generate unique numbers.

If you are upgrading from pre Yantra 5x 5.0 SP2 versions, ensure the seq_yfs_task_key sequence is created with the NOORDER parameter. If the ORDER option is enabled, RAC disables the CACHE option.

The SQL command to create sequence is as follows:

   create sequence seq_yfs_table_key
   increment by 1 start with 1
   maxvalue 9999999999
   minvalue 1
   cycle
   cache 500 noorder ;
   

In the example above, the CACHE option pre-allocates and stores 500 sequence numbers in the instance's SGA for fast access. When those sequence numbers are used up, Oracle pre-allocates another group of sequence numbers. The CACHE option should be set to a value so that sequence requests for one to two seconds during the peak period can be satisfied if memory is critical for performance (see Oracle 11g SQL Reference and Oracle 11g Real Application Cluster (RAC) Administration.)

For example, if the sequence cache is set to 500, the last_sequence_number in user_sequences table should not grow by more than 500 every two seconds or 15,000 every minute. You should monitor this value periodically during the peak hour.

The NOORDER option allows each RAC instance to preallocate its own group of sequence numbers. The NOORDER option is enabled by default. If the NOORDER option is disabled (or if the ORDER option is selected), Oracle disables the CACHE option.

Enabling the CACHE option with a sufficiently high value and the NOORDER option are critical for Oracle RAC performance.

You can issue the following command to check whether the ORDER option is disabled:

   select sequence_name, order_flag, cache_size,last_number
   from user_sequences
   where sequence_name = 'SEQ_YFS_TABLE_KEY'
   

If the ORDER_FLAG is set to "N", the NOORDER option is enabled:

   SEQUENCE_NAME                  ORDER_FLAG CACHE_SIZE LAST_NUMBER
   ------------------------------ ---------- ---------- -----------
   SEQ_YFS_TABLE_KEY                       N        500   422838694