7 Replies Latest reply: Jan 8, 2013 3:46 PM by Srini Chavali-Oracle RSS

    SQL Loader choosing conventional path when direct path is requested

    mtefft
      We have a mystery regarding SQL Loader choosing to load with conventional path even though direct path is requested.

      We have a control file that produces direct-path loads and one which does not. The difference between them does not seem to account for the difference in behavior.

      The following control file does not give us direct-path:
      OPTIONS (
           SKIP=0,
           ERRORS=0,          
           DIRECT=TRUE,          
           NOLOGGING
           )
      LOAD DATA
      INFILE "[file path]" "STR x'0A'"
      BADFILE "[file path].bad"
      DISCARDFILE "[file path].dsc"
      DISCARDMAX 0
      INSERT
      INTO [schema name].[table name]
      FIELDS TERMINATED BY X'2C'
      OPTIONALLY ENCLOSED BY '?'
      TRAILING NULLCOLS
      (
           
           C1_ACD_LINE_CD     CHAR(2000),
      [column specifications continue]
      )
      When running with this control file, the log shows:
      Number to load: ALL
      Number to skip: 0
      Errors allowed: 0
      Bind array:     64 rows, maximum of 256000 bytes
      Continuation:    none specified
      Path used:      Conventional
      
      Table [schema name].[table name], loaded from every logical record.
      Insert option in effect for this table: INSERT
      TRAILING NULLCOLS option in effect
      If we use a control file that is modified as follows:
      OPTIONS (
           SKIP=0,
           ERRORS=0,     
           DIRECT=TRUE,     
           PARALLEL=TRUE,
           NOLOGGING
           )
      Then we do get direct-path load:
      Number to load: ALL
      Number to skip: 0
      Errors allowed: 0
      Continuation:    none specified
      Path used:      Direct
      
      Table [schema name].[table name], loaded from every logical record.
      Insert option in effect for this table: INSERT
      TRAILING NULLCOLS option in effect
      So there is nothing about the table (constraints, triggers, etc.) that is preventing direct-path loads.

      Now, we stumbled into this PARALLEL thing by accident - we are not really trying to do parallel loads.
      In my reading of the Utilities guide (http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_modes.htm#autoId64 ), the PARALLEL option lets SQL Loader tolerate multiple sessions loading to the same segment at once, but does not perform parallel processing itself. So, is it possible there is some other lock on the table is causing SQL Loader to block direct-path loads to the table (because of a previous SQL Loader direct-path load, perhaps) unless the PARALLEL option is invoked? If so, how do we recognize that state and how do we resolve it?

      Version information:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for Solaris: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      Any thoughts or suggestions would be appreciated.

      Thanks,
      Mike