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)