-
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
-
Well, after deleting a lot of data from the db, there will be fragmentation at all levels (block, extent, segment). I would export/import the db after a bulk delete. Remember, indexes get fragmented too.
-
First of all thanks for the prompt reply.
My applications involves deletion and reloading of data.
Also when i delete the data i wipe out every data from that partition.
Also how does batch processing takes care of loads and deletes? It is not possible to do export and import everytime the data is to be deleted and re-loaded.
sarathi
