Loading Data through sql loader very very slow.... Please Help!!!

[ronnie]

Hi,
I am trying to load around 3 million records in a table using sql loader (Oracle 8.1.7). It is a simple load of a comma delimited file.

I was able to load the same data before with the speed atleast 100 times faster

I dont know what happenned and what do do to fix this thing.

I have to deliver it today, so if please if someone can help me out with it.

Infact I even tried direct=y in the parfile but the load does not progress at all, it just hangs.

Thanks
Ronnie

Here is the control file
--------------------------
load data
infile 'loadhfod.dat'
badfile 'hfod.bad'
discardfile 'hfod.dcs'
truncate
into table ofas
fields terminated by ","
(company,
time,
region,
lineitem,
actual
nullif (actual = BLANKS),
actualus
nullif (actualus = BLANKS),
year_quarter)


Parameter File
----------------
userid=hfod/hfod@louise_company
control=loaddata.ctl
errors=25000
log=loaddata.log

Sample Data
--------------
MBOC,1996,REGROLL,COFYE, 12.000000, 12.000000,YR
MBOC,1996,REGROLL,COLOP, 12.000000, 12.000000,YR
MBOC,1996,REGROLL,COEPAM, 1.000000, 1.000000,YR
MBOC,1996,REGROLL,COCAI, 1.000000, 1.000000,YR
MBOC,1996,REGROLL,COAAER, 1.000000, 1.000000,YR
MBOC,1996,REGROLL,COYEER, 1.000000, 1.000000,YR

Table Structure
----------------
SQL> desc ofas
Name Null? Type
----------------------------------------- -------- -----------------
COMPANY VARCHAR2(10)
TIME VARCHAR2(10)
REGION VARCHAR2(10)
LINEITEM VARCHAR2(15)
ACTUAL NUMBER(20,6)
ACTUALUS NUMBER(20,6)
YEAR_QUARTER VARCHAR2(5)

important init.ora parameters
---------------------------------
db_block_buffers = 13124
shared_pool_size = 35837337
large_pool_size = 614400
log_buffer = 32768
db_block_size = 8192
compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536

The database is in no archive mode.

PLEASE HELP

[s_radhak]

Hi,

I am not sure about the syntax of the control file...

You are using TRUNCATE INTO [table_name]

Should it be INSERT or APPEND or REPLACE

Please check the syntax

Radha.

[ronnie]

s_radhak,

Its truncating the data in the table before loading the recordsAny suggesstions why it is sooooooooo sloooooooooooow

Ronnie

[s_radhak]

Ronnie,

It might be because of the table's referential integrity constraints.

Radha.

[ronnie]

There were no indexes or constraints on the table.

Anyways I dropped and recreated the table and now its fine.

I have no clue why. Any Ideas?????

Ronnie

[antonv]

how many extents does the table has? b4 u dropped it?

[sambavan]

Do you have any triggers against the table? which you have forgot to disable?


Sam