Ari,
What benefit would I gain by using the create sequence command with the
no cache option? Is it possible to get duplicate sequence numbers when
the system is under a heavy load? (note: we are using Oracle 8.05 )
Thanks, -Mike
---------------------------------------------------------------------------
This is from Oracle Support - I thought I'd put it up for people's reading,
as I had received a question on this once.
-Ari
--------------------------------------------------------------------------
Oracle Corporate Support
Problem Repository
1. Prob# 1035206.6 BULLETIN: CACHING ORACLE SEQUENCES
Problem ID : 1035206.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle Server - Enterprise Edition V7
Oracle ODBC Driver
Affected Components : RDBMS V07.XX
Affected Oracle Vsn : V07.XX
Summary:
BULLETIN: CACHING ORACLE SEQUENCES
+=+
Document ID: PRE 1035206.6
Title: Caching Oracle Sequences
Department: US: WWSUP, Workgroup Technologies
Revision Number: 0
Distribution Code: External
Product: Oracle Server
Product Version: V7.X and V8.x
Abstract: This article discusses the cache option, why
numbers can be lost and how to minimize this
occurance.
Keywords: sequence, cache, skip
------------------------------------------------------------------------
Applications which use Oracle sequences which have the CACHE option
enabled will often 'skip' values. This article discusses the cache
option, why numbers can be lost and how to minimize this occurrance.
When used in a non-cache mode, an access of a sequence which requests
the 'nextval' will increase the current value by the number specified
in the 'increment' section of the sequence and return the new value.
As an example, for a sequence with a current value of zero, an
increment of one, and no cache, the current value would become one,
and one would be returned to the calling statement.
When used with a cache, an access of the sequence which requests the
'nextval' will increase the current value by the increment times the
cache, and will return to the calling function the current value plus
one times the increment. The following access of the nextval *should*
access the cached seqence values and return a number which equals the
previous plus the increment. Ideally, numbers will continue to be
returned from the cache until it has reached the end. This is
diagrammed below with an increment of one and a cache of 5:
NUMBER SEQUENCE CACHE
RETURNED CURRENT CURRENT
*start* *none* 0 *none*
1st access 1 5 1
2nd access 2 5 2
3rd access 3 5 3
4th access 4 5 4
5th access 5 5 5
6th access 6 10 6
7th access 7 10 7
Assuming the cache is never lost, a cached sequence will return
values exactly the same as a non-cached one. However, a sequence
cache is kept in the shared pool just as other cached information is.
This means it can age out of the shared pool in the same way as
a procedure if it is not accessed frequently enough. Everything is the
cache
is also lost when the instance is shut down. Because of these two
issues
(especially the second), if an application requires a sequence which
never
skips any numbers, a non-cached sequence must be used. The above
example is
repeated below with the addition of one occurance of the cache aging out
of
the shared pool and one shutdown of the database:
NUMBER SEQUENCE CACHE
RETURNED CURRENT CURRENT
*start* *none* 0 *none*
1st access 1 5 1
2nd access 2 5 2
*cache aged out*
3rd access 5 10 5
4th access 6 10 6
*shutdown*
5th access 10 15 10
6th access 11 15 11
7th access 12 15 12
To help with the aging problem, the Oracle development team made a
change to
the DBMS_POOL.KEEP procedure which allowed sequences to be PIN'd in
the shared pool in the same fashion as packages and procedures. This
will not prevent the cache from being lost in the event of a
shutdown, but will make it less far likely for numbers to be skipped.
However, if a sequence is being used so seldom that it *must* be
PIN'd, there is little or no gain from having the sequence
cached at all. Caching a seqence prevents Oracle from having to make
a disk access to get the next value at the expense of using a portion
of memory from the shared pool. If sequences are accessed rapidly,
the time savings can add up and the minor cost in RAM is well spent.
For an infrequently used sequence, the savings are minimal and the
cache is better disabled than PIN'd.
In release 7.2 and below of Oracle, sequence caches were kept in the
rowcache. As of 7.3, they were moved to the library cache. To users
and applications, the result of this change is that sequence caches
are aged out faster in 7.3 (and this continues in Oracle 8). It's
for this reason that the DBMS_POOL.KEEP procedure was changed. The
change exists on 8.0.x, 7.3.3.x patch releases and has been
backported to some operating systems for 7.3.2.x. For versions which
support it, sequences can now be pinned in the library cache. For a
sequence named 'seq', this is achieved by invoking
dbms_shared_pool.keep() as follows: dbms_shared_pool.keep('seq', 'Q').
Back to Ari Kaplan's Home Page