Re: Oracle Myths

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Wed, 29 May 2002 06:22:18 +1000
Message-ID: <ad0p0k$7pg$1@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 Tue May 28 2002 - 15:22:18 CDT