Re: massive delete

In article <6g3l3s$8q3$1_at_as1000.javanet.com>, George Meltser <meltser_at_aigfpc.com> wrote:
>I hope somebody can help me.
>At night we have to replace about 20% of our data.
>First we delete the rows then insert the new ones.
>Delete may take any time from 2 to 4 hours.
>I did SQL_TRACE many times and was really surprised that amount of time it
>takes to delete the same number of rows from the same table changes from day
>to day dramatically, let's say from 3 min to 28 min or from 54 min to 22.
>The load on the server is the same at this time.
>What may cause those delays?
>None of these tables have chained rows, all are stored in 1 extent, i have
>not changed indexes on them for about 6 months.
>Is it Rollback segment shrinking?

Oracle is possibly finding different places to put the data based on differing amounts and placement of free space. Perhaps you are doing checkpoints or log switches sometimes during the delete. By not changed indexes, you mean you haven't rebuilt them? They must be way unbalanced.

>Thanx in advance,
>Regards,
>George
>
>
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!