Note: Tested in Oracle 19.27 on AIX and Linux
1. Test Setup
We create one TDE tablespace and one table on it, and also one test procedure.
drop tablespace test_ts_tde including contents and datafiles;
create tablespace test_ts_tde datafile '/oratestdb/oradata/testdb/test_ts_tde.dbf'
size 10m autoextend on maxsize 1g extent management local segment space management auto encryption default storage (encrypt);
drop table test_tab_tde;
create table test_tab_tde(grp number, id number, name varchar2(100), ts timestamp with time zone) tablespace test_ts_tde;
create index test_tab_tde_idx1 on test_tab_tde (grp, id, name, ts) tablespace test_ts_tde;
create index test_tab_tde_idx2 on test_tab_tde (grp, name) tablespace test_ts_tde;
create or replace procedure test_tab_tde_proc (p_grp_cnt number, p_id_cnt number) as
l_start number := dbms_utility.get_time;
type t_tab is table of test_tab_tde%rowtype;
l_tab t_tab := t_tab();
l_ins_cnt number := 0;
l_del_cnt number := 0;
l_upd_cnt number := 0;
l_sel_cnt number := 0;
begin
select ceil(level/p_id_cnt), level, rpad('abc_'||level, 100, 'x'), systimestamp bulk collect into l_tab from dual connect by level <= p_id_cnt;
l_start := dbms_utility.get_time;
for g in 1..p_grp_cnt loop
for i in 1..p_id_cnt loop
l_tab(i).grp := g;
end loop;
forall k in l_tab.first .. l_tab.last save exceptions
insert into test_tab_tde values l_tab(k);
l_ins_cnt := l_ins_cnt + p_id_cnt;
if mod(g, 2) = 1 then
delete from test_tab_tde where grp = g and rownum <= p_id_cnt;
l_del_cnt := l_del_cnt + sql%rowcount;
end if;
if mod(g, 2) = 0 then
update test_tab_tde set name = rpad('abc_'||id, 100, 'x') where grp = g and rownum <= p_id_cnt;
l_upd_cnt := l_upd_cnt + sql%rowcount;
end if;
for c in (select * from test_tab_tde where grp = g and rownum <= p_id_cnt)
loop
l_sel_cnt := l_sel_cnt + 1;
end loop;
commit;
end loop;
dbms_output.put_line('test_tab_tde elapsed='||round((dbms_utility.get_time-l_start)/100, 2)||
', Insert = '||l_ins_cnt||', Delete = '||l_del_cnt||', Update = '||l_upd_cnt||', Select = '||l_sel_cnt);
end;
/
drop tablespace test_ts_non including contents and datafiles;
create tablespace test_ts_non datafile '/oratestdb/oradata/testdb/test_ts_non.dbf'
size 10m autoextend on maxsize 1g extent management local segment space management auto;
drop table test_tab_non;
create table test_tab_non(grp number, id number, name varchar2(100), ts timestamp with time zone) tablespace test_ts_non;
create index test_tab_non_idx1 on test_tab_non (grp, id, name, ts) tablespace test_ts_non;
create index test_tab_non_idx2 on test_tab_non (grp, name) tablespace test_ts_non;
create or replace procedure test_tab_non_proc (p_grp_cnt number, p_id_cnt number) as
l_start number := dbms_utility.get_time;
type t_tab is table of test_tab_non%rowtype;
l_tab t_tab := t_tab();
l_ins_cnt number := 0;
l_del_cnt number := 0;
l_upd_cnt number := 0;
l_sel_cnt number := 0;
begin
select ceil(level/p_id_cnt), level, rpad('abc_'||level, 100, 'x'), systimestamp bulk collect into l_tab from dual connect by level <= p_id_cnt;
l_start := dbms_utility.get_time;
for g in 1..p_grp_cnt loop
for i in 1..p_id_cnt loop
l_tab(i).grp := g;
end loop;
forall k in l_tab.first .. l_tab.last save exceptions
insert into test_tab_non values l_tab(k);
l_ins_cnt := l_ins_cnt + p_id_cnt;
if mod(g, 2) = 1 then
delete from test_tab_non where grp = g and rownum <= p_id_cnt;
l_del_cnt := l_del_cnt + sql%rowcount;
end if;
if mod(g, 2) = 0 then
update test_tab_non set name = rpad('abc_'||id, 100, 'x') where grp = g and rownum <= p_id_cnt;
l_upd_cnt := l_upd_cnt + sql%rowcount;
end if;
for c in (select * from test_tab_non where grp = g and rownum <= p_id_cnt)
loop
l_sel_cnt := l_sel_cnt + 1;
end loop;
commit;
end loop;
dbms_output.put_line('test_tab_non elapsed='||round((dbms_utility.get_time-l_start)/100, 2)||
', Insert = '||l_ins_cnt||', Delete = '||l_del_cnt||', Update = '||l_upd_cnt||', Select = '||l_sel_cnt);
end;
/
2. Run both tests with SQL trace
We run both tests on AIX.
SQL > exec test_tab_tde_proc(1e4, 100);
test_tab_tde elapsed=91.65, Insert = 1000000, Delete = 500000, Update = 500000, Select = 500000
SQL > exec test_tab_non_proc(1e4, 100);
test_tab_non elapsed=42.31, Insert = 1000000, Delete = 500000, Update = 500000, Select = 500000
Here the TDE trc:
********************************************************************************
INSERT INTO test_TAB_TDE VALUES(:B1 ,:B2 ,:B3 ,:B4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 20.99 37.88 8 117577 1458787 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 20.99 37.88 8 117577 1458787 1000000
********************************************************************************
DELETE FROM test_TAB_TDE WHERE GRP = :B2 AND ROWNUM <= :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 2 0
Execute 5000 18.87 33.92 0 24883 3593457 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5001 18.87 33.92 0 24883 3593459 500000
********************************************************************************
SELECT * FROM test_TAB_TDE WHERE GRP = :B2 AND ROWNUM <= :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 2 0
Execute 10000 0.73 1.32 0 0 0 0
Fetch 15000 0.59 1.05 0 61687 0 500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25001 1.33 2.38 0 61687 2 500000
********************************************************************************
UPDATE test_TAB_TDE SET NAME = RPAD('abc_'||ID, 100, 'x') WHERE GRP = :B2 AND ROWNUM <= :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 2 0
Execute 5000 7.93 14.24 0 19816 520264 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5001 7.93 14.24 0 19816 520266 500000
********************************************************************************
INSERT INTO test_TAB_NON VALUES(:B1 ,:B2 ,:B3 ,:B4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 9.57 16.73 8 119206 1430838 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 9.57 16.73 8 119206 1430838 1000000
********************************************************************************
DELETE FROM test_TAB_NON WHERE GRP = :B2 AND ROWNUM <= :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 7.76 13.48 0 24835 3592775 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5001 7.76 13.48 0 24835 3592775 500000
********************************************************************************
SELECT * FROM test_TAB_NON WHERE GRP = :B2 AND ROWNUM <= :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.73 1.27 0 0 0 0
Fetch 15000 0.58 1.00 0 61647 0 500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25001 1.31 2.27 0 61647 0 500000
********************************************************************************
UPDATE test_TAB_NON SET NAME = RPAD('abc_'||ID, 100, 'x') WHERE GRP = :B2 AND ROWNUM <= :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 3.94 6.82 0 19815 520240 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5001 3.94 6.82 0 19815 520240 500000
3. Perf TOP
Here the perf top output of both tests runing on Linux.
-- TDE
SQL > exec test_tab_tde_proc(1e4, 100);
test_tab_tde elapsed=24.86, Insert = 1000000, Delete = 500000, Update = 500000, Select = 500000
$ > perf top -p 1438084 -d 2
Overhead Shared Object Symbol
5.76% oracle [.] l9_EncryptCFB128_RIJ128_AES_NI.key_128_s
5.05% oracle [.] kcbchg1_main
4.14% oracle [.] kcrfw_redo_gen_ext
3.58% oracle [.] __intel_avx_rep_memcpy
3.40% [vdso] [.] __vdso_clock_gettime
2.34% oracle [.] kcbgcur
2.32% oracle [.] __intel_avx_rep_memset
2.03% oracle [.] kdkcmp1
1.65% oracle [.] kcrfw_enc_redo_gen
1.58% oracle [.] kcrfw_copy_cv
1.56% oracle [.] lxoCpStr
1.28% oracle [.] kcbget
1.25% oracle [.] kcbklbc
1.21% oracle [.] l9_aes128_KeyExpansion_NI
-- Non TDE
SQL > exec test_tab_non_proc(1e4, 100);
test_tab_non elapsed=18.54, Insert = 1000000, Delete = 500000, Update = 500000, Select = 50000
$ > perf top -p 1438084 -d 2
Overhead Shared Object Symbol
6.40% oracle [.] kcbchg1_main
5.31% oracle [.] kcrfw_redo_gen_ext
4.24% oracle [.] __intel_avx_rep_memcpy
2.61% oracle [.] kcbgcur
2.45% oracle [.] lxoCpStr
2.31% oracle [.] kcrfw_copy_cv
2.13% oracle [.] kcbhfix_tail
2.12% oracle [.] kdkcmp1
1.61% oracle [.] kcoapl
1.58% [vdso] [.] __vdso_clock_gettime
1.56% oracle [.] ktuchg2
1.49% oracle [.] lxsCntDisp
1.41% oracle [.] kcbget
1.36% oracle [.] kdiins1
1.35% oracle [.] kcbklbc
1.28% oracle [.] kslfre
1.17% oracle [.] _intel_fast_memcpy
1.16% oracle [.] kslgetl
1.09% oracle [.] sxorchk
0.90% oracle [.] ksl_get_shared_latch_int
-- check AES-NI
$ > grep -o aes /proc/cpuinfo | head -n 1
aes
