Software Only Encryption = 00:02:16.41 Query on Clear Text = 00:00:28.80 Accelerated with AES-NI = 00:00:45.30 This means that with AES-NI the query completed 3x faster than that using software only encryption and 1.55x slower compared to using no encryption at all. Clearly if you are using Oracle TDE for encryption then you are going to see significant performance gains from using AES-NI for acceleration.If you are curious why Oracle decided not to cache the data at all and instead chose to read from disk and decrypt exactly the same query each time, we’ll look into that in Part II of this post. Elapsed: 00:00:28.74And the same execution plan value for physical reads.Statistics———————————————————-…1038269 physical reads…The first time, Oracle decided not to cache the LINEITEM table in the buffer cache SGA. When I ran the query a second time, it fetched the data from disk again and continued to do this for every time the query was run with consistent timing each time.What about the same query on encrypted data? Oracle TDE is controlled by hidden parameters, so we need a query to see the parameters:SQL> li1* select a .ksppinm “Parameter”, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value” from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx AND a.indx = c.indx AND ksppinm like ‘%encryption%’SQL> /Parameter——————————————————————————–Session Value——————————————————————————–Instance Value——————————————————————————–_use_platform_encryption_libTRUETRUE_use_hybrid_encryption_modeTRUETRUE_db_disable_temp_encryptionFALSEFALSETo use AES-NI the parameter _use_platform_encryption_lib needs to be set to TRUE. To use AES-NI for both encryption and decryption _use_hybrid_encryption_mode needs to be set to FALSE. We can test the impact of hardware encryption acceleration performance by turning these parameters on and off, for example:SQL> alter system set “_use_platform_encryption_lib”=FALSE scope=both;System altered.I restarted the database and opened the wallet before running queries against the encrypted data without AES-NI for hardware encryption acceleration. In this case Oracle is using software only to do the decryption and is not using the AES-NI at all.SQL> alter system set wallet open identified by “oraclepassword”;System altered. The query again returned 4 rows and the timing value is 136 seconds, including the time to do the software only decryption.Elapsed: 00:02:16.41Our Execution plan shows “TABLE ACCESS FULL” on “LINEITEM” and again our statistics show that the full table scan was based on physical reads. Each time that the same statement was re-executed, the data was read from disk. This means that each time the data was decrypted with software acceleration it took approximately 4.7x longer than the same query on clear text.Next, I restarted the database but this time enabled hardware accelerated encryption to use AES-NI.SQL> alter system set “_use_platform_encryption_lib”=TRUE scope=both;System altered.I re-ran the same query with the following timing value:Elapsed: 00:00:45.30As expected, our Execution plan again shows “TABLE ACESS FULL” on “LINEITEM” with the full table scan based on physical reads. The same query was run each each time (the data was read from disk and decrypted). In this case the difference was making use of AES-NI for acceleration.Timing the same query consistently gave us the following results: One interesting aspect of working with real world implementations of database technology is that they often raise interesting questions about how that technology is used. One such recent question concerned the real performance benefits of the Intel Advanced Encryption Standard New Instructions (Intel AES-NI) set on Intel Xeon processors for improving Oracle database encryption performance with Oracle Transparent Data Encryption (TDE).Oracle TDE provides security where data is automatically encrypted and decrypted when written to and read from the physical media. If we look in the Oracle TDE FAQ it clarifies what happens to the data once it has already been read and is held in memory. With TDE, column encryption data always remains encrypted in the Oracle SGA so the benefits of encryption acceleration are clear. However, with TDE tablespace encryption, data is already decrypted in the SGA so once it is cached it is in clear text. This makes the question more precise:If I am using TDE tablespace encryption for query-based data and have a large SGA to cache most of the data in decrypted form, what gains will AES-NI really bring me?The best way to answer this question is to put it to the test, so I tested a system equipped with Intel Xeon processor E5-2680 running Red Hat Enterprise Linux* Server release 5.6. I know these processors have AES-NI, but in the flags section of “/proc/cpuinfo” the flag “aes” confirms it for me. I installed Oracle 188.8.131.52 and applied the patch 10080579 to enable TDE to use AES-NI by default.To set up TDE I then created an encryption wallet directory in my admin directory as follows:/u01/app/oracle/admin/SANDEPDB1/walletI ensured that the permissions were set correctly to keep the directory secure. I then created a sqlnet.ora file in my network admin directory,/u01/app/oracle/product/11.2.0/dbhome_1/network/adminand added the following line to this file (keeping the entry all on the same line):ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/SANDEPDB1/wallet/)))Then I created (you can see the file created in your wallet directory) and opened the wallet as follows:[[email protected] ~]$ sqlplus / as sysdba…SQL> alter system set encryption key authenticated by “oraclepassword”;System altered.SQL>WALLET createdNext, I created an unencrypted tablespace as normal and an encrypted tablespace,SQL> create bigfile tablespace TPCH_ENCRYPT datafile ‘+DATA’ size 50g encryption using ‘AES256’ default storage(encrypt);Tablespace created.and checked the tablespace was indeed encrypted.SQL> select tablespace_name, encrypted from dba_tablespaces;TABLESPACE_NAME ENC—————————— —SYSTEM NOSYSAUX NOUNDOTBS1 NOTEMP NOUSERS NOTPCH_CLEAR NOTPCH_ENCRYPT YES7 rows selected.I then used Hammerora to create identical Scale Factor 10, 10GB schemas based on the TPC-H specification in both clear-text and encrypted forms, ensuring that with my 40GB buffer cache in the Oracle SGA there would be plenty of memory to cache the data. I also used Hammerora to run and capture an example query to use against this data and keep the predicates the same so the query run would be identical each time. I used autotrace and timing to test my query performance. First I took a look at the clear text schema with the following query (which is TPC-H Query 1).SQL> connect tpch/tpchConnected.SQL> set autotrace on;SQL> set timing on;SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 – l_discount)) as sum_disc_price, sum(l_extendedprice * (1 – l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '119' day (3) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;The query returned 4 rows and the timing value showed it took almost 29 seconds.Elapsed: 00:00:28.80Our Execution plan shows "TABLE ACCESS FULL" on "LINEITEM" which is a full table scan on our biggest table. Our statistics show that these were physical reads which means that the data was not cached in memory but instead read from disk.Statistics----------------------------------------------------------...1038269 physical reads...I then ran the same query again with the result showing the following timing.