2 Replies Latest reply on Jul 2, 2020 12:29 PM by DelaCiv

    SQL loader direct Path load ignored - Always conventional load

    DelaCiv

      Hello,

       

      Database: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit

      Sql Loader: SQL*Loader: Release 12.2.0.1.0

       

      I'm trying to use direct path to load a dataset of few millions records. The data is loaded successfully but It appears that direct path is being ignored and sql loader always use conventional path as shown by the explain plan.

      What could be the issue? Any article, hint would be appreciated. Thanks

       

      Some fact:

           1 - DBA confirmed that was catldr.sql executed and system is set for direct path.

           2 - Table has no constraints, no Pk no FK keys

           3 - Table is compressed basic

       

       

      Table description

      CREATE TABLE DIRECT_PATH_LOAD

      (

        COL1 NUMBER

      , COL2 NUMBER

      , COL3 VARCHAR2(20 BYTE) NOT NULL

      , COL4 VARCHAR2(20 BYTE) NOT NULL

      , COL5 NUMBER(*, 0) NOT NULL

      , COL6 DATE NOT NULL

      )  LOGGING

      COMPRESS BASIC;

       

      sql loader ct.l file

       

      OPTIONS (SILENT=(HEADER, FEEDBACK, DISCARDS), PARALLEL=FALSE, DIRECT=TRUE, STREAMSIZE=2097152, COLUMNARRAYROWS=50000)

      LOAD DATA

      CHARACTERSET UTF8

      APPEND

      INTO TABLE DIRECT_PATH_LOAD

      FIELDS TERMINATED BY '|'

      TRAILING NULLCOLS (

      COL1,

      COL2,

      COL3,

      COL4,

      COL5,

      COL6

      )

       

      Sample .dat file

      405054|1167505|CFDA571|FF031|5002|31-DEC-19

      405054|1167506|CFDA571|FF031|5002|31-DEC-19

      405054|1167507|CFDA571|FF031|5002|31-DEC-19

      405054|1167508|CFDA571|FF031|5002|31-DEC-19

      405054|1167509|CFDA571|FF031|5002|31-DEC-19

      405054|1167510|CFDA571|FF031|5002|31-DEC-19

      405054|550619|RFD1410|FFI031|5002|31-DEC-19

      405054|550620|RFD1410|FF031|5002|31-DEC-19

      405054|550621|RFD1410|FF031|5002|31-DEC-19

       

       

      Explain Plan

      Operation Options Object Name CPU Cost IO Cost

      __INSERT STATEMENT

      __INSERT STATEMENT

      ____LOAD TABLE CONVENTIONAL DIRECT_PATH_LOAD

      ____LOAD TABLE CONVENTIONAL DIRECT_PATH_LOAD

       

      Thanks