1 million can I load data into sqlldr faster?

Setp-1 to generate data files:

GetObject. SQL

SELECT a.owner||',"'||a.object_name||'",'||a.object_id||','||to_char(a.created,'yyyy-mm-dd hh24:mi:ss')||','||a.statusFROM dba_objects a, (SELECT rownum rn FROM dual connect BY rownum<=23) b;

Call. SQL

SET echo offSET term offSET line 100 pages 0SET feedback offSET heading offspool C:\oracle\script\ldr_object.csv@C:\oracle\script\getobject.sqlspool offSET heading onSET feedback onSET term on SET echo on 

Step 0 initialize the environment:

Createobject. SQL

CREATE TABLE objects (owner varchar(30),object_name varchar(50),object_id NUMBER,created date,status VARCHAR2(10));CREATE INDEX  idx_obj_owner_name on objects(owner,object_name);

Step 1: import for the first time

Ldr_object.ctl

load datainfile ldr_object.csvtruncate into table objectsfields terminated by "," optionally enclosed by '"'(owner,object_name,object_id,created date 'yyyy-mm-dd hh24:mi:ss',status "substr(:status,1,5)")

C: \ oracle \ script> sqlldr Scott/tiger control = ldr_object.ctl errors = 10

Ldr_object.log

SQL * Loader: Release 10.2.0.1.0-production on Friday January 20 02:16:56 2012 copyright (c) 1982,200 5, Oracle. all rights reserved. control File: ldr_object.ctl data file: ldr_object.csv error file: ldr_object.bad obsolete file: unspecified (all records can be discarded) number of files to be loaded: All number to be skipped: 0 allowed error: 10 bind an array: 64 rows, up to 256000 bytes continue: unspecified path: regular table objects, the inserted option from each logical record has been loaded. This table truncate takes effect. Column name Position length abort packaging data type ----------------------------------------------------------------- --------- Owner first *, O (") character object_name next *, O (") character object_id next *, O (") character created next *, O (") date yyyy-mm-dd hh24: MI: ssstatus next *, SQL string in the O (") character column:" substr (: Status,) "table objects: 1033298 rows are loaded successfully. Zero rows are not loaded due to data errors. Because all the when clauses fail, the 0 rows are not loaded. Because all fields are empty, 0 rows are not loaded. Space allocated to the bound array: 82560 bytes (64 rows) read buffer Bytes: 1048576 skipped logical records Total: 0 read logical records Total: 1033298 reject logical records total: 0 total number of discarded logical records: 0 from Friday January 20 02:16:56 2012 start running on Friday January 20 02:19:02 end running time: 00: 02: 05.55cpu time: 00: 00: 2012

Step 2: Perform the second import

C: \ oracle \ script> sqlldr Scott/tiger control = ldr_object.ctl errors = 10 rows = 640

. Log

SQL * Loader: Release 10.2.0.1.0-production on Friday January 20 02:56:28 2012 copyright (c) 1982,200 5, Oracle. all rights reserved. control File: ldr_object.ctl data file: ldr_object.csv error file: ldr_object.bad obsolete file: unspecified (all records can be discarded) number of files to be loaded: All number to be skipped: 0 allowed error: 10 bind an array: 640 rows, up to 256000 bytes continue: unspecified path: regular table objects, the inserted option from each logical record has been loaded. This table truncate takes effect. Column name Position length abort packaging data type ---------------------------------------------------------------- ---------- Owner first *, O (") character object_name next *, O (") character object_id next *, O (") character created next *, O (") date yyyy-mm-dd hh24: MI: ssstatus next *, SQL string in the O (") character column:" substr (: Status) "the value used by the rows parameter has been changed from 640 to 198 table objects: Row 1033298 is successfully loaded. Zero rows are not loaded due to data errors. Because all the when clauses fail, the 0 rows are not loaded. Because all fields are empty, 0 rows are not loaded. Space allocated to the bound array: 255420 bytes (198 rows) read buffer Bytes: 1048576 skipped logical records Total: 0 read logical records Total: 1033298 reject logical records total: 0 total number of discarded logical records: 0 from Friday January 20 02:56:28 2012 start running on Friday January 20 02:57:48 end running time: 00: 01: 91cpu time: 00: 00: 2012

Well, it's 45 s faster.

Setp3 executes import for the third time

Use Direct Parameters

C: \ oracle \ script> sqlldr Scott/tiger control = ldr_object.ctl errors = 10 direct = true

Log

SQL * Loader: Release 10.2.0.1.0-production on Friday January 20 03:44:39 2012 copyright (c) 1982,200 5, Oracle. all rights reserved. control File: ldr_object.ctl data file: ldr_object.csv error file: ldr_object.bad obsolete file: unspecified (all records can be discarded) number of files to be loaded: All number to be skipped: 0 allowed error: 10 continue: path not specified: Direct table objects, the inserted option from each logical record has been loaded. This table truncate takes effect. Column name Position length abort packaging data type -------------------------- ---------- ----- ---- ------------------- owner first *, O (") character object_name next *, O (") character object_id next *, O (") character created next *, O (") date yyyy-mm-dd hh24: mi: ssstatus next *, the SQL string in the O (") character column:" substr (: Status,) "The following indexes in the objects table are processed: The index Scott. idx_obj_owner_name is successfully loaded. The table objects with 1033298 keywords: Row 1033298 is successfully loaded. Zero rows are not loaded due to data errors. Because all the when clauses fail, the 0 rows are not loaded. Because all fields are empty, 0 rows are not loaded. Date cache: maximum size: 1000 items: 892 hits: 1032406 missed items: 0 the size of the bound array is not used in the direct path. Number of column array rows: 5000 Number of buffer Bytes: 256000 Number of read buffer Bytes: 1048576 total number of skipped logical records: 0 total number of read logical records: 1033298 total number of rejected logical records: 0 total number of discarded logic records: 0 total number of stream buffers loaded by SQL * loader main thread: 302 total number of stream buffers loaded by SQL * loader loading thread: 201 run from Friday January 20 03:44:39 2012 run on Friday January 20 03:45:11 2012 run End Time: 00: 00: 32.17cpu time: 00: 00: 06.19

Well, it's just half a minute and a minute.

Setp 4: Execute the fourth Import

Increase the stream storage area and increase the date format buffer.

C: \ oracle \ script> sqlldr Scott/tiger control = ldr_object.ctl errors = 10 direct = true streamsize = 10485760 date_cache = 5000

Log

SQL * Loader: Release 10.2.0.1.0-production on Friday January 20 03:48:27 2012 copyright (c) 1982,200 5, Oracle. all rights reserved. control File: ldr_object.ctl data file: ldr_object.csv error file: ldr_object.bad obsolete file: unspecified (all records can be discarded) number of files to be loaded: All number to be skipped: 0 allowed error: 10 continue: path not specified: Direct table objects, the inserted option from each logical record has been loaded. This table truncate takes effect. Column name Position length abort packaging data type -------------------------- ---------- ----- ---- ------------------- owner first *, O (") character object_name next *, O (") character object_id next *, O (") character created next *, O (") date yyyy-mm-dd hh24: mi: ssstatus next *, the SQL string in the O (") character column:" substr (: Status,) "The following indexes in the objects table are processed: The index Scott. idx_obj_owner_name is successfully loaded. The table objects with 1033298 keywords: Row 1033298 is successfully loaded. Zero rows are not loaded due to data errors. Because all the when clauses fail, the 0 rows are not loaded. Because all fields are empty, 0 rows are not loaded. Date cache: maximum size: 5000 items: 892 hits: 1032406 missed items: 0 the size of the bound array is not used in the direct path. Number of column array rows: 5000 Number of buffer Bytes: 10485760 Number of read buffer Bytes: 1048576 total number of skipped logical records: 0 total number of read logical records: 1033298 total number of rejected logical records: 0 total number of discarded logic records: 0 total number of stream buffers loaded by SQL * loader main thread: 302 total number of stream buffers loaded by SQL * loader loading thread: 0 run from Friday January 20 03:48:27 2012 run on Friday January 20 03:48:53 2012 run End Time: 00: 00: 25.39cpu time: 00: 00: 06.38

Successful 30 s

I believe that as I continue to learn and become familiar with various parameters, I can hurry up ~

Refer to apply to Oracle