Sql*Loader direct slower than nodirect problem...

From: Giorgio Sorbara <mail_at_fake.org>
Date: Mon, 18 Oct 2004 18:13:51 +0200
Message-ID: <cl0cqm$2rcf$1@newsreader1.mclink.it>

Hi folks,

got a few problems on sql*loader. The scenario I am in is the following: data need to be loaded into tables and encrypted... for no reasons data should be loaded in clear (this prevents me to load data into a staging area and encrypt). So far so good... I managed to load data from an encrypted tar through a couple of pipes and finally to sql*loader which actually gets data clear through the pipe but it encrypts "on the fly".

This is a piece of the ctl file:

...

   field_clear    char,
   field_encoded  char "crypkg.encString(:field_encoded,<HereIsTheKey>)",
   other_fields   char,

...

Performance for 10M records and a nodirect load is around 8 minutes. Performance for 100K records for a direct load is a mess... here follows a log:

---

Record 65504: Rejected - Error on table BIG_TABLE_ENC. ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Record 65505: Rejected - Error on table BIG_TABLE_ENC. ORA-00603: ORACLE server session terminated by fatal error

SQL*Loader-926: OCI error while uldlfca:OCIDirPathColArrayLoadStream for table BIG_TABLE_ENC
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. Specify SKIP=65000 when continuing the load. SQL*Loader-925: Error while uldlgs: OCIStmtExecute (ptc_hp) ORA-03114: not connected to ORACLE

SQL*Loader-925: Error while uldlgs: OCIStmtFetch (ptc_hp) ORA-24338: statement handle not executed

Table BIG_TABLE_ENC:

   65000 Rows successfully loaded.
   11 Rows not loaded due to data errors.    0 Rows not loaded because all WHEN clauses were failed.    0 Rows not loaded because all fields were null.  

   Date cache:

    Max Size:      1000
    Entries :         6
    Hits    :    130988
    Misses  :         0
 

Bind array size not used in direct path.
Column array  rows :      71

Stream buffer bytes: 256000
Read buffer bytes: 1048576  

Total logical records skipped:          0
Total logical records read:         65568
Total logical records rejected:        11
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      995
Total stream buffers loaded by SQL*Loader load thread:        0
 

Run began on Mon Oct 18 12:43:03 2004
Run ended on Mon Oct 18 12:59:20 2004

---

Now I was wondering if is it really faster to go for a direct path load even if there is a function involved... does Oracle need to go through Sql for evaluting the function? even if this is the case i would expect at least the same performance I get with the no direct...

My package is buggy... yes it might be

Sql*loader is buggy... it might be as well, considering also the fact that sql*loader starts to discard records after 65K (!!??) it stinks..

Utl_raw is buggy... evidence might be the following info I found in a core file:

---

begin

          dbms_obfuscation_toolkit.des3encrypt(input_string
=>:1,key_string =>:2,

encrypted_string=>:3,which=>dbms_obfuscation_toolkit.ThreeKeyMode);

        end;
----- PL/SQL Call Stack -----

   object line object
   handle number name

3b9b2bfc0       178  package body SYS.UTL_RAW
3b9b2ff10       201  package body SYS.DBMS_OBFUSCATION_TOOLKIT
3b9b861a0         2  anonymous block
3b9b37210        32  package body SCOTT.CRYPKG
3baddef28         1  anonymous block

----- Call Stack Trace -----

---

Anything so evidently wrong I can't see?

/Giorgio

PS: my package is just a wrapper for the DBMS_OBF_TOOLKIT Received on Mon Oct 18 2004 - 11:13:51 CDT