Re: massive delete

You might also want to check if the data blocks are being cached. Try downing and restarting the database to see if the timings become more consistent - they should. If they don't, it may be a factor outside the database.

Having said all that, why not look at partitioning the table to dramatically improve the delete (i.e. truncate partitions instead) performance (and pre-drop and post build indexes too). We've speeded some of our bulk delete/update runs by a factor of 20 times by doing this.

Just my two pennies.

Steve Phelan.

>>> 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?
>>> Thanx in advance,
>>> Regards,
>>> George
>>
>>
>
>Have you checked whether all rollback segments are the same size ?
>Oracle randomly chooses an available rollback segment. Often there is
>a small rollback segment left over after the initial database
>cration. (Owned by SYS) When this is used for a big transaction
>performance can go way down.
>
>I hope this helps
>
>
>Henk de Wilde
>
Received on Sat Apr 18 1998 - 12:31:54 CDT