Ittichai at 20:36, 14 May 2012

2012-05-14T20:36:15Z

Ittichai: Created page with "

  -- 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..."

2012-05-14T20:35:09Z

Created page with "<pre> -- 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..."

New page

<pre>

-- 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)
</pre>

[[Category: SQL_Scripts]]
[[Category: Oracle_Sequences]]

Sequence Cache and No Cache comparison - Revision history

Ittichai at 20:36, 14 May 2012

Ittichai: Created page with "
-- 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..."

@@ Line 190: / Line 190: @@
 </pre>
-[[Category: SQL_Scripts]]
+[[Category: Database Scripts]]
 [[Category: Oracle_Sequences]]