Impact of Encryption in Objective-C

Maker Data Matrix in Objective-C Impact of Encryption

Let s see how we can measure the impact. We ll use the following tables in this example: the STAGE table is used so that we measure the impact of encryption, not the impact of running a relatively complex query against the view ALL_OBJECTS, and table T is used to measure the impact of column level encryption:

ops$tkyte%ORA11GR2> create table stage 2 as 3 select object_name 4 from all_objects; Table created. ops$tkyte%ORA11GR2> create table t 2 ( non_encrypted varchar2(30), 3 encrypted varchar2(30) ENCRYPT 4 ); Table created. Our goal is to measure redo generation and CPU utilization of various SQL operations against these columns. To that end, we ll use a small stored procedure that can capture that utilization for us. Our procedure will use dynamic SQL (so we ll be using AUTHID CURRENT_USER to avoid SQL Injection security issues) and measure the amount of redo generated and CPU used. It starts by truncating the table we are using, if necessary. Then our procedure sets about getting the starting redo/cpu metrics, performing an operation, committing (without the PL/SQL optimization we discussed in 11 Indexes ), and printing out the totals for each of redo and CPU: ops$tkyte%ORA11GR2> create or replace 2 procedure do_sql( p_sql in varchar2, 3 p_truncate in boolean default true ) 4 authid current_user 5 as 6 l_start_cpu number; 7 l_start_redo number; 8 l_total_redo number; 9 begin 10 if (p_truncate) 11 then 12 execute immediate 'truncate table t'; 13 end if; 14 15 dbms_output.put_line( p_sql ); 16 17 l_start_cpu := dbms_utility.get_cpu_time; 18 l_start_redo := get_stat_val( 'redo size' ); 19 20 execute immediate p_sql; 21 commit work write batch wait; 22 23 dbms_output.put_line 24 ( (dbms_utility.get_cpu_time-l_start_cpu) || ' cpu hsecs' ); 25 26 l_total_redo := 27 round((get_stat_val('redo size')-l_start_redo)/1024/1024,1); 28 dbms_output.put_line 29 ( to_char(l_total_redo,'999,999,999.9') || ' mbytes redo' ); 30 end; 31 / Procedure created.

Now we can start by measuring the difference between a bulk insert into the NON_ENCRYPTED column versus the ENCRYPTED column: ops$tkyte%ORA11GR2> begin 2 do_sql( 'insert into t(non_encrypted) ' || 3 'select object_name from stage' ); 4 do_sql( 'insert into t(encrypted) ' || 5 'select object_name from stage' ); 6 end; 7 / insert into t(non_encrypted) select object_name from stage 13 cpu hsecs 2.3 mbytes redo insert into t(encrypted) select object_name from stage 121 cpu hsecs 5.3 mbytes redo PL/SQL procedure successfully completed. So, column encryption had a definite impact here: almost 10 times as much CPU was used (on my hardware; your mileage may vary!) and more than twice as much redo was generated. While 10 times as much CPU sounds like a lot, we might want to compare that to the do it yourself approach where the CPU utilization was closer to 50 times higher. Now, let s measure the impact of slow by slow processing also known as row by row processing. This time we ll execute a dynamic PL/SQL block that will load the records from the STAGE table into T a row at a time, once referencing the non-encrypted column and then referencing the encrypted column: ops$tkyte%ORA11GR2> declare 2 l_sql long := 3 'begin ' || 4 'for x in (select object_name from stage) ' || 5 'loop ' || 6 'insert into t(#CNAME#) ' || 7 'values (x.object_name); ' || 8 'end loop; ' || 9 'end; '; 10 begin 11 do_sql( replace(l_sql,'#CNAME#','non_encrypted') ); 12 do_sql( replace(l_sql,'#CNAME#','encrypted') ); 13 end; 14 / begin for x in (select object_name from stage) loop insert into t(non_encrypted) values (x.object_name); end loop; end; 411 cpu hsecs 16.5 mbytes redo begin for x in (select object_name from stage) loop insert into t(encrypted) values (x.object_name); end loop; end; 1039 cpu hsecs 19.4 mbytes redo This time we needed about 2.5 times as much CPU and just a little more redo. This 2.5 times difference should be compared to the do it yourself encryption we saw previously; there we needed almost six times as much CPU. So again, transparent column level encryption is much more efficient than the do it yourself approach. This example points out the overhead of row by row operations in