When defining sequences, you can choose to set a cache size or disable the sequence cache:

create sequence tst_nc2  start with 1 increment by 1 nocycle nocache;create sequence tst_ch  start with 1 increment by 1 nocycle cache 50000;

I wondered, does it make an difference in performance?....

 

A simple test:

declare l_id number(12);begin  dbms_output.put_line( 'start nocache op '||to_char(systimestamp));  for r in 1..49999  loop  select tst_nc2.nextval into l_id from dual;  end loop;dbms_output.put_line( 'end nocache op '||to_char(systimestamp));  dbms_output.put_line( 'start cache op '||to_char(systimestamp));  for r in 1..49999  loop  select tst_ch.nextval into l_id from dual;  end loop;dbms_output.put_line( 'end cache op '||to_char(systimestamp));end;
/

Result: 

start nocache op 22-09-06 10:39:41,163681000 +02:00
end nocache op 22-09-06 10:40:39,500215000 +02:00
start cache op 22-09-06 10:40:39,500591000 +02:00
end cache op 22-09-06 10:40:55,823251000 +02:00

 
Without cache, 50k of sequence numbers are fetched in about 58 seconds. Filling the cache with 50000 records at once and fetching them takes 16 seconds.

So, there is a noticable benefit of using the cache.

When I fill the cache with values before running the procedure:

  select tst_ch.nextval from dual;

   NEXTVAL
———-
         1

1 rij is geselecteerd.

  1  declare l_id number(12);
  2  begin
  3    dbms_output.put_line( ’start cache op ‘||to_char(systimestamp));
  4    for r in 1..49999
  5    loop
  6    select tst_ch.nextval into l_id from dual;
  7    end loop;
  8  dbms_output.put_line( ‘end cache op ‘||to_char(systimestamp));
  9  end;
 10
 11  /
start cache op 22-09-06 10:51:25,604447000 +02:00
end cache op 22-09-06 10:51:32,852197000 +02:00

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:07.31

 

 I see that just reading 50k values from the cache takes just over 7 seconds.

 So, if you really, really, really need to squeeze the last bit of performance improvement out of you batch, check your sequence’s cache size.