Cached values of sequence in oracle

Question

When I am generating sequence values from the below-created sequence trial_seq, it gave 1, 2, 21, 41, 4,......

CREATE SEQUENCE trial_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

I am confused with the working of CACHE. What values from the sequence are stored in cache when for the first time NEXTVAL is called (just after the creation of sequence)? Are they from 1 to 20 (both inclusive) or just some random 20 numbers between MINVALUE and MAXVALUE? Now, if the cache is storing random 20 number within sequence range, then it's okay but if the cache is storing from 1 to 20, then why it is giving 21 and subsequently 41, it should be giving values within the range of 1 to 20 until all values within this get exhausted? I specifically want to understand this by NOT using NOCACHE and/or ORDER. Also, I am just learning, not using for RAC.

Answer 1

You describe that you get a sequence value in the following order 1, 2, 21, 41, 4. Are you executing the nextval query from within APEX? That might indeed be correct.

APEX uses connection pools and a sequence cache cannot be shared over connections, so each connection will have its own sequence cache.

Let's imagine you have a connection pool of 5 connections. Each connection will create its own sequence cached values at the moment you execute sequence.nextval within that connection.

Connection 1 - Sequence cache 1 - 20
Connection 2 - Sequence cache 21 - 40
Connection 3 - Sequence cache 41 - 60
Connection 4 - Sequence cache 61 - 80
Connection 5 - Sequence cache 81 - 100

You can imagine that when APEX controls which connection it is going to use within the connection pool, it is impossible to determine the sequence onbeforehand.

In short:

• The sequence will generate a number in ascending order, f.e. 1,2,3,4,5,etc.. not random.
• There can be gaps within that sequence, f.e. 1,2,4,5,8,9,10. Here 3,6,7 are skipped and will never be used again.
• If you run it through APEX, multiple connections can/will be used and so multiple cached sequence ranges will be used. The order you will see could be anything like 1,21,41,22,42,2,3,43. The jumping from 1 to 21 to 41, back to 22 aren't gaps, but is because it is using a different connection, and thus a different cached sequence range. I used 1, 21, 41 so you can see the CACHE 20 behaviour back in the example.

You could try executing this as a script, to see the correct behaviour:

DROP SEQUENCE trial_seq
/

CREATE SEQUENCE trial_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20
/

create table trial_test( id number)
/

insert into trial_test
select trial_seq.nextval
from   dual
connect by rownum <= 10
/

select *
from   trial_test
/

What is the outcome? It should be 1 - 10, or at least somewhere near that outcome.

Let me know if this answers your question.