Sequence Cache and No Cache comparison

-- Comparing NOCACHE and CACHE sequence 
-- emphasizing the UPDATE and COMMIT impacts on the SYS.SEQ$ 

CREATE SEQUENCE sq_nocache NOCACHE;
CREATE SEQUENCE sq_defaut_cache;
CREATE SEQUENCE sq_big_cache CACHE 100000;

-- Create a test table
CREATE TABLE t1 (A INT);

-- ******************************************
-- START NO CACHE
TRUNCATE TABLE t1;

-- Append to the trace files - making it easier to find
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'trc_no_cache';

ALTER SESSION SET SQL_TRACE = TRUE;

/** OR use

BY SYSTEM
exec dbms_monitor.session_trace_enable(session_id => 10, serial_num => 7, waits => true, binds => false);

exec dbms_monitor.session_trace_disable(session_id => 10, serial_num => 7);

BY USER
exec dbms_session.session_trace_enable(waits => true, binds => false);

exec dbms_session.session_trace_disable();
**/

INSERT INTO t1 
SELECT sq_nocache.nextval x FROM dual 
connect by level <=100000;

ALTER SESSION SET SQL_TRACE = FALSE;

-- END NO CACHE


-- ******************************************
-- START DEFAULT CACHE
TRUNCATE TABLE t1;

-- Append to the trace files - making it easier to find
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'trc_default_cache';

ALTER SESSION SET SQL_TRACE = TRUE;

/** 
BY SYSTEM
exec dbms_monitor.session_trace_enable(session_id => 10, serial_num => 7, waits => true, binds => false);

exec dbms_monitor.session_trace_disable(session_id => 10, serial_num => 7);

BY USER
exec dbms_session.session_trace_enable(waits => true, binds => false);

exec dbms_session.session_trace_disable();
**/

INSERT INTO t1 
SELECT sq_defaut_cache.nextval x FROM dual 
connect by level <=100000;

ALTER SESSION SET SQL_TRACE = FALSE;

-- END NO CACHE

-- ******************************************
-- START BIG CACHE
TRUNCATE TABLE t1;

-- Append to the trace files - making it easier to find
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'trc_big_cache';

ALTER SESSION SET SQL_TRACE = TRUE;

/** 
BY SYSTEM
exec dbms_monitor.session_trace_enable(session_id => 10, serial_num => 7, waits => true, binds => false);

exec dbms_monitor.session_trace_disable(session_id => 10, serial_num => 7);

BY USER
exec dbms_session.session_trace_enable(waits => true, binds => false);

exec dbms_session.session_trace_disable();
**/

INSERT INTO t1 
SELECT sq_big_cache.nextval x FROM dual 
connect by level <=100000;

ALTER SESSION SET SQL_TRACE = FALSE;

-- END NO CACHE


-- ******************************************
-- ******************************************
-- ANALYSIS TKPROF

tkprof trc_no_cache.trc no_cache.prf EXPLAIN=username/PASSWORD 

tkprof trc_default_cache.trc default_cache.prf EXPLAIN=username/PASSWORD 

tkprof trc_big_cache.trc no_cache.prf EXPLAIN=username/PASSWORD 

-- ******************************************
-- NO CACHE
SQL ID: 4m7m0t6fjcs5x
Plan Hash: 1935744642
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10 
where
 obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    99999      1.32       1.11          0          0          0           0
Execute  99999      6.86       7.86          5     100034     203240       99999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   199998      8.19       8.98          5     100034     203240       99999

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  SEQ$ (cr=1 pr=2 pw=0 TIME=0 us)
      1   INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=1 pw=0 time=0 us cost=0 size=70 card=1)(object id 79)
      
-- ******************************************
-- DEFAULT CACHE which is 20

SQL ID: 4m7m0t6fjcs5x
Plan Hash: 1935744642
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10 
where
 obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     5000      0.12       0.08          0          0          0           0
Execute   5000      0.34       0.39          0       5000      10141        5000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10000      0.46       0.47          0       5000      10141        5000

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  SEQ$ (cr=1 pr=0 pw=0 TIME=0 us)
      1   INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 TIME=0 us COST=0 SIZE=70 card=1)(OBJECT ID 79)
      
-- ******************************************
-- BIG CACHE which is 100000

SQL ID: 4m7m0t6fjcs5x
Plan Hash: 1935744642
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10 
where
 obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          2           1

Misses in library cache during parse: 0
Optimizer MODE: CHOOSE
Parsing user id: SYS   (recursive depth: 1)