I am facing a problem related to batch inserts in an Oracle8i database. The stored procedure is inserting hundred thousand rows in 5 tables.
I have created the tables and indexes with appropriate storage parameters.
I have done the following tuning
PCTFREE 10
PCTUSED 90 (Tables only - initially i tried it with 85 since it is a DSS application)
INITIAL = (max. size it needs to allocate)
NEXT = (size in MB it possible needs to extend to)
PCTINCREASE =0
The max a table or index ever extends is 3-4 extents. The pl/sql job runs okay the first time taking 2 hours to finish which inserts several hundred thousands on each of the 5 tables.
After the first time .. i delete the data using delete statements and load the same job again it takes more than 10 hours to complete. I tried coalescing the tablespace and rerunning it. That wouldnt work. The only way it seems to run at the same speed is after i drop the tables & recreate them.But this is not proper way to do it.
The tables are not fragmented as i have explicitly specified storage allocations for each.
I have tested the job several time trying out all possibility and every subsequent load takes a lot of time(atleast 10 hours).
I would appreciate it very much if anybody can give me some possible solutions to try out.
Anxious to hear from you.

Thanks
sarathi