My Oracle Support Banner

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

Last updated on MARCH 13, 2019

Applies to:

Advanced Networking Option - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.
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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.