Performance SQL Loader (sqlldr) Oracle9.2

Hi,
can anybody out there give me a rough number how much records per second I can expect to load with SQL Loader (sqlldr) :-?

Happy helloween, Wolfgang

Some information about our environment(s):

1. HW: a) Sun 280R, 2 CPUs, 1MB memory b) Sun 480R, 2 CPUs, 4MB memory
2. OS: SunOS 5.8
3. Oracle 9.2 a) Redo logs, UNDO, Data/Index on different disks of disk array, mirrored. b) Data and Index in same tablespace. c) 2 redo log files on 2 raw devices of same disk, no archiving.
4. Table test_table:

This is the target/working table.

Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 TRANS_ID                                           NUMBER(38)
 RECORD_ID                                          NUMBER(38)
 NAME                                               CHAR(6)
 RECORD_STATE                                       NUMBER(38)
 C_ID                                               VARCHAR2(12)
 A_ADDRESS                                          VARCHAR2(64)
 B_ADDRESS                                          VARCHAR2(64)
 SN                                                 NUMBER(38)
 CAUSE                                              NUMBER(38)
 TIME                                               DATE
 CHANGE                                             NUMBER(38)
 RECORD                                             VARCHAR2(1024)
 TRANS_ID_OUT                                       NUMBER(38)

create unique index test_table_a on test_table(trans_id, record_id); create index test_table_b on test_table(c_id, B_Address, A_address, name);
create index test_table_c on test_table( trans_id_out, record_id);

5) Size of records in RECORD around 350 bytes.

6) MY RESULTS:
With conventional path loading, nothing else going on, for 1Mio records, I got between 400-500 records/sec on 1a). Can I expect more?

7) Would you recommend loading into an intermediate staging table with DIRECT PATH, and then move from staging table to target table? Drop/rebuild indexes?

8) What are the drawbacks of using DIRECT PATH?

9) What are the most critical parts of Oracle set-up, i/o, parameterization I should look at? Received on Thu Oct 31 2002 - 07:23:17 CST