Re: Performance SQL Loader (sqlldr) Oracle9.2

sqlldr is not more necessary with 9.2

use external tables

stagging is also not more necessary , because you can build your sql with external tables

direct path is for loading always preferable

you need KAIO on solaris for good performance ( raw devices, veritas qio ... )

9.2 is not comparable with "old oracle"

you need somebody who know how it works

"wolfgang blume" <wolfgang.blume_at_gmx.de> schrieb im Newsbeitrag news:19846023.0210310523.5b89a0e4_at_posting.google.com...
> 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 - 08:22:21 CST