Re: optimizing in delete-statement

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Tue, 11 Feb 2003 12:18:17 -0800
Message-ID: <3E495A89.1061B9A1@exesolutions.com>

Ralf Beutler wrote:

> DA Morgan schrieb unter anderem:
> > Ralf Beutler wrote:
> >
> >> DA Morgan wrote:
> >>
> >> > My guess is that you have multiple things wrong. Start with an EXPLAIN PLAN.
> >> > .. post the plans.
> >>
> >> DELETE STATEMENT ()
> >> DELETE () TROUTE
> >> NESTED LOOPS ()
> >> VIEW () VW_NSO_1
> >> SORT (UNIQUE)
> >> TABLE ACCESS (BY INDEX RO TUSERXORGANIZER
> >> INDEX (RANGE SCAN) I_TUSERXORG_FKUSERID
> >> INDEX (UNIQUE SCAN) SYS_C001926
> >>
> >> I can't see something wrong.
> >>
> >
> > This is taking 30 seconds on 58,000 rows? Hmmm.
> >
> > Is it 58,000 rows in the table or 58,000 rows that are being deleted? What is the
> > breakdown between total rows and deleted rows?

>

> I think there are 3 to 5 rows to be deleted in average.
> 58,000 rows is the total number of rows.

>

> Remark: I installed a system for tests on a WinNT System and I can't
> reproduce that problem. The datafiles on the unix-System are placed in
> RAID 5. I thought about rollback segments: there are 12 RBS and file
> size is 4MB for each. Redo-Log-Groups: 24 and 20MB in size for each.

>

> > Please don't snip as it is hard to remember specific posts when I
> > can't see them.

>

> Hmmm. This causes on postings in german newsgroups. I can't post there
> generating fullquotes. But here's the statement again:

>

> explain plan for
> DELETE FROM tRoute
> WHERE RouteId IN
> (
> SELECT ItemId
> FROM tUserXorganizer
> WHERE Type = :1 AND FKUserId = :2
> )
> ;

>
> br | rb

You definitely have a problem but it isn't your SQL statement. Something is causing a huge wait. I ran your statement on a test table with 100K rows deleting 3 with indexes on a 500MHz PIII with 512MB RAM on my notebook. It took far less than one second.

Look at the various data dictionary views that hold wait information and see what is there. Also I notice that your EXPLAIN PLAN listing didn't contain the cost. What is it?

Daniel Morgan Received on Tue Feb 11 2003 - 14:18:17 CST