Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Databse File layout on only 4 drives Ideas?

Re: Databse File layout on only 4 drives Ideas?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 7 Feb 2003 02:16:42 +1100
Message-ID: <RSu0a.42202$jM5.106300@newsfeeds.bigpond.com>


All I would say Paul is that I am on record, interminably, as saying that, in practice, I have -and would still- separate tables and indexes for 'management convenience', and that is exactly what backup/restore and offline-dropping is all about.

In the context of this thread, however, when you only have 4 disks to play with, and are looking for a least-bad file distribution with reasonable performance... then it is appropriate to realise that such separation is not a boost to performance. And, specifically, that is why "a couple of you have written off the idea of splitting data and index across drives"

Regards
HJR "Paul Drake" <drak0nian_at_yahoo.com> wrote in message news:1ac7c7b3.0302052058.7e386f4a_at_posting.google.com... > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<l9pZ9.34802$jM5.89167_at_newsfeeds.bigpond.com>...
> > "David Platt" <david-platt_at_cogeco.ca> wrote in message
> > news:AepY9.54719$L47.8070994_at_read2.cgocable.net...
> > > I would argue that one drive should be dedicated to archive
desitination -
> > > don't want to be sharing that if we lose a disk.
> > >
> > > I am quite curious as to why a couple of you have written off the idea
of
> > > splitting data and index across drives. This is a practise that I
have
> > > followed for a while and I am wondering why it is being written off so
> > > quickly
> >
> > Because (here goes!) there is no intrinsic performance benefit to be
gained
> > by housing indexes and tables separately. That would only be a
possibility
> > if indexes and tables were read and written simultaneously, thus
introducing
> > a contention issue, but they aren't. Table and index reads are
serialised,
> > and writes are at the mercy of the LRU list and DBWR's own flushing
> > schedule.
> >
> > They *might* contend, of course. But they might not. In the absence of a
> > suitable number of hard disks, and in pursuit of the simplest rule for
the
> > most common situations, then index and table separation is a waste of
time.
> >
> > But it needs monitoring on a segment-by-segment basis to find the
> > exceptions.
> >
> > Regards
> > HJR

>
> Howard,
>
> Tables are the type of segment that I would like to keep in a
> tablespace whose datafiles I would never want to offline drop.
> Indexes are the type of segment that I would consider offline dropping
> in a bad circumstance. I also might have all of the objects (indexes)
> in that tablespace created with the attribute "NOLOGGING" and might
> keep the indexes with that attribute set.
> The indexes can be rebuilt. This is no revelation - I'm sure you have
> been thru far more recovery scenarios that me.
> So - even though you're on board with "indexes and tables need not be
> separated onto separate disks", I still believe that they do not
> belong in the same tablespace, as their backup/restore/recovery
> requirements differ significantly (nologging).
>
> If the datafiles for each tablespace (and maybe we're using the Juan
> Loiza strategy of small, medium and large extents for each) are
> striped across the same disks, so be it. But they - imho - do not
> belong in the same tablespace.
>
> Paul
Received on Thu Feb 06 2003 - 09:16:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US