SQLLOADER is taking long to load the data. It is taking around 20mins to load 4209 records only-CodePudding

CREATE TABLE SQL_LOAD
(col1 varchar2(4000),
Col2  varchar2(4000),
Col3  varchar2(4000),
Col4  varchar2(4000),
Col5  varchar2(4000),
Col6  varchar2(4000),
Col7  varchar2(4000),
Col8  varchar2(4000),
Col9  varchar2(4000),
Col10  varchar2(4000),
Col11  varchar2(4000),
Col12  varchar2(4000),
Col13  varchar2(4000),
Col14  varchar2(4000),
Col15  varchar2(4000),
Col16  varchar2(4000),
Col17  varchar2(4000),
Col18  varchar2(4000),
Col19  varchar2(4000),
Col20  varchar2(4000),
Col21  varchar2(4000),
Col22  varchar2(4000),
Col23  varchar2(4000),
Col24  varchar2(4000));

Control file :

options (
    skip = 2,
    DIRECT = TRUE
    )
load data
infile 'I:\SQLLOADER\sqlloader.csv'
replace into table sql_load
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
Col1,
Col2,
Col3 ,
Col4 ,
Col5 ,
Col6 ,
Col7 ,
Col8 ,
Col9 ,
Col10 ,
Col11,
Col12,
Col13 char(4000) nullif Col13=BLANKS,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24
)

CSV file : Sample data https://drive.google.com/file/d/1wKchp3y1Uir2hxuXS29rX5GAQdHU6LUd/view?usp=sharing

Issue : Currently my csv file has 4209 records. When I am running the sqlldr command then it was loading only 3680 records and I checked the log and found the file exceed the limit error for the col13 in my table. So, I have added col13 char(4000) in my control file and then ran the same sqlldr command. Now it is taking around 20mins to load the data. Can someone let me know what went wrong

CodePudding user response：

I ran the same table/control with your sample data duplicated out to 15,000 rows and got this:

SQL> CREATE TABLE SQL_LOAD
  2  (col1 varchar2(4000),
  3  Col2  varchar2(4000),
  4  Col3  varchar2(4000),
  5  Col4  varchar2(4000),
  6  Col5  varchar2(4000),
  7  Col6  varchar2(4000),
  8  Col7  varchar2(4000),
  9  Col8  varchar2(4000),
 10  Col9  varchar2(4000),
 11  Col10  varchar2(4000),
 12  Col11  varchar2(4000),
 13  Col12  varchar2(4000),
 14  Col13  varchar2(4000),
 15  Col14  varchar2(4000),
 16  Col15  varchar2(4000),
 17  Col16  varchar2(4000),
 18  Col17  varchar2(4000),
 19  Col18  varchar2(4000),
 20  Col19  varchar2(4000),
 21  Col20  varchar2(4000),
 22  Col21  varchar2(4000),
 23  Col22  varchar2(4000),
 24  Col23  varchar2(4000),
 25  Col24  varchar2(4000));

Table created.

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Nov 18 08:07:48 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Control File:   x:\tmp\sqlloader.ctl
Data File:      x:\tmp\sqlloader.csv
  Bad File:     x:\tmp\sqlloader.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 2
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table SQL_LOAD, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                FIRST     *   ,  O(") CHARACTER            
COL2                                 NEXT     *   ,  O(") CHARACTER            
COL3                                 NEXT     *   ,  O(") CHARACTER            
COL4                                 NEXT     *   ,  O(") CHARACTER            
COL5                                 NEXT     *   ,  O(") CHARACTER            
COL6                                 NEXT     *   ,  O(") CHARACTER            
COL7                                 NEXT     *   ,  O(") CHARACTER            
COL8                                 NEXT     *   ,  O(") CHARACTER            
COL9                                 NEXT     *   ,  O(") CHARACTER            
COL10                                NEXT     *   ,  O(") CHARACTER            
COL11                                NEXT     *   ,  O(") CHARACTER            
COL12                                NEXT     *   ,  O(") CHARACTER            
COL13                                NEXT  4000   ,  O(") CHARACTER            
    NULL if COL13 = BLANKS
COL14                                NEXT     *   ,  O(") CHARACTER            
COL15                                NEXT     *   ,  O(") CHARACTER            
COL16                                NEXT     *   ,  O(") CHARACTER            
COL17                                NEXT     *   ,  O(") CHARACTER            
COL18                                NEXT     *   ,  O(") CHARACTER            
COL19                                NEXT     *   ,  O(") CHARACTER            
COL20                                NEXT     *   ,  O(") CHARACTER            
COL21                                NEXT     *   ,  O(") CHARACTER            
COL22                                NEXT     *   ,  O(") CHARACTER            
COL23                                NEXT     *   ,  O(") CHARACTER            
COL24                                NEXT     *   ,  O(") CHARACTER            

Record 1963: Discarded - all columns null.

Table SQL_LOAD:
  15682 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  1 Row not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          2
Total logical records read:         15683
Total logical records rejected:         0
Total logical records discarded:        1
Total stream buffers loaded by SQL*Loader main thread:       11
Total stream buffers loaded by SQL*Loader load thread:       10

Run began on Thu Nov 18 08:07:48 2021
Run ended on Thu Nov 18 08:07:48 2021

Elapsed time was:     00:00:00.20
CPU time was:         00:00:00.11

so this would suggest that there is nothing untoward with the data or your control file. Places to look:

constraints
triggers
indexes
network
is the data truly representative? Once you start getting into rows longer than the block size you can get some performance penalties, but still should not be in the 20min range