Motivation
In an older and quite successful blog post of mine I tried to find the “perfect” solution for audit columns. See: sequence-and-audit-columns-with-apex-5-and-12c.
One finding then was that sys_context('userenv','current_user') is considerably faster than the user pseudocolumn.
I recently noticed that this seem to have changed and decided to retest the behavior.
The old test was done against a 12.1.0.1 standard edition database.
The new test is done against a 19.5 enterprise edition (on ATP which features an exadata).
Test setup
Mass inserting 100000 records into empty tables. A very fast select was used to generate 100k rows of null values.
3 columns were filled with data:
IDby a sequence using a cache of 10000created_byfilled with the schema name =>sys_contextorusercreated_onfilled with sysdate
Test 1 (T1) was using sys_context as default value.
Test 2 (T2) was using user as default value.
Test 3 (T3) was using a trigger with sys_context.
Test 4 (T4) was using a trigger with user.
Each test was run once to warm-up the database (and the tablespace). This warm-up run didn’t count. Then each test was run 3 times. Only the execution speed of the inserts were measured.
Results
Test 1 – sys_context as default value
Elapsed:1.23 seconds
Elapsed:1.21 seconds
Elapsed:1.26 seconds
Average: 1.23 s
Test 2 – user as default value
Elapsed:1,32 seconds (This looks suspicious. Maybe the warm-up run didn’t warm up enough)
Elapsed:1,16 seconds
Elapsed:1,19 seconds
Average: 1.22 s
Test 3 – sys_context in trigger
Elapsed:17,33 seconds
Elapsed:17,08 seconds
Elapsed:17,05 seconds
Average: 17.15 s
Test 4 – user in trigger
Elapsed:17,03 seconds
Elapsed:16,71 seconds
Elapsed:16,97 seconds
Average: 16.90 s
Comparison
My test shows that user was even a tiny tiny bit faster than the sys_context. Which means the previous 12.1 recommendation not to use “user” anymore is now outdated. The difference is so small that you can choose whatever you want.
User is fast now!
The main message still is, if you can get rid of the trigger, then do it. The difference between a default value logic and the trigger is huge! But even in the trigger logic there seems to be a slight performance advantage for user now.
If you are still on 12.1 or 12.2 you should run your own tests and compare the results. If you are on 19c or higher I would now use user again.
Test scripts
Create statements
-- cleanup
drop table test_insert_perf_t1_sys_context purge;
drop table test_insert_perf_t2_user purge;
drop table test_insert_perf_t3_trigger purge;
drop sequence test_insert_perf_t3_trigger_seq;
drop table test_insert_perf_t4_trigger purge;
drop sequence test_insert_perf_t4_trigger_seq;
-- create objects
create table test_insert_perf_t1_sys_context
(id number generated by default on null as identity (cache 10000) primary key
,created_by varchar2(128) default on null sys_context('userenv','current_user') not null
,created_on date default on null sysdate not null);
create table test_insert_perf_t2_user
(id number generated by default on null as identity (cache 10000) primary key
,created_by varchar2(128) default on null user not null
,created_on date default on null sysdate not null);
create table test_insert_perf_t3_trigger
(id number not null primary key
,created_by varchar2(128) not null
,created_on date not null);
create sequence test_insert_perf_t3_trigger_seq cache 10000;
create or replace trigger test_insert_perf_t3_trigger_bri
before insert on test_insert_perf_t3_trigger
for each row
begin
:new.id := test_insert_perf_t3_trigger_seq.nextval;
:new.created_by := sys_context('userenv','current_user');
:new.created_on := sysdate;
end;
/
create table test_insert_perf_t4_trigger
(id number not null primary key
,created_by varchar2(128) not null
,created_on date not null);
create sequence test_insert_perf_t4_trigger_seq cache 10000;
create or replace trigger test_insert_perf_t4_trigger_bri
before insert on test_insert_perf_t4_trigger
for each row
begin
:new.id := test_insert_perf_t4_trigger_seq.nextval;
:new.created_by := user;
:new.created_on := sysdate;
end;
/
Run statements
-------------------------------------------------------------------
-- run tests (insert 100000 rows)
-------------------------------------------------------------------
-- Test 1 --------------------------------------
set serveroutput on
set time on;
declare
v_time number;
begin
v_time := dbms_utility.get_time;
-- insert 100000 records
insert into test_insert_perf_t1_sys_context(id)
(select n1.nr
from (select cast(null as number) nr from dual connect by level <=100) n1
cross join (select cast(null as number) nr from dual connect by level <=1000) n2
);
sys.dbms_output.put_line('Elapsed:'||to_char((dbms_utility.get_time - v_time) / 100)||' seconds');
rollback;
end;
/
-- Test 2 --------------------------------------
set serveroutput on
set time on;
declare
v_time number;
begin
v_time := dbms_utility.get_time;
-- insert 100000 records
insert into test_insert_perf_t2_user(id)
(select n1.nr
from (select cast(null as number) nr from dual connect by level <=100) n1
cross join (select cast(null as number) nr from dual connect by level <=1000) n2
);
sys.dbms_output.put_line('Elapsed:'||to_char((dbms_utility.get_time - v_time) / 100)||' seconds');
rollback;
end;
/
-- Test 3 --------------------------------------
set serveroutput on
set time on;
declare
v_time number;
begin
v_time := dbms_utility.get_time;
-- insert 100000 records
insert into test_insert_perf_t3_trigger(id)
(select n1.nr
from (select cast(null as number) nr from dual connect by level <=100) n1
cross join (select cast(null as number) nr from dual connect by level <=1000) n2
);
sys.dbms_output.put_line('Elapsed:'||to_char((dbms_utility.get_time - v_time) / 100)||' seconds');
rollback;
end;
/
-- Test 4 --------------------------------------
set serveroutput on
set time on;
declare
v_time number;
begin
v_time := dbms_utility.get_time;
-- insert 100000 records
insert into test_insert_perf_t4_trigger(id)
(select n1.nr
from (select cast(null as number) nr from dual connect by level <=100) n1
cross join (select cast(null as number) nr from dual connect by level <=1000) n2
);
sys.dbms_output.put_line('Elapsed:'||to_char((dbms_utility.get_time - v_time) / 100)||' seconds');
rollback;
end;
/
svenweller 