Sequence CACHE or NOCACHE?

Sequence created with CACHE settings enhance performance in single user / multi user environments

scott@11GR1> create sequence s_cache CACHE 1000;
Sequence created.

scott@11GR1> create sequence s_no_cache NOCACHE ;

Sequence created.

scott@11GR1> declare
  2     l_val number;
  3     l_start_time number;
  4     l_end_time number;
  5  begin
  6     runstats_pkg.rs_start;
  7
  8     l_start_time := dbms_utility.get_time;
  9
 10     for i in 1..1000000
 11     loop
 12             select s_cache.nextval
 13             into l_val
 14             from dual;
 15     end loop;
 16
 17     l_end_time := dbms_utility.get_time;
 18     dbms_output.put_line (' Total Time taken = '|| ( l_end_time - l_start_time) );
 19
 20     runstats_pkg.rs_middle;
 21
 22     l_start_time := dbms_utility.get_time;
 23     for i in 1..1000000
 24     loop
 25             select s_no_cache.nextval
 26             into l_val
 27             from dual;
 28     end loop;
 29
 30     l_end_time := dbms_utility.get_time;
 31     dbms_output.put_line (' Total Time taken = '|| ( l_end_time - l_start_time) );
 32  end;
 33  /
 Total Time taken = 2488
 Total Time taken = 20886

PL/SQL procedure successfully completed.

Elapsed: 00:03:53.84

Elapsed time is quite HUGELY different.... the cached sequence is obviously  faster.

scott@11GR1> exec runstats_pkg.rs_stop(1000000);
Run1 ran in 2488 hsecs
Run2 ran in 27870 hsecs
run 1 ran in 8.93% of the time

Name                                  Run1        Run2        Diff
LATCH.Change Notification Hash       1,291   1,001,329   1,000,038
LATCH.session idle bit               1,345   1,001,645   1,000,300
LATCH.sequence cache             3,002,018   2,000,030  -1,001,988
STAT...consistent gets - exami       1,001   1,020,450   1,019,449
STAT...consistent gets from ca       1,018   1,023,803   1,022,785
STAT...consistent gets               1,018   1,023,803   1,022,785
STAT...calls to kcmgas               1,041   1,035,784   1,034,743
LATCH.MinActiveScn Latch             2,173   2,001,865   1,999,692
STAT...enqueue requests              3,010   2,006,178   2,003,168
STAT...enqueue releases              3,009   2,006,178   2,003,169
STAT...redo entries                  2,012   2,008,815   2,006,803
LATCH.session allocation             2,259   2,013,396   2,011,137
STAT...calls to get snapshot s   1,002,008   3,020,923   2,018,915
LATCH.DML lock allocation           12,771   2,054,305   2,041,534
STAT...session pga memory max      131,072   3,137,420   3,006,348
STAT...db block gets                 3,090   3,055,841   3,052,751
STAT...db block gets from cach       3,090   3,055,841   3,052,751
STAT...db block changes              4,069   4,009,523   4,005,454
LATCH.enqueue hash chains            6,749   4,023,192   4,016,443
STAT...session logical reads         4,108   4,079,644   4,075,536
STAT...physical read total byt      40,960   4,857,856   4,816,896
STAT...calls to kcmgrs               5,030   5,002,112   4,997,082
LATCH.undo global data               3,096   5,482,793   5,479,697
LATCH.row cache objects              8,238   7,016,514   7,008,276
STAT...recursive calls           1,014,042  15,006,097  13,992,055
LATCH.cache buffers chains          20,510  18,290,102  18,269,592
STAT...physical write total by           0 151,044,096 151,044,096
STAT...undo change vector size     287,024 284,172,572 283,885,548
STAT...redo size                   891,976     888,577,148 887,685,172

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
   6,068,802  47,504,769  41,435,967     12.78%

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23

Remember -- LATCHES are locks, locks imply serialization, serialization implies less scalable -- longer waits. Also only 891K redo for "cache" but 888M redo for "nocache".