Re: ASSM bug: slow INSERT after uncomitted DELETE
Date: Mon, 10 Aug 2009 20:37:35 -0700 (PDT)
Message-ID: <9fc8ead2-1ed5-47bd-8af4-ee1bd74a3f11_at_d4g2000prc.googlegroups.com>
On Aug 8, 7:47 am, ca111026 <ca111..._at_gmail.com> wrote:
> The example given above is not artificial. I work in environment where
> we have more than hundred production
> databases, and Apps Support is mostly outsourced/offshored. As
> databases grow the data needs to be purged,
> some databases have partitioned tables but many don't. So DELETE needs
> to be run, often using condition
> on a column without index like LAST_UPDATED_DATE. So Apps Support runs
> this delete, they make a mistake
> a delete too much, inserts are affected, CPU usage/disk I/O goes
> through the roof, people start blaming SAN,
> SRDF gets switched to adaptive mode, etc. It is not an easy problem to
> diagnoze, normally when going through
> Statspack reports people just skip past single-row insert statements
> as they rarely experience problems.
Yes but realistically speaking, wouldn't it be easier to just commit at end of big number of deletes? We know that fixes the problem with the bitmap slowness as described by Jonathan. In fact, if you commit the first session while the second session is doing the second batch of inserts, the second session speeds up to normal times.
Perhaps even more significantly, if you delete only a portion of the table instead of the lot, then the second session's insert is not affected. Normally, I would expect anyone wanting to clear a table to truncate it, as opposed to deleting all rows: truncate is far more efficient.
We do a lot of delete-insert in the ETL tables of our DW and don't
have this problem at all.
Mostly because a big delete is always followed by a commit, rather
than letting other concurrent processing go through in that table. Or
we do a truncate instead of total table delete.
In general, I wouldn't consider designing in the requirement for big deletes in long transactions, when a table is also under multi-user access: that will sooner or later cause a performance problem, assm or no assm. If one tries a similar concurrent exercise with heavily indexed tables, things will go haywire anyway whatever storage model we use.
>
> Which brings me back to the original question - Why use ASSM in the
> first place? It does not look like it offers
> any benefits (may be ALTER TABLE SHRINK but how often you use it?).
> Could someone point to a simple test
> (however artificial) that shows advantage of ASSM?
To me the biggest advantage in using assm is not having to constantly
worry about buffer busy waits: they virtually disappear with this type
of storage.
Provided it doesn't affect other normal access, I'm happy with the
trade-offs.
In other words: I'd rather ask developers to not do big deletes
uncommitted - a rather dubious practice to start with - than having to
figure out at what time and where should I be changing per table
allocation parameters to resolve busy waits and other similar
contention.
Received on Mon Aug 10 2009 - 22:37:35 CDT