There are days when I honestly want to hang up my spurs and retire. To be honest, the problem is not with TDE; it’s with some people not understanding how TDE works.
There are a couple ways you can implement Oracle TDE, in this example; we’re going to be discussing Column Encryption and Tablespace Encryption. It’s important to understand what Oracle does in the background for encrypting and decrypting data for column and tablespace encryption.
When doing column encryption, every row/column goes through an encryption/decryption process. Therefore, if you do a full table scan on 100,000 rows that are encrypted, there will be 100,000 decrypt operations performed and if you’re updating 100,000 rows, there will be 100,000 decrypt and encrypt operations performed. So, you will be seeing a performance impact when doing column encryption.
Other down sides of doing column encryption is you can’t put foreign key restraints on encrypted columns. One of the attack vectors on encrypted columns is to do a statistical analysis on the data to determine what the plain text data is. To get around this, you add salt to the encryption; however, if you do that, then you can not index the encrypted column.
With column encryption, there is the advantage that all supporting objects (indexes, materialized views) columns will be encrypted. This is not true for tablespace encryption.
When doing tablespace encryption (recommended for most use cases) encryption / decryption operations are done at the block level. Therefore, if you have 100,000 rows and there are 200 rows per block, then there will be 500 decryption operations in a full table scan. And there you have it. By doing tablespace encryption, the number of encryption/decryption operations is reduced as compared to column encryption.
Now, the downside of tablespace encryption, if your indexes or materialized views are in a tablespace that is not encrypted, then your indexes or materialized views will not be encrypted. For this reason I always recommend encrypting all tablespaces in the database. (Commonly referred to full database encryption.)
Here are the results of some performance test I’ve done on column based vs. tablespace encryption. Each table has 500,000 rows. All times are in seconds.
| Table | Encryption | Insert Time | Update Time | Full Table scan | |
| T0 | No encryption | 3.75 | 3.508 | 0.785 | |
| T1 | Column Encryption | 29.5 | 90.02 | 16.621 | |
| T2 | Tablespace Encryption | 4.01 | 3.9 | 0.04 |
Here is my code if you’d like to duplicate my test.
set echo on
set timing on
set feedback on
-- cleanup.
drop table rlockard.t0;
drop table rlockard.t1;
drop table rlockard.t2;
drop sequence rlockard.t_seq;
-- check to see if the DATA tablespace is
-- encrypted. No, it's not encrypted, so we're
-- going to put our table with an encrypted column
-- in the DATA tablespace.
select encrypted from dba_tablespaces
where tablespace_name = 'DATA';
create table rlockard.t0 (id number primary key,
n1 number)
tablespace data;
-- we need a sequence for our primary key.
create sequence rlockard.t_seq;
insert into rlockard.t0 (
select rlockard.t_seq.nextval,
sys.dbms_random.value(0,50000)
from dual
connect by level <= 500000);
commit;
select avg(n1) from rlockard.t0;
update rlockard.t0
set n1 = n1*2
where mod(trunc(n1),2) = 0;
commit;
-- number of blocks used by t, where each element nbr is encrypted.
analyze table rlockard.t0 compute statistics;
select blocks - empty_blocks
from dba_tables
where table_name = 'T0'
and owner = 'RLOCKARD';
-- average number of rows in a block where each element is encrypted.
select avg(row_count) from (
select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) row_count
from rlockard.t0
group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
/
-- drop t0 to free up space.
drop table rlockard.t0;
-- create a test table in an unencrypted tablespace and use salt.
-- to keep things simple, we're not going to use salt in this
-- example. Just use simple aes256 encryption.
create table rlockard.t1 (id number primary key,
n1 number encrypt using 'aes256')
tablespace data;
-- populate the table with 500,000 rows.
insert into rlockard.t1 (
select rlockard.t_seq.nextval,
sys.dbms_random.value(0,50000)
from dual
connect by level <= 500000);
commit;
-- this will do a full tablescan and perform 500,000
-- decryption operations.
select avg(n1) from rlockard.t1;
-- will need to decrypt and then encrypt each n1. therefore,
-- there will be 500,000 encryption calculations and aprox
-- 250,000 encryption calculations.
update rlockard.t1
set n1 = n1*2
where mod(trunc(n1),2) = 0;
commit;
-- number of blocks used by t, where each element nbr is encrypted.
analyze table rlockard.t1 compute statistics;
select blocks - empty_blocks
from dba_tables
where table_name = 'T1'
and owner = 'RLOCKARD';
-- average number of rows in a block where each element is encrypted.
select avg(row_count) from (
select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) row_count
from rlockard.t1
group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
/
-- okay, so what happens when we access by primary key and it's not encrypted.
-- this will update 1/2 of the rows where the primary key is an even number.
-- 250,000 updates by primary key.
select encrypted from dba_tablespaces
where tablespace_name = 'USERS';
-- we're going to put t2 into an encrypted tablespace.
create table rlockard.t2 (id number primary key,
n1 number)
tablespace users;
-- insert 500,000 rows, each block will be encrypted. There will be
-- multiple rows stored in each block.
insert into rlockard.t2 (
select rlockard.t_seq.nextval,
sys.dbms_random.value(0,50000)
from dual
connect by level <= 500000);
commit;
-- a full table scan will be performed and
-- each block will be decrypted. We'll get
-- the number of decryption operations in a moment.
select avg(n1) from rlockard.t2;
-- decryption and decryption will happen at the block level.
-- logically mod(trucn(n1),2) = 0 should be evenly distrbuted
-- in the blocks, therefore will still have good peformance.
update rlockard.t2
set n1 = n1*2
where mod(trunc(n1),2) = 0;
commit;
-- number of blocks used by t2 in the encrypted tablespace. This will
-- give us the number of encryption operations.
analyze table rlockard.t2 compute statistics;
select blocks - empty_blocks
from dba_tables
where table_name = 'T2'
and owner = 'RLOCKARD';
-- average number of rows in a block used by t2 in the encrypted tablespace.
-- this will give us the number of rows decrypted in each encryption operation.
select avg(row_count) from (
select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) row_count
from rlockard.t2
group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
/