Since you're deleting half your table, a (proper) index will not help the
speed, most likely. I'm shooting from memory here, since I don't have
all the message traffic, but as I recall, you indicated a concatentated
index which didn't include or start with the referenced where columns...
But, deleteing half the table would ignore the index, yet you're wasting
cycles maintaining the index while deleting, as well as writing all the
transactions to the redo. And, I agree with the others that your RAID 5
config, as well as general file configuration is hurting here as well.
Best bet would be, as suggested already, create a temp table that holds
the data to keep, trunc the original, and repopulate... (or create temp,
drop original and rename temp to original if indexes, constraints, etc
aren't an issue)
Good luck.
Greg
Allen <allen.brost_at_motorola.com> wrote in message
news:3912D3E4.8076C3D3_at_motorola.com...
> 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 Tue May 09 2000 - 00:00:00 CDT
