Re: Oracle Myths

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0205291028.79e0b197_at_posting.google.com...
> While I have no doubt that SELECT and DELETE may have little
> difference, what about UPDATE?

Explain why you think update should behave any differently (in respect of contiguity of extents or lack thereof making a difference) from the other forms of DML which were tested?

>What about DROP TABLE?

As I said elsewhere, a drop table is ultimately a bunch of deletes from the data dictionary. Explain why you think it would produce different results from the delete tests I performed. My point is that I can postulate why the contiguity of extents *shouldn't* make a difference (because even a single extent is not comprised of physically contiguous blocks). I have no idea on what basis you postulate that it should make a difference.

>Was the DELETE a
> conditional DELETE or a DELETE of all rows?

Which bit of the phrase (from my original post) "The script then performed select all records, then delete all records, " did you miss?

>Again, give us the full
> story.

I did, actually, at the time. If you want to see the results, it occurs to me that they should be visible at google. Oh, and incidentally, the test was performed on a Solaris system, RAID 5; a Win2000 system single disk; a Win2000 system raid 1; and a Tru64 system - god knows what the disk array was there, I didn't get a chance to check.

HJR
>Also, where the extents contiguous (i.e. created all at the
> same time with no other segments) or did you make sure and distribute
> them randomly across the tablespace datafiles involved?
>
> Your statement of experiment lacks even a hint of scientific method,
> thus your proof statement is not valid.
>
> Mike
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<ad0p0k$7pg$1_at_lust.ihug.co.nz>...
> > "William Rice" <ricew_at_operamail.com> wrote in message
> > news:1f1a539b.0205280730.5c8e188d_at_posting.google.com...
> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:<acmdu7$cdt$1_at_lust.ihug.co.nz>...
> > > > "William Rice" <ricew_at_operamail.com> wrote in message
> > > > news:1f1a539b.0205231415.51b83543_at_posting.google.com...
> > > > > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
> > message
> > news:<3ce36ec0$0$8513$ed9e5944_at_reading.news.pipex.net>...
> > > > > <SNIP>
> > > > > > >
> > > > > > > Seperate tables and indexes for performance reasons.
> > > > > >
> > > > > > see the huge thread on this earlier at
http://shrinkalink.com/201
> > > > > >
> > > > > > I think my summary of this is that in general seperating data
and
> > indexes
> > > > > > should be done for
> > > > > > 1) management reasons only.
> > > > >
> > > > > While I didnt read the whole thread, I guess what I got from it
was
> > > > > the old statement, benchmark your system and find out...
> > > > >
> > > > > <SNIP>
> > > > >
> > > > > An example of where separating indexes from a table might be
> > > > > beneficial would be if you happen to do lots of full table scans
on a
> > > > > particular table. If you make it the only inhabitant of a
tablespace
> > > > > (or tablespaces if it is partitioned), you avoid having to skip
past
> > > > > all of the extents you are not interested in.
> > > >
> > > >
> > > > Myth alert. The contiguity of extents has absolutely zero bearing on
> > > > performance. None whatsoever. For the simple reason that the blocks
of a
> > > > single extent are not physically contiguous on disk anyway. They are
> > > > scattered all over the physical platter... and therefore, you are
> > forever
> > > > having to skip around the place whether there is one extent of one
> > segment,
> > > > or several thousand extents belonging to several hundred segments.
It
> > makes
> > > > no difference.
> > > >
> > > > Regards
> > > > HJR
> > > >
> > >
> > > I would be interested in seeing any documentation, results from
> > > benchmarks, or anything of that nature that would show this to be the
> > > case.
> >
> > Been there and done that, but I am no longer allowed to post you to the
> > results, I'm afraid. I had a script that created either many hundreds or
> > just a handful of extents, large and small, contiguous and
non-contiguous.
> > The script then performed select all records, then delete all records,
and
> > there was absolutely no variation between any of the runs, except that
which
> > was within normal variation for these sorts of tests.
> >
> > >Even with the volume mangers that do scatter the data around,
> > > they figure out if you are doing a sequential scan, and try to
> > > prefetch efficiently in order to compensate for the fact that the data
> > > is scattered around. If your data is scattered around, you don't get
> > > to benefit from this.
> >
> > Just remember that a single Oracle block is not contiguous on disk.
> > Comprised of many O/S blocks, it's all over the place. Extrapolate from
a
> > single block to an extent, and the truth of the matter will be evident.
> >
> > >
> > > I haven't been able to find the benchmarks I did a couple of years ago
> > > otherwise I would post the results.
> >
> > It seems we are in the same boat!
> >
> > >
> > > I would also be interested in where you go the information that the
> > > data that seems contiguous from a raw device is actually scattered all
> > > over the disk.
> >
> > I didn't say anything about raw devices. For the very good reason that
they
> > are the one thing where contiguity is possible, since there is no file
> > system taking it upon itself to place your data.
> >
> > >I will admit to not having any documentation to the
> > > contrary, because to me this has always been a given documented
> > > otherwise by the disk vendor. I am aware that some bad blocks might
> > > end up being written to another part of the disk, but this would be
> > > the exception, not the general case.
> > >
> >
> > Don't extrapolate past what I actually wrote. Raw is a special case, and
> > with raw you truly can get physical contiguity. Anything else: what you
> > *think* is contiguous actually isn't.
> >
> > Regards
> > HJR
> >
> > > If this is to far off topic for this group, I would be happy to take
> > > the discussion offline.
> > >
> > > Will
Received on Wed May 29 2002 - 17:14:53 CDT