Re: delete statement taking forever

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 09 Aug 1999 22:43:48 +0800
Message-ID: <37AEE924.6294@yahoo.com>

dbanguyen_at_hotmail.com wrote:
>
> Hello, I hope someone can help me with a simple delete statement. Here
> is the statement and the qplan. The appropriate fields are indexed.
> Will it help to analyze the table with compute statistics? The
> activities table has about 700k records and the mds_removed_id has
> 60k. Any way to optimize this statement? Also, I am finding that
> there is a lot of disk swaps on the server when running this.
> Thanks for your help,
> Dave
>
> explain plan
> set statement_id = 'del_mds' for
> delete from activities where contact_entity_id in
> (select entity_id from mds_removed_id);
>
> Q_PLAN
> ------------------------------------
> DELETE STATEMENT
> NESTED LOOPS
> VIEW
> SORT UNIQUE
> INDEX FULL SCAN MDS_REMOVED_ID_IDX
> TABLE ACCESS BY ROWID ACTIVITIES
> INDEX RANGE SCAN R_ACTIVITIES7_FK
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

Maybe also take a look at your alert logs...

If you are getting a lot of "checkpoint not complete" messages in there it means that you're redo logs are too small..

Thus a lot of your delete time is spent with the database doing nothing (ie waiting for the redo log bottleneck to clear) --

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Aug 09 1999 - 09:43:48 CDT