Re: Slow delete query...

Thanks for your comments so far!

I have some more things to add to this. I have been looking at the time it takes to do these delete moves. At 200 meg per hour, I really don't think having an index (which it is indexed!) would even matter. The table size is only 1.6G. To delete half of the table it takes 4-5 hours. Something else must be wrong to explain this. Hell, It would be more efficient to use flat files! I just cannot understand where the time is going. Maybe there is something with the redo logs. I far as I know, we are not using the redo logs for backup and their current size is only 500K.

My question again... is it Oracle or is it all databases?

Any more comments are welcome.

• Allen

Sybrand Bakker wrote:

> Allen <allen.brost_at_motorola.com> schreef in berichtnieuws
> 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
> >
>
> If you have only raid-5 that's one of the reasons why it is so slow. You
> need to have your redolog files on Raid 0, Raid 1, or Raid0+1. The rollback
> segment doesn't really matters.
> You don't tell us: whether that table is properly indexed?
> Whether the database is properly tuned?
> Whether the online redologs are big enough so no error messages in the alert
> log occur, and so many many other things, that it is way too early to start
> shouting at Oracle. (My experience is that this is usually done by people
> who don't know too much about Oracle)
>
> If you are deleting more than say 10 percent of that table, did you consider
> to copy the rows you need to retain to a temporary table, to truncate the
> original table (which is DDL, and happens in a second) and to copy those
> rows back?
>
> In short: you still need to start to resolve this and there is no proof it
> wouldn't eventually work.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
Received on Fri May 05 2000 - 00:00:00 CDT