Re: ASSM bug: slow INSERT after uncomitted DELETE
From: ca111026 <ca111026_at_gmail.com>
Date: Fri, 7 Aug 2009 14:47:32 -0700 (PDT)
Message-ID: <0f045d6e-3f36-44d9-b8ea-01b10d421447_at_o9g2000prg.googlegroups.com>
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.
Date: Fri, 7 Aug 2009 14:47:32 -0700 (PDT)
Message-ID: <0f045d6e-3f36-44d9-b8ea-01b10d421447_at_o9g2000prg.googlegroups.com>
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.
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?
Received on Fri Aug 07 2009 - 16:47:32 CDT