Sequence Cache and No Cache comparison
Jump to navigation
Jump to search
-- 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)