One of the cool thing in 12c is that the ability to default
a sequence.nextval to a column.
While prior to 12c, we could achieve the same using a simple
trigger in place.
Here is a simple test case to show – how things got improved
in 12c by defaulting sequence.nextval to a column.
demo@ORA12C>
create table t1(x int,y varchar2(300));
Table created.
demo@ORA12C>
create sequence s;
Sequence
created.
demo@ORA12C>
create table t2(x int default s.nextval,y
varchar2(300));
Table created.
demo@ORA12C>
create or replace trigger t1_trig
2
before insert on t1
3 for
each row
4
begin
5
if :new.x is null then
6
:new.x := s.nextval;
7
end if;
8 end;
9 /
Trigger
created.
So we created two tables
o
Table T1 with trigger in place to populate the
column X with sequence.nextval
o
Table T2 with sequence.nextval defaulted to the
column X
demo@ORA12C>
insert into t1(y) select object_name from big_table;
1000000 rows
created.
demo@ORA12C>
insert into t2(y) select object_name from big_table;
1000000 rows
created.
demo@ORA12C>
exit
Disconnected
from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
Tkprof shows this.
insert into
t1(y) select object_name from big_table
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.00 0.00 0 0 0 0
Execute 1
150.21
151.03 18570 32498
54095 1000000
Fetch 0
0.00 0.00 0 0 0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
150.21 151.04 18570
32498 54095 1000000
Misses in
library cache during parse: 1
Misses in
library cache during execute: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ----------
---------------------------------------------------
0 0 0
LOAD TABLE CONVENTIONAL T1 (cr=82511
pr=18570 pw=0 time=243474242 us starts=1)
1000000 1000000
1000000 TABLE ACCESS FULL
BIG_TABLE (cr=24709 pr=18570 pw=0 time=743301 us starts=1 cost=5125
size=38000000 card=1000000)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
direct path read 3 0.01 0.04
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
insert into
t2(y) select object_name from big_table
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.00 0.00 0 0 0 0
Execute 1
12.44
12.96 18570 26527
105359 1000000
Fetch 0
0.00 0.00 0 0 0 0
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 2
12.44 12.96 18570
26527 105359 1000000
Misses in
library cache during parse: 1
Misses in
library cache during execute: 1
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (1st) Rows
(avg) Rows (max) Row Source Operation
----------
---------- ----------
---------------------------------------------------
0 0 0
LOAD TABLE CONVENTIONAL T2 (cr=76539
pr=18570 pw=0 time=82169362 us starts=1)
1000000
1000000 1000000 SEQUENCE
S (cr=68585 pr=18570 pw=0 time=316161124 us starts=1)
1000000
1000000 1000000 TABLE ACCESS FULL BIG_TABLE (cr=18574
pr=18570 pw=0 time=519706 us starts=1 cost=5125 size=38000000 card=1000000)
Elapsed times
include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
PGA memory operation 1 0.00 0.00
direct path read 4 0.05 0.08
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
A comparison from runstats tool report the same.
Connected to:
Oracle Database
12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
demo@ORA12C>
truncate table t1;
Table
truncated.
demo@ORA12C>
truncate table t2;
Table
truncated.
demo@ORA12C>
exec runstats_pkg.rs_start;
PL/SQL
procedure successfully completed.
demo@ORA12C>
insert into t1(y) select object_name from big_table;
1000000 rows
created.
demo@ORA12C>
exec runstats_pkg.rs_middle;
PL/SQL
procedure successfully completed.
demo@ORA12C>
insert into t2(y) select object_name from big_table;
1000000 rows
created.
demo@ORA12C>
exec runstats_pkg.rs_stop(1000);
Run1
ran in 3595 cpu hsecs
Run2
ran in 624 cpu hsecs
run 1 ran in
576.12% of the time
Name Run1 Run2 Diff
STAT...redo
size 86,105,192 86,103,620 -1,572
LATCH.active
service list 2,919 918 -2,001
STAT...recursive
cpu usage 2,465 212 -2,253
STAT...Elapsed
Time 3,672 1,167 -2,505
STAT...DB
time 3,674 1,165 -2,509
STAT...CPU used
when call star 3,596 622 -2,974
STAT...CPU used
by this sessio 3,596 622 -2,974
STAT...dirty
buffers inspected 2,519 6,232 3,713
LATCH.simulator
hash latch 8,922 13,084 4,162
LATCH.simulator
lru latch 3,766 8,244 4,478
LATCH.JS queue
state obj latch 7,560 2,394 -5,166
LATCH.cache
buffers lru chain 4,504 10,010 5,506
LATCH.enqueue
hash chains 317,189 306,351
-10,838
LATCH.object
queue header oper 28,528 41,116 12,588
LATCH.checkpoint
queue latch 9,486 22,116 12,630
LATCH.cache
buffers chains 867,666 884,259 16,593
STAT...session
uga memory 65,432 56
-65,376
STAT...session
pga memory max 65,536 0
-65,536
STAT...session
pga memory -131,072 -65,536 65,536
STAT...logical
read bytes from########################
-122,880
STAT...session
uga memory max 192,584 65,424
-127,160
STAT...session
cursor cache hi 1,050,001 50,003
-999,998
STAT...execute
count 1,050,006 50,006
-1,000,000
STAT...opened
cursors cumulati 1,050,006 50,006
-1,000,000
STAT...calls to
get snapshot s 1,100,086 100,084
-1,000,002
STAT...recursive
calls 1,050,090 50,084
-1,000,006
LATCH.shared
pool 1,001,123 687
-1,000,436
STAT...Effective
IO time 306,332 5,162,302
4,855,970
STAT...file io
wait time 36,925,506 20,782,391 -16,143,115
Run1 latches
total versus runs -- difference and pct
Run1 Run2 Diff Pct
5,623,724
4,660,079 -963,645 120.68%
PL/SQL
procedure successfully completed.
demo@ORA12C>
demo@ORA12C>
So when you upgrade/migrate to 12c, think about revisiting
those triggers to default values if possible, since they yield more benefits to
performance (in-terms of CPU and number of recursive calls).
No comments:
Post a Comment