Wednesday, June 1, 2011

Effects Of Sequence Aging Out Of Shared Pool

Like any other database object in shared pool sequence could also be aged out or flushed out of the shared pool to make room for other objects. When this happens values return by the sequence may not be in the expected order.
SQL> create sequence mseq ;

SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N N 20 1
Take the first value from the sequence and examine the last number
SQL> select mseq.nextval from dual;

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

SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N N 20 21
At this stage if the sequence is aged out of the shared pool (in this test case by flushing the shared pool) the next value returned would be 21 instead of 2. As sysdba run
SQL> alter system flush shared_pool;
and then as owner of the sequence
SQL> select mseq.nextval from dual;

NEXTVAL
----------
21

SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N N 20 41
This creates gaps in the values returned by the sequences and it only affects the cached sequences and nocache, order sequences are not affected
SQL> create sequence mseq nocache order;

SQL> select mseq.nextval from dual;

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

SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N Y 0 2
Flush the shared pool
SQL> alter system flush shared_pool;
Sequence returns the next value 2 and flushing shared pool has no effect
SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N Y 0 2

SQL> select mseq.nextval from dual;

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

SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N Y 0 3
One way to prevent is to pin the sequence in the shared pool
SQL> create sequence mseq ;

SQL> select mseq.nextval from dual;

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

SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N N 20 21
Pin the sequence with
SQL> exec dbms_shared_pool.keep('ASANGA.MSEQ','Q');
and flush the shared pool
SQL> alter system flush shared_pool;
Ordered values are returned from the sequence without a gap
SQL> select mseq.nextval from dual;

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

SQL> /

NEXTVAL
----------
3

SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ 1 1.0000E+28 1 N N 20 21