Home » RDBMS Server » Server Utilities » clob infile taking long time to load few records (solaris)
clob infile taking long time to load few records [message #340089] Mon, 11 August 2008 07:30
manoj339
Messages: 9
Registered: April 2007
Junior Member
Hi,
The control file and log file is mentioned below.It is taking 20 min to load 900 kb file containing infile clob data.
The length of clob can be variable length.
I have explicitly mentioned as 2000000000 .However clob data as per 9i can be upto 4GB.How can I change ctl file or sqlloader parameters to improve performance of this load.


sqlldr conn_details control="psd_term_20080811.ctl" data="psd_term_20080811_L.dat" log="psd_term_20080811_load_L.log" bad="psd_term_20080811_L.bad" discard="psd_term_20080811_L.dsc" rows=500 errors=5000000

control file of sqlloader :
LOAD DATA
INFILE '' "str X'3C213E0A'"
APPEND INTO TABLE PSD_TERM
FIELDS TERMINATED BY '~^'
TRAILING NULLCOLS
(
"PSD_ID" CHAR(16) NULLIF ("PSD_ID"=BLANKS)
,"PSD_SERIAL_NUM" CHAR(4) NULLIF ("PSD_SERIAL_NUM"=BLANKS)
,"PSD_TERM_COD" CHAR(4) NULLIF ("PSD_TERM_COD"=BLANKS)
,"PSD_TERM_SER_NO" CHAR(4) NULLIF ("PSD_TERM_SER_NO"=BLANKS)
,"VERSION_DT" DATE "DD-MON-YYYY HH:MI:SS AM" NULLIF ("VERSION_DT"=BLANKS)
,"LATEST_VERSION" CHAR(1) NULLIF ("LATEST_VERSION"=BLANKS)
,"NARRATIVE" CHAR(2000000000)
,"PARTITION_DT" DATE "DD-MON-YYYY HH:MI:SS AM" NULLIF ("PARTITION_DT"=BLANKS)
,"NARRATIVE_UNEXPANDED" CHAR(2000000000)
)


log file o/p of sqlloader

SQL*Loader: Release 9.2.0.8.0 - Production on Fri Aug 8 08:47:12 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808.ctl
Data File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808_L.dat
File processing option string: "str X'3C213E0A'"
Bad File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808_L.bad
Discard File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808_L.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 5000000
Bind array: 500 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK
Table "IWH_OWNER"."PSD_TERM", loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
"PSD_ID" FIRST 16 CHARACTER
Terminator string : '~^'
NULL if "PSD_ID" = BLANKS
"PSD_SERIAL_NUM" NEXT 4 CHARACTER
Terminator string : '~^'
NULL if "PSD_SERIAL_NUM" = BLANKS
"PSD_TERM_COD" NEXT 4 CHARACTER
Terminator string : '~^'
NULL if "PSD_TERM_COD" = BLANKS
"PSD_TERM_SER_NO" NEXT 4 CHARACTER
Terminator string : '~^'
NULL if "PSD_TERM_SER_NO" = BLANKS
"VERSION_DT" NEXT * DATE DD-MON-YYYY HH:MI:SS AM
Terminator string : '~^'
NULL if "VERSION_DT" = BLANKS
"LATEST_VERSION" NEXT 1 CHARACTER
Terminator string : '~^'
NULL if "LATEST_VERSION" = BLANKS
"NARRATIVE" NEXT ***** CHARACTER
Maximum field length is 2000000000
Terminator string : '~^'
"PARTITION_DT" NEXT * DATE DD-MON-YYYY HH:MI:SS AM
Terminator string : '~^'
NULL if "PARTITION_DT" = BLANKS
"NARRATIVE_UNEXPANDED" NEXT ***** CHARACTER
Maximum field length is 2000000000
Terminator string : '~^'


Table "PSD_TERM":
177 Rows successfully loaded.
0 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.


Space allocated for bind array: 4000000564 bytes(1 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 178
Total logical records rejected: 0
Total logical records discarded: 0

Previous Topic: SQL Loader how to load header line as field
Next Topic: how to transfer data
Goto Forum:
  


Current Time: Sun Dec 11 02:40:48 CST 2016

Total time taken to generate the page: 0.27315 seconds