Re: massive delete

On Sun, 05 Apr 1998 17:46:01 +0200, Olivier Bercovitz <oberco_at_club-internet.fr> wrote:

>Hello George,
>do you wirk with cost-based optimizer ?
>Try to analyze your table just before delete the rows
>or
>try to delete statistics (with analyze table xxxx delete statistics;)
>both tries with optimizer_mode=choose
>
>Tell me the result please, I am interested by it
>
>Regards. Olivier
>
>George Meltser 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?
>> 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 Fri Apr 17 1998 - 15:46:20 CDT