Monday, April 27, 2026

Blog List

Posted by at

One Oracle TDE Performance Test

In this Blog, we will test the performance difference of SQLs on TDE table and non TDE table.

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;
/
Similarily, we create one NON TDE tablespace and one table on it, and also one test procedure. 

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
The elapsed time for TDE operations is more than double the time required for non-TDE operations.

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
Here the non TDE trc: 

********************************************************************************
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
Above traces showed that although Consistent Gets ("query") and Current Gets ("current") stats are very closed, TDE doubled elaspsed time compared to non-TDE. All 4 SQLs are CPU intensive (the differences between "cpu" and "elapsed" are due to AIX special accounting).


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
Posted by at
Subscribe to: Comments (Atom)