测试TDE对于表DML的性能影响

1.      为Transparent Data Encryption指定 Wallet存储路径

 

1.1  创建目录 ‘/home/oracle/ora_wallet/’ 用于存放wallet

 

1.2  修改sqlnet.ora.

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)

        (METHOD_DATA=

          (DIRECTORY=/home/oracle/ora_wallet/)))

 

1.3  用sysdba登录数据库给wallet设置密码

SQL>alter system set encryption key identified by [password];

如果返回错误 ORA-28368: cannot auto-create wallet, 这里必须登录到服务器本机，远程登录无效， 另外如果修改了sqlnet.ora需要重新登录sysdba.

这一步过后会有一个新的文件 ‘ewallet.p12’ 被创建在 ‘/home/oracle/ora_wallet/’ 这个目录下.

 

1.4  开启 encryption wallet ，如果没开启的话

SQL>alter system set encryption wallet open identified by [password];

以下语句用于关闭wallet.

SQL>alter system set encryption wallet close;

 

2.      测试TDE列加密

 

2.1  创建一个有TDE加密的表，和 DBA_OBJECTS同结构

CREATE TABLE TEST_TABLE_TDE

(

OWNER        VARCHAR2(30) ,

OBJECT_NAME      VARCHAR2(128) ,

SUBOBJECT_NAME      VARCHAR2(30) ,

OBJECT_ID      NUMBER ENCRYPTUSING 'AES256'  NO SALT   PRIMARY KEY ,

DATA_OBJECT_ID      NUMBER ,

OBJECT_TYPE      VARCHAR2(19) ,

CREATED      DATE     ,

LAST_DDL_TIME      DATE   ENCRYPT  USING 'AES256', 

TIMESTAMP      VARCHAR2(19) ,

STATUS        VARCHAR2(7) ,

TEMPORARY      VARCHAR2(1) ,

GENERATED      VARCHAR2(1) ,

SECONDARY      VARCHAR2(1) ,

NAMESPACE      NUMBER ,

EDITION_NAME      VARCHAR2(30)

);

By default, transparent data encryption adds salt to clear text before encrypting it. This makes it harder for attackers to steal data through a brute force attack. However, if you plan to index the encrypted column, you must use NO SALT. Transparent data encryption also enables you to specify a nondefault encryption algorithm. You can choose from one of the following algorithms: ·         3DES168 ·         AES128 ·         AES192 (default) ·         AES256 There cannot be two or more different encryption algorithms within the same table.

创建另外一张没有TDE加密的表，和DBA_OBJECTS同结构。

SQL> CREATE TABLE TEST_TABLE  AS SELECT * FROM DBA_OBJECTS WHERE 1=2;

接下来分别往两张表里插入，修改和删除数据。以下是测试结果：

INSERT
	First Time		Second Time		Third Time
	TEST_TABLE_TDE	TEST_TABLE	TEST_TABLE_TDE	TEST_TABLE	TEST_TABLE_TDE	TEST_TABLE
Elapsed	13 min 1 sec	6 min 54 sec	12 min 28 sec	6 min 59 sec	12 min 41 sec	6 min 37 sec
REDO	3517.95M	2037.59M	3517.84M	2037.68M	3517.79M	2037.57M
UNDO	89.59M	66.09M	89.57M	66.08M	89.58M	66.09M
UPDATE
	First Time		Second Time		Third Time
	TEST_TABLE_TDE	TEST_TABLE	TEST_TABLE_TDE	TEST_TABLE	TEST_TABLE_TDE	TEST_TABLE
Elapsed	5 min 14 sec	2 min 51 sec	1 min 14 sec	47 sec	4 min 6 sec	46 sec
REDO	41.42M	23.24M	8.61M	6.25M	51.7M	14.35M
UNDO	4.67M	2.48M	3.34M	2.19M	20.67M	6.58M
DELETE
	First Time		Second Time		Third Time
	TEST_TABLE_TDE	TEST_TABLE	TEST_TABLE_TDE	TEST_TABLE	TEST_TABLE_TDE	TEST_TABLE
Elapsed	3 min 12 sec	1 min 22 sec	2 min 38 sec	1 min 19 sec	5 min 20 sec	3 min 18 sec
REDO	22.65M	18.56M	59.11M	48.63M	200.75M	165.51M
UNDO	14.38M	10.74M	39.63M	29.64M	133.92M	100.39M
SUMMARY
	对于INSERT的影响		对于UPDATE的影响		对于DELETE的影响
执行时间	+ 46.3%		+ 58.4%		+ 46.4%
Redo	+ 42.1%		+ 56.9%		+ 17.6%
Undo	+ 26.2%		+ 60.8%		+ 25.1%