Sql*loader Table Load With Tde Encryption Takes a Very Long Time (Doc ID 790476.1)

Last updated on MARCH 22, 2009

Applies to:

Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
This problem can occur on any platform.

Symptoms

When using sql*loader to upload a text file into a TDE encrypted table, it takes a hours more than when uploading the same table without TDE.
The same problem can occur when using other methods of direct load to populate a TDE encrypted table.

To investigate the problem, the table should be loaded with and without encryption and a sql trace file generated for both situations.

Tracing can be easily set up for sql loader with a trigger similar to:

CREATE OR REPLACE TRIGGER set_trace_on_sqlldr
AFTER LOGON
ON DATABASE
DECLARE
  v_program v$session.program%TYPE;
  v_dyn_sql VARCHAR2(100);
BEGIN
  SELECT substr(program,1,6) INTO v_program FROM v$session WHERE sid=(SELECT DISTINCT sid FROM v$mystat);
  IF v_program = 'sqlldr' THEN
    v_dyn_sql:='ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED';
    EXECUTE IMMEDIATE v_dyn_sql;
    v_dyn_sql:='ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 8''';
   EXECUTE IMMEDIATE v_dyn_sql; 
 END IF;
END set_trace_on_sqlldr;

(for windows, the program name is sqlldr.exe).

Format the resulting trace files using tkprof.

The INSERT /*+ SYS_DL_CURSOR */ INTO <table_name> command takes a comparable time in both situations, but the time to execute the whole operation, as seen in the last line in the trace file, is much larger in the encrypted scenario:

5345 elapsed seconds in trace file.

while not encrypted:

153 elapsed seconds in trace file.

If the situation is comparable with the above, then most likely this bug is hit.
 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms