Re: delete performance problem

From: Steve Adams <steveadams_at_acslink.net.au>
Date: 1997/10/27
Message-ID: <345426be.2439992192@newsserver.trl.oz.au>#1/1

Hi Barry,

I guess that the execution plan for your delete would be an index range scan. If so, the explanation for your progressive performance degradation may be due to the fact that when all the keys have been deleted from an index leaf block, that block is added to the free list, but not immediately unlinked from the B*-tree structure (it is only unlinked when claimed by a subsequent insert). The net effect is that subsequent index range scans for the same date_time value will have to scan through a whole lot of empty blocks before they find 2500 rows to delete.

As to a solution, this looks like a classic case for partitioning the table, so that the "delete" can be accomplished by truncating one partition.

Hope this helps,

Steve Adams
steveadams_at_acslink.net.au

---

On Fri, 24 Oct 1997 12:13:33 -0700, Barry Mahony <mahony.barry.bj_at_bhp.com.au> wrote:

>[snip]
>The table contains approx 2.2m rows, 324,000 are deleted each day.
>Another process on a SCO server inserts 13500 rows each hour into the
>table. It takes about 19 hours to complete the delete of 324,000 rows
>daily. The server is a Sun SparcServer5 with 96mb RAM.
>
>I have noticed on a number of occassions when running the delete process
>manually is that the number of rows deleted each hour is less and less ie
>
>1st hour 60,000
>2nd hr 40,000
>3rd hr 35,000
>4th hr 30,000
>
>etc and eventually tapers off at 18-20,000 rows/hr. There have been no
>other users on the system at the times when this has been monitored.
>
>Does anyone have any ideas as to what may cause this and where I should
>be looking ??
>
>Thanks
Received on Mon Oct 27 1997 - 00:00:00 CST