How does the sequence cache affect performance?

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

<code>create sequence tst_nc2&nbsp; start with 1 increment by 1 nocycle nocache;</code><code><br /></code><code>create sequence tst_ch&nbsp; start with 1 increment by 1 nocycle cache 50000;</code>

I wondered, does it make an difference in performance?

A simple test:

declare<br />&nbsp;l_id number(12);<br />begin<br />&nbsp; dbms_output.put_line( 'start nocache op '||to_char(systimestamp));<br />&nbsp; for r in 1..49999<br />&nbsp; loop<br />&nbsp; select tst_nc2.nextval into l_id from dual;<br />&nbsp; end loop;<br />dbms_output.put_line( 'end nocache op '||to_char(systimestamp));<br />&nbsp; dbms_output.put_line( 'start cache op '||to_char(systimestamp));<br />&nbsp; for r in 1..49999<br />&nbsp; loop<br />&nbsp; select tst_ch.nextval into l_id from dual;<br />&nbsp; end loop;<br />dbms_output.put_line( 'end cache op '||to_char(systimestamp));<br />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.