Oracle Sequence Nocache

默认情况下，创建Sequence时，缓冲(cache)是20.意即：20次.nextval才会触发一次对sys.seq$的更新(update)操作。这样就能提高SQL执行性能。
当用户指定nocache时，如：

create sequence test_seq nocache;

每一次test_seq.nextval都会触发一次sys.seq$更新(update)。
我们可以比较下cache 20 和nocache的性能：

create sequence test_seq1 nocache;
create sequence test_seq2 cache 20;

create table tt1 (id number);
create table tt2 (id number);

declare
  v_start_time number :=dbms_utility.get_cpu_time;
  v_time1 number;
begin
  insert into tt1 select test_seq1.nextval from dual connect by level <= 100000;
  v_time1 := dbms_utility.get_cpu_time;
  dbms_output.put_line('Nocache sequence: ' || (v_time1 - v_start_time) / 100 || ' seconds');
  insert into tt2 select test_seq2.nextval from dual connect by level <= 100000;
  dbms_output.put_line('Caceh 20 sequence: ' || (dbms_utility.get_cpu_time - v_time1) / 100 || ' seconds');
end;

anonymous block completed
Nocache sequence: 28.37 seconds
Caceh 20 sequence: 1.83 seconds

性能差异还是相当明显的。