Oracle Transparent Data Encryption - 性能测试

对于数据列的透明加密，不可避免的会产生性能影响;对于Oracle Database 11gR2，Oracle声称对于表空间级别的加密，通过加密硬件可以实现几乎0性能影响。

New in Oracle Database 11g Release 2:

Cryptographic hardware acceleration delivers near-zero performance impact of TDE tablespace encryption

When Oracle Database 11g Release 2 Patchset 1 (11.2.0.2) is installed on an Intel Server with AES-NI capability, the data throughput is up to 8 times higher for decryption and up to 10 times higher for encryption (requires patch 10080579) compared to CPUs without hardware acceleration.

对于列级别的加密，可以简要的测试一下其性能。

在以下测试中：

加密INSERT 10，000记录：时间1.39秒 删除：3.29.44

非加密INSRT 10,000记录：时间0.87秒 删除：    5.28

因为删除涉及到了查询的全表扫描，实际上还涵盖了查询的性能影响，根据测试表明对于INSERT操作，加密使得性能下降了1倍，而删除则更慢。

SQL> create table tde(id number,name varchar2(50) encrypt);

Table created.

Elapsed: 00:00:00.01
SQL> create table nor(id number,name varchar2(50));

Table created.

Elapsed: 00:00:00.01
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.05
SQL> begin
  2  for i in 1 .. 10000 loop
  3  insert into tde values(i,'eygle'||i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.39
SQL> l 3
  3* insert into tde values(i,'eygle'||i);
SQL> c/tde/nor
  3* insert into nor values(i,'eygle'||i);
SQL> l
  1  begin
  2  for i in 1 .. 10000 loop
  3  insert into nor values(i,'eygle'||i);
  4  end loop;
  5  commit;
  6* end;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87

进行删除测试：

SQL> begin
  2  for i in 1 .. 10000 loop
  3  delete from tde where name='eygle'||i;
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:29.44
SQL>
SQL>
SQL>
SQL>
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.03
SQL> l
  1* alter system switch logfile
SQL> ed
Wrote file afiedt.buf

  1* alter system switch logfile
SQL> begin
  2  for i in 1 .. 10000 loop
  3  delete from nor where name='eygle'||i;
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.28

参考文献：
http://www.oracle.com/technetwork/database/options/advanced-security/index-099011.html