How does the sequence cache affect performance?

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.