Re: Slow delete query...

I will agree with Sybrand in that Raid-5 is probably your problem, but redo's do make a difference.
Delete's require writes. Writes with Raid-5 require 4x the processing of reads.

I import 10G in 1hour. Your database isn't tuned, and if your deletes are taking that long you have a lot more than just one problem. You most likely have quite a few issues. Have you issued a trace on the delete.

An index on the column MyMarket might help some.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"Allen" <allen.brost_at_motorola.com> wrote in message
news:391082D8.69B13849_at_motorola.com...


> Here is my query on a table that contains about 2G of info:

>

> SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS;

> delete from peakData where

>     day < '01-jan-00' AND

>     MyMarket in ('AT','BO','CA','CB','CH','DA','DE','HO','KS','LA','MA');

> commit;

>

>

> This statement seems slow to me.  Our machine is a Sun Enterprise 250.

 The


> query seems to be removing about 200 megabits per hour.  Does this seem

 normal?


> Man this seems like crap to me.  How do people with 200+G database deal

 with


> query times like this?  Is this just Oracle or is this normal for the

 industry


> of databases.  To do an import of 8G of data into Oracle 8, it took 5

 days!  5


> FREAKING DAYS!.  Like I said, what do people use when databases approach

 the


> 200+G size?  I believe managing this monster would be impossible.  Am I

 missing


> something?  Is there some kind of block query that could move data more

> efficiently, rather than line-by-line to the rollback segment?  I am

 assuming


> this is what is happening.

>

> I am already using a large rollback segment with extents of 50Meg.  We

 have 4


> Ultra SCSI disks set up in a raid 5 configuration, so we have one logical

> drive.  Would performance improve if we added another disk on a separate

> controller and put the rollback segment on that drive?

>

> Any help would be appreciated.

>

> Thanks

>