使用Oracle的TDE特性加密

评估性能开销

我的客户询问的第一组问题之一就是“TDE 对我的应用程序的一般性能影响如何？”Oracle 文档中有一小部分论述了一般情况下 TDE 对相关应用程序性能的影响。但是我的客户希望获得一些具体的统计信息，以帮助他们了解 TDE 如何影响日常进行的有严格时间要求的数据加载过程。

为了满足客户需求，我计算了每天在有严格时间要求的过程中插入到目标表中的平均行数。然后，我在客户端的相同沙箱环境中创建了一个类似的测试表和索引，测量在加密目标列前后插入相同数量的行所花费的时间。时间消耗上的差别让我们更好地了解了在该过程中对列数据进行加密所造成的“性能损失”。列表 1 是我如何使用 SQL*Plus 执行该操作的示例。

SQL> CONNECT system
Enter password:
Connected.
SQL> -- Configure Oracle-Managed (Data) Files
SQL> ALTER SYSTEM
2 SET db_create_file_dest = '/data01/oracle/'
3 SCOPE = MEMORY;

System altered.

SQL> -- Create two new tablespaces for the demo,
SQL> -- one for data segments, one for index segments
SQL> CREATE TABLESPACE data_001
2 DATAFILE SIZE 1G;

Tablespace created.

SQL> CREATE TABLESPACE indx_001
2 DATAFILE SIZE 500M;

Tablespace created.

SQL> -- Create a user for the demo
SQL> CREATE USER app_001 IDENTIFIED BY app
2 DEFAULT TABLESPACE data_001
3 TEMPORARY TABLESPACE temp
4 QUOTA UNLIMITED ON data_001
5 QUOTA UNLIMITED ON indx_001;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO app_001;

Grant succeeded.

SQL> -- Work as the demo user
SQL> CONNECT app_001/app;
Connected.
SQL> -- Create the demo table in the default tablespace
SQL> CREATE TABLE app_001.transactions (
2 trans_id INTEGER
3 CONSTRAINT transactions_pk PRIMARY KEY
4 USING INDEX TABLESPACE indx_001,
5 credit_card INTEGER NOT NULL
6 );

Table created.

SQL> -- Create an index in the INDX_001 tablespace
SQL> CREATE INDEX app_001.transactions_ndx1
2 ON app_001.transactions(credit_card)
3 TABLESPACE indx_001;

Index created.

SQL> -- Time how long it takes to load data in the clear
SQL> SET TIMING ON;
SQL> BEGIN
2 -- AMEX
3 FOR i IN 1 .. 100000 LOOP
4 INSERT INTO app_001.transactions(trans_id, credit_card)
5 VALUES (
6 i,
7 '34' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999))
8 );
9 END LOOP;
10 COMMIT;
11 -- VISA
12 FOR i IN 100001 .. 400000 LOOP
13 INSERT INTO app_001.transactions(trans_id, credit_card)
14 VALUES (
15 i,
16 '4' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999))
17 );
18 END LOOP;
19 COMMIT;
20 -- MASTERCARD
21 FOR i IN 400001 .. 500000 LOOP
22 INSERT INTO app_001.transactions(trans_id, credit_card)
23 VALUES (
24 i,
25 '54' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999))
26 );
27 END LOOP;
28 COMMIT;
29 END;
30 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:56.14
SQL> SET TIMING OFF;
SQL> -- Remove existing synthetic data
SQL> TRUNCATE TABLE app_001.transactions;

Table truncated.

SQL> -- Enable encryption of the credit card column
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card ENCRYPT NO SALT);

Table altered.

SQL> -- Time how long it takes to load encrypted data
SQL> SET TIMING ON;
SQL> BEGIN
2 -- AMEX
3 FOR i IN 1 .. 100000 LOOP
4 INSERT INTO app_001.transactions(trans_id, credit_card)
5 VALUES (
6 i,
7 '34' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999))
8 );
9 END LOOP;
10 COMMIT;
11 -- VISA
12 FOR i IN 100001 .. 400000 LOOP
13 INSERT INTO app_001.transactions(trans_id, credit_card)
14 VALUES (
15 i,
16 '4' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999))
17 );
18 END LOOP;
19 COMMIT;
20 -- MASTERCARD
21 FOR i IN 400001 .. 500000 LOOP
22 INSERT INTO app_001.transactions(trans_id, credit_card)
23 VALUES (
24 i,
25 '54' || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999))
26 );
27 END LOOP;
28 COMMIT;
29 END;
30 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:16.31
SQL> SET TIMING OFF;