Sequence Generator In Oracle Database Management System - Part I

Sequence is an atomic unit that provides uniqueness of table columns, especially in multiuser environments. In general, sequences are used to generate primary key values for tables. A sequence in Oracle, is equivalent to idendity columns in Microsoft SQL Server. But Oracle provides a more flexible structure with sequences. Start value, increment by value, min-max values and cachebility features are given with Sequence( a.k.a sequence generator ) in Oracle database management system.
When using sequence in Oracle it must be taken into consideration to that it may not be possible to provide consecutive sequence numbers. If business has a restriction, it will be better to use own-made sequences. Because, with cachebility of sequences for performance reason, consecutive sequence numbers may not be used.

SQL> create sequence seq_test
  2  start with 1
  3  increment by 1
  4  cache 50;

Sequence created

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        1

SQL> alter system flush sga;

alter system flush sga

ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush SHARED_POOL;

alter system flush SHARED_POOL

ORA-01031: insufficient privileges

SQL> conn sys/0000@XE as sysdba;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL> grant alter system to hr;

Grant succeeded

SQL>  conn hr/hr@XE
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        2

SQL> alter system flush SHARED_POOL;

System altered

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        51

SQL>


On requesting a sequence number, Oracle first check its internal cache storage areas. If Oracle finds a pre-allocated sequence number, uses it. This operation do not need any I/O. So in performance metrics, this is very efficient way. If Oracle does not find, it queries its internal tables to get last value. This can be found is [dba|all|user]_sequences.LAST_NUMBER column. Oracle then uses cache degree to hold consecutive sequence numbers in memory, SGA.

SQL> SELECT cache_size, last_number
  2    FROM user_sequences
  3   WHERE sequence_name = 'SEQ_TEST';

CACHE_SIZE LAST_NUMBER
---------- -----------
        50         51

SQL>


Oracle gives an option to create cacheless sequences. This is in one respect, retrieving every sequence from disk, instead of sequence cache of SGA. In multiuser environment that uses sequences, for instance OLTP systems that has a huge number of inserts, will decrease performance. Always memory is much more faster than disk. But the advantage of this option is may  provide consecutive sequence numbers. Do not forget nocache sequences will not gurantee consecutivity because of rollbacks.

SQL> alter sequence seq_test nocache;

Sequence altered

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        52

SQL>
SQL> SELECT cache_size, last_number
  2    FROM user_sequences
  3   WHERE sequence_name = 'SEQ_TEST';

CACHE_SIZE LAST_NUMBER
---------- -----------
         0          53

SQL> alter system flush SHARED_POOL;

System altered

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        53

SQL>


The performance effects of cached sequences versus cacheless sequences are dramatic. Cache degree does not have a linear equation with performance. Choosing a suitable number will effect best performance on system.

SQL> alter sequence seq_test nocache;

Sequence altered

SQL> drop table t;

Table dropped

SQL> set timing on

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 5,888 seconds

SQL> alter sequence seq_test cache 5;

Sequence altered

Executed in 0,01 seconds

SQL> drop table t;

Table dropped

Executed in 0,041 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 1,392 seconds

SQL> alter sequence seq_test cache 10;

Sequence altered

Executed in 0,01 seconds

SQL> drop table t;

Table dropped

Executed in 0,04 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 0,811 seconds

SQL> alter sequence seq_test cache 100;

Sequence altered

Executed in 0,02 seconds

SQL> drop table t;

Table dropped

Executed in 0,05 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 0,29 seconds

SQL> alter sequence seq_test cache 10000;

Sequence altered

Executed in 0 seconds

SQL> drop table t;

Table dropped

Executed in 0,03 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 0,211 seconds