What Can Go Wrong With Sequence?

Oracle Sequence is a unique number generator that is used in a table to automatically populate Primary Keys value like ID or sequence number.  The database has full control on a sequence number you generate. The sequence number generated can be incremented, decremented; can have max, min value, defined interval etc.  There are several parameters you can specify to control the function of the sequence.

Using parameters like ascending, descending, min value, max value, sequence interval, cache, order etc.let you define the properties of a sequence. No other objects or Functions in Oracle can produce a sequence number like Sequence object. Sequence number is independent of transaction committing or rollback.

Recently, our developer experiences an issue with Sequence on our 2 nodes cluster database. What exactly is the issue? The problem sequence was created with using below DDL statement.

CREATE SEQUENCE id_seq
      INCREMENT BY 1
      START WITH 1
      NOMAXVALUE
      NOCYCLE
      CACHE 10;

The sequence starts with 1, increment with 1, has no max value, cache 100 and no cycle. Immediately after creating a sequence, the sequence number starts with 1 on Instance 1, whereas it starts from 101 on Instance 2. The developer wants to see the next value to be 2 on Instance 2.  The sequence is skipping sequence value in a RAC environment. Developer would like to know why and what is the cause of skipping sequence?

I do not see this as an issue as long as the sequence is generating a unique number that starts with 1 and increment by 1 and not generating a duplicate numbers. In this case, it is not generating duplicate sequence.

Why do you think skipping a sequence number is a problem? It is functioning exactly what you have asked to do. I would like to know more on the purpose of the sequence and what we are trying to accomplish? Developer is using sequence to get the count or tallying on how many times a Function is being called? Sequence is not the right object to get the count of anything. The execution count of a function or any object should be done inside a  code using PL/SQL or outside of database and not rely on sequence number.

One last time I repeat, sequence is not designed for counting or for tallying. Well, let's go back to our skipping problem, I was little offtrack here while explaining the purpose of the Sequence. Is there a way to fix this skipping issue on RAC environment if not why are we seeing this issue? What is the casue or is it a know issue? In my decade long database career, I have not seen this issue on a database with single instance. Why do we see on RAC only? Before solving the issue, it is highly recommended to know what each parameter does in a CREATE Sequence Syntax. 

Sequence Syntax:

CREATE SEQUENCE [ schema. ] sequence_name
   [ { INCREMENT BY | START WITH } integer
   | { MAXVALUE integer | NOMAXVALUE }
   | { MINVALUE integer | NOMINVALUE }
   | { CYCLE | NOCYCLE }
   | { CACHE integer | NOCACHE }
   | { ORDER | NOORDER }
   ]...;

What each parameter means? 

1.  INCREMENT BY – Sequence interval
2.  START WITH – The first sequence number
3.  MAXVALUE – Maximum integer value
4.  NOMAXVALUE – Sets max value to 9999999999999
5. MINVALUE -- Minimum value, used ONLY descending sequence.
6. NOMINVALUE: Sets to -10^16, used ONLY for descending sequence.
7. CYCLE – To generate values after reaching it max value. It will again begin from minimum        or start with value.
8. NOCYCLE – Indicates the sequence ends when maximum integer value is reached
9. CACHE – Indicates the number of sequence values for database engine to reallocate and           keeps in memory for faster access.
10. ORDER -- Guarantees sequence generation in order across all nodes
11. NOORDER -- Reverse of ORDER

Solution: Using the ORDER parameter while creating the sequence guarantees the sequence generation in order across all RAC nodes instances. This use of ORDER parameter is something you will need to verify with your DBA to see if this cause performance issue. Generally, this is recommended for sequence which is seldom used.

Solution: 

CREATE SEQUENCE id_seq
      INCREMENT BY 1
      START WITH 1
      NOMAXVALUE
      NOCYCLE
      CACHE 10
      ORDER;

We resolved the skipping issue but we introduced performance issue. NOORDER parameter should be used to avoid SQ enqueue contention while ordering the sequence value. If you don't specify ORDER/NOORDER, it defaults to NOORDER. How do you improve Sequence Performance? You will need the detail understanding of CACHE, ORDER, and NOORDER parameters and know when to use them before you learn to tune the Sequence.  Below, we will talk about these three parameters in detail. 

CACHE: Indicates the number of sequence values which Oracle database reallocates and keeps in memory for faster access. The default value is 20 if you don’t specify it during Sequence creation. The maximum integer value can have 28 or less digits.

On a heavily used Sequence, the cache value should be higher for best performance. If you set it to low or NOCACHE, the sequence number has to be read from disk which becomes costly and degrades the database performance. Avoid using NOCACHE where possible if not set CACHE to low value like 100. Alter command may be used to increase cache size anytime after a sequence is created. You may not alter all other parameters, if you need to alter them, dropping and re-creating a sequence is the only way.

ALTER SEQUENCE id_seq cache 1000;

When the database engine restarts, you will lose all your unused cache number, therefore it is not recommended to set high cache size. A best size is to the find out the number of sequences used in a day and set that number as a cache size.  Usually cache size of 1000 is sufficient in most cases. 

Also, know when not to cache sequence number? I would never you use this options but if I have to, the following scenario qualifies for NOCACHE parameter.

1. When you absolutely can’t skip the sequence value.
2. Memory constraint or shared pool too busy
3. Sequences are rarely used
4. DBA don’t mind the performance! Yeah right!!

ORDERED: It guarantees the sequence numbers are generated in order of the request. This solves the skipping issue in a RAC environment like we discussed

NOORDER: This is the default setting which does not guarantee the order of sequence number generation. This setting is highly recommended when CACHE is set and the database has cluster.

To get the best performance on sequence set CACHE to higher number or ORDERED but not both. Forcing the ordering of sequence by using ORDERED parameter will cause a RAC environment to slow down and may eventually bring down the database.

Do other DBMS have Sequence?

SQL Server has introduced the concept of Sequence on  SQL Server 2012 and later editions of their database. The syntax and parameters are almost identical to that of Oracle database. I wonder if they copied their source code? The best practices that we have been taking about Oracle Sequences performance principle applies to SQL Server Sequences as well.

SQL Server Syntax:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH  ]
    [ INCREMENT BY  ]
    [ { MINVALUE [  ] } | { NO MINVALUE } ]
    [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [  ] } | { NO CACHE } ]
    [ ; ]

MySQL: There is no Oracle or SQL Server equivalent of  Sequence in MySQL database. The purpose of sequence is to generate a list of integer in ascending/descending order to use with primary key in a table. Don’t use this for anything else!. The AUTO_INCREMENT attribute of a column in MySQL does the same thing as sequence with limited control on how you wanted your sequence to act. Oracle now owns MySQL, I will not be surprised to see Sequence on MySQL soon.

CREATE TABLE employee( 
  column1 datatype NOT NULL AUTO_INCREMENT,
  column2 datatype [ NULL | NOT NULL ],
  ...);

Oracle 12c has introduced a new feature for auto increment column attribute (Identity) like that of MySQL and SQLServer. More and more developers have started using this feature over sequence because it is easy implement.  Again, this feature is limited and don’t give us much control like Sequence, therefore we started to see Sequence on SQL Server on Release 2012 or later version.

CREATE TABLE emoloyee(
  id    NUMBER GENERATED ALWAYS AS IDENTITY,
  fname VARCHAR2(30),
  lname VARCHAR2(30),
  ...
  );

Other Errors:

What other error are commonly seen with Sequence? Another most popular error with Sequence is ORA-08002.  This error occurs when you try to execute or display CURRVAL before the NEXTVAL command was executed. Always run NEXTVAL before executing a CURRVAL. Do you know any other error or issue with Sequence?

CACHE, NOCACHE and ORDER are three important sequence parameters to be used wisely to gain the performance. Choose them wise or pay the price.