sqlldr very very slow

From: freCho <svenson1_at_libero.it>
Date: Wed, 26 Jan 2005 17:52:16 +0100
Message-ID: <pan.2005.01.26.16.52.11.370628_at_libero.it>

Hi all,
I'm having problem using sqlldr.
[Quoted] [Quoted] I have to load about 6.000.000 records into a large table
[Quoted] (about 300.000.000 records). This mornig I started a script which
connect to db and launch sqlldr....but I'm waiting for the FIRST "Save data point reached" !!
The db is an Oracle9i Enterprise Edition Release 9.2.0.1.0, the server is a Sun Enterprise 420R whit 4 CPU and 4GB RAM Solaris 8.
The control file used by sqlldr utility is this:

options (direct=true, rows=100000)
unrecoverable load data
infile 'first.csv'
append
into table TABLENAME
fields terminated by ','
trailing
(

column names here
)

The db is out of service, so only background process and the sqlldr are connected to it:

SQL> select username, sid,MACHINE,PROCESS from v$session;  

USERNAME                              SID MACHINE                        PROCESS
------------------------------ ---------- ------------------------------ ------------
                                        1 dbadv                          3813
                                        2 dbadv                          3815
                                        3 dbadv                          3817
                                        4 dbadv                          3819
                                        5 dbadv                          3821
                                        6 dbadv                          3825
                                        7 dbadv                          3827
                                        8 dbadv                          3829
ENG	                              191 dbadv                          22554
SYS                                   259 dbadv                          16507

After 3 hours waiting I started a little test on another server running an 8i.
I created the same table, and using the same input file and control file
(changing only the connect string of course), all records are been loaded
in about 6 mins !!!

There are differences between the two schema used in the different oralce server, I have not created all tables related with the original one, so no time have been spent in the test db to check constraints, but I don't think that this can be the root cause of the slowness in the 9i environment.

[Quoted] What can I check to see what is happening?? If same other information is required please ask me. Any idea is very appreciated.

ps- sorry for my poor english Received on Wed Jan 26 2005 - 17:52:16 CET