Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Loader choosing conventional path when direct path is requested

mtefftJan 7 2013 — edited Jan 8 2013

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