sequence

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 31 Jul 2003 10:08:25 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703F9B1C6@lnewton.leeds.lfs.co.uk>

Morning,

CACHE : this many numbers from the sequence are cached ready for 'immediate' use as and when required. Any unused but cached entries will be lost on shutdown or on flushing the shared pool. Also, if you look at user_sequences (or all_ or dba_) then the last_number column is NOT the last number generated for a user requesting a number, but is the last number cached plus one !!

eg.

DROP SEQUENCE testseq
Sequence dropped

CREATE SEQUENCE testseq
CACHE 100
ORDER
Sequence created

SELECT sequence_name,last_number
FROM USER_SEQUENCES
WHERE sequence_name = 'TESTSEQ'

SEQUENCE_NAME                  LAST_NUMBER


------------------------------ -----------

TESTSEQ                                  1

1 row selected

At this point no numbers have been requested, so the sequnce has not yet been cached.

SELECT testseq.NEXTVAL FROM dual

NEXTVAL

1
1 row selected

SELECT sequence_name,last_number
FROM USER_SEQUENCES
WHERE sequence_name = 'TESTSEQ'

SEQUENCE_NAME                  LAST_NUMBER


------------------------------ -----------

TESTSEQ                                101

1 row selected

Last number shows that values 2 - 100 have been cached and that the NEXT number to be issued when the cache is used up will be 101. The name last_number is a tad misleading if you ask me !

ALTER SYSTEM FLUSH SHARED_POOL
System altered

SELECT testseq.NEXTVAL FROM dual

NEXTVAL

101
1 row selected

So, all the numbers in the cache, from 2 to 100 have been lost forever.

PIN : the sequence cannot be flushed out of the shared pool - so flushing won't affect any cached entries, only shutdown.

eg.

DROP SEQUENCE testseq
Sequence dropped

CREATE SEQUENCE testseq
CACHE 100
ORDER
Sequence created

you might need to run oracle_home/rdbms/admin/dbmspool.sql to get the dbms_shared_pool package installed
as it is not installed by default.

BEGIN
sys.dbms_shared_pool.KEEP('TESTSEQ','Q'); END;
/
PL/SQL procedure successfully completed

SELECT sequence_name,last_number
FROM USER_SEQUENCES
WHERE sequence_name = 'TESTSEQ'

SEQUENCE_NAME                  LAST_NUMBER


------------------------------ -----------

TESTSEQ                                  1

1 row selected

getting the first number will cache the sequences cached values as before.

SELECT testseq.NEXTVAL FROM dual

NEXTVAL

1
1 row selected

SELECT sequence_name,last_number
FROM USER_SEQUENCES
WHERE sequence_name = 'TESTSEQ'

SEQUENCE_NAME                  LAST_NUMBER


------------------------------ -----------

TESTSEQ                                101

1 row selected

There's that 101 again !

ALTER SYSTEM FLUSH SHARED_POOL
System altered

SELECT testseq.NEXTVAL FROM dual

NEXTVAL

2
1 row selected

So you can see that the cached values were not affected by the flush.

They WILL be affected by a shutdown though.

HTH Cheers,
Norm.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: daud11_at_hotmail.com (Daud) [mailto:daud11_at_hotmail.com] Posted At: Thursday, July 31, 2003 5:45 AM Posted To: server
Conversation: sequence
Subject: sequence

I found that in dbms_shared_pool you can actually pin sequences. What is the difference between doing this and using cache when creating the sequence? Received on Thu Jul 31 2003 - 04:08:25 CDT