This is a possible problem you are having (and to clarify my point a
bit). You said that the batch process did massive deletes. It is true
that the free space may be used eventually with subsequent inserts, but
your problem occurs during the deletes. When Oracle performs deletes
the space is put on the freelist for subsequent use. However, that
space can only be freed if you drop the table or truncate it. How is
this hurting you? Based on your explanation of the problem and what has
been fixing it, I would think that full table scans or at least large
index scans are being performed during the batch process. If you have
not deallocated the empty space with a drop or truncate the full table
scan will read every block to the high water mark, which may take some
time if you originally had a lot of rows. Rebuilding the indexes (or
creating a new one) may help the problem as you described. Since you
have taken care of the most obvious problems, my money is betting that
your issue is SQL related. It is likely that the SQL is so poorly
written it is causing your issues. You may have to demonstrate how to
write the query correctly, or at least take advantage of existing or
new indexes. You may even have to explore a rewrite in PLSQL. I have
seen developers often write terrible SQL simply because they did not
want to write PLSQL. Cursor processing with array fetching on the
types of operations you describe are usually more efficient and easier
to tune. In addition, you can include frequent commit logic.
Again, I emphasize that I have never heard of anyone dropping and
recreating a database to continually correct his type of problem. That
is stupid and I would defy your users to find legtimate documentation
suggesting such a practice.
Received on Fri Jun 24 2005 - 08:18:56 CDT
