Register Login

What is Meaning of Cache and Nocache in Sequence?

Updated May 18, 2018

Hello friends,

I am new to this field and I want to know that what is meaning of cache and nocache in sequence? If anyone is familiar with these terms then please give me the suitable definition and also tell me their functionalities.

Thanks in Advance!


Comments

  • 18 Aug 2015 1:46 pm Sonia Barwar

    CACHE:
    Specify how many values of the sequence the database preallocates and keeps in memory for faster access. Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment. The "cache" clause caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue.

    NOCACHE:
    Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

  • 18 Aug 2015 1:46 pm Abhijeet Mudgal

    Nice explanation.

  • 18 Aug 2015 1:49 pm Chandan Singh Parihar

    Not only the cached values will go away after bouncing the database. It may happen if SGA is ran out of memory/flushed out.That being said, never expect a sequence to give "SEQUENTIAL" numbers unless if you give "NOCACHE". But thats not bad at all. If you use it primary key/unique key, it will always give you unique values but dont expect it be "SEQUENTIAL", there could be gaps. Simple example below.
    CREATE SEQUENCE S_1_1
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 100
    NOORDER
    CACHE 20
    CYCLE;
    --sequence S_1_1 created.--
    SELECT S_1_1.NEXTVAL FROM DUAL;
    NEXTVAL
    ------
    1
    --Flushing the Shared pool---
    ALTER SYSTEM FLUSH SHARED_POOL;
    --system FLUSH altered.--
    --Calling the Sequence again expecting it will give 2, but not ---
    SELECT S_1_1.NEXTVAL FROM DUAL;
    NEXTVAL
    -------
    21. It gives 21 because already values upto 20 are cached in the shared pool, when we flushed it, it went away. So when we call again, it gives the next value as 21. Ok. Why Caching values will give good performance? Consider the sample example before Flushing statement. When you call s_1_1.nextval, the values from 1-20 will taken from the sequence and cached in the SGA. When you call it again like s_1_1.nextval, the value 2 will be returned but this value 2 is not taken from Sequence, it has been taken from Cache (SGA). It will do so till your call reaches 21. So till 2 to 20 there is no "Real" call to sequence and hence the improvement in performance.


×