Encryption is a classic security versus performance dual. Encrypting and decrypting within software can be particularly challenging because the crypto processes are very CPU intensive. Most implementations that require high volumes of encryption and decryption use specialized hardware to achieve the acceptable performance.
Within the database, you can look at encryption performance overall, but every situation will be different in the real world. My personal experiences have shown that the Oracle Database encryption performance characteristics make it a suitable and effective solution.
The following example represents the summarization of a few performance tests run with the DBMS_CRYPTO package. The tests try to isolate the actual encryption and decryption processes. Admittedly, the results were different and better than I predicted. Undoubtedly, your results will vary.
Two basic test types were run. The first was a loop test that encrypted (or decrypted) 20 bytes of data. The loop size was 1,000,000. This test measured how fast the database could encrypt or decrypt 20 bytes of data 1,000,000 times. To try and isolate the encryption process from the PL/ SQL looping time, I first timed how long a basic PL/SQL loop would take. The following code illustrates how I captured the clock time and CPU time for this process:
sec_mgr@KNOX10g> SET feedback off sec_mgr@KNOX10g> SET verify off sec_mgr@KNOX10g> SET timing off sec_mgr@KNOX10g> SET sqlprompt 'sql> ' sql> COL "CPU Start Value" new_val cpu_start sql> COL name format a25 sql> sql> -- Define a variable to hold size of for loop sql> VAR loop_size number; sql> -- Set loop size to one million sql> BEGIN 2 :loop_size := 1000000; 3 END; 4 / sql> /***** PL/SQL function in loop *******/ sql> @pl_loop.sql sql> --- Create sample PL/SQL function sql> CREATE OR REPLACE FUNCTION do_nothing 2 RETURN RAW 3 AS 4 BEGIN 5 RETURN NULL; 6 END; 7 / sql> sql> -- Test time to loop calling a PL/SQL function sql> @cpu_start sql> SELECT b.VALUE "CPU Start Value" 2 FROM v$statname a, v$mystat b 3 WHERE a.statistic# = b.statistic# 4 AND a.NAME = 'CPU used by this session'; CPU Start Value ------------------- 3 sql> DECLARE 2 l_data RAW (40); 3 l_start_time NUMBER; 4 l_end_time NUMBER; 5 BEGIN 6 l_start_time := DBMS_UTILITY.get_time; 7 FOR i IN 1 .. :loop_size 8 LOOP 9 l_data := do_nothing; 10 END LOOP; 11 l_end_time := DBMS_UTILITY.get_time; 12 DBMS_OUTPUT.put_line ( 'PL/SQL Loop Time: ' 13 || (l_end_time - l_start_time) / 100 14 || ' seconds'); 15 END; 16 / PL/SQL Loop Time: 1.52 seconds sql> @cpu_stop sql> SELECT b.VALUE "CPU End Value", 2 (b.VALUE - &cpu_start) / 100 3 "hsecs of CPU Units used" 4 FROM v$statname a, v$mystat b 5 WHERE a.statistic# = b.statistic# 6 AND a.NAME = 'CPU used by this session'; CPU End Value hsecs of CPU Units used ------------ -------------------------- 143 1.4 sql> /**************** eor ****************/
The PL/SQL loop required 1.52 seconds and consumed 1.4 units of CPU. The code illustrates the basic approach to how all the algorithms were tested. The first encryption test is for the AES encryption with the CBC block chaining and the PKCS5 padding (the Oracle recommended settings and the DATA_CRYPTO default). Note this code also shows how to invoke the DBMS_ CRYPTO package directly:
sql> /*********** AES encryption **********/ sql> @aes sql> @cpu_start CPU Start Value --143 sql> DECLARE 2 l_algo PLS_INTEGER 3 := dbms_crypto.encrypt_aes 4 + dbms_crypto.chain_cbc 5 + dbms_crypto.pad_pkcs5; 6 l_key RAW (16) 7 := UTL_RAW.cast_to_raw ('0123456789012345'); 8 l_data RAW (20) 9 := UTL_RAW.cast_to_raw ('01234567890123456789'); 10 l_enc_data RAW (40); 11 l_start_time NUMBER; 12 l_end_time NUMBER; 13 BEGIN 14 l_start_time := DBMS_UTILITY.get_time; 15 FOR i IN 1 .. :loop_size 16 LOOP 17 l_enc_data := 18 dbms_crypto.encrypt (l_data, l_algo, l_key); 19 END LOOP; 20 l_end_time := DBMS_UTILITY.get_time; 21 DBMS_OUTPUT.put_line ( 'AES Encryption Time: ' 22 || (l_end_time - l_start_time) / 100 23 || ' seconds'); 24 END; 25 / AES Encryption Time: 18 seconds sql> @cpu_stop CPU End Value hsecs of CPU Units used ------------- ------------------------ 1793 16.5 sql> /**************** eor ****************/
If you subtract the PL/SQL loop time, the database is encrypting 20 bytes of data 1,000,000 times in 16.5 seconds. I ran an AES decryption process, too. The following summarizes the results for the remaining algorithms, with block modifiers and padding:
AES Decryption Time: 18.68 seconds 17.21 hsecs of CPU Units used DES Encryption Time: 19.04 seconds 17.18 hsecs of CPU Units used Triple DES, 2 Key Encryption Time: 22.01 seconds 20.11 hsecs of CPU Units used Triple DES, 3 keys Encryption Time: 22.6 seconds 20.72 hsecs of CPU Units used AES 192-bit key Encryption Time: 18.33 seconds 16.56 hsecs of CPU Units used AES 256-bit key Encryption Time: 19.76 seconds 17.43 hsecs of CPU Units used RC4 Encryption Time: 23.02 seconds 21.11 hsecs of CPU Units used AES with CFB modifier Encryption Time: 18.01 seconds 16.44 hsecs of CPU Units used AES with CFB modifier Encryption Time: 17.47 seconds 15.96 hsecs of CPU Units used AES with OFB modifier Encryption Time: 17.63 seconds 16.13 hsecs of CPU Units used AES Encrypt: Pad Zeros Time: 17.89 seconds 16.4 hsecs of CPU Units used AES Encrypt: Pad ORCL Time: 18.13 seconds 16.49 hsecs of CPU Units used AES Encrypt: Pad None Time: 16.96 seconds 15.56 hsecs of CPU Units used
The SQL details and full unaltered results are located in Appendix C.