Re: Locally Managed Tablespaces ... again!!!

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<akHT9.20745$jM5.57153_at_newsfeeds.bigpond.com>...
> "David Sharples" <david.sharples3_at_ntlworld.com> wrote in message
> news:YlFT9.171$p_1.1699_at_newsfep4-win.server.ntli.net...
> >
> > "Charlie Edwards" <charlie3101_at_hotmail.com> wrote in message
> > news:217ac5a8.0301100657.43251985_at_posting.google.com...
> >
> > >
> > > I get transferred to another project and, lo-and-behold, we're back to
> > > Dictionary Managed Tablespaces (sigh), with tables and indexes in
> > > separate tablespaces (double sigh).
> > >
> >
> > On my recent Oracle DBA Admin Part 1 course (8i) I was told to keep
> indexes
> > and tables in seperate tablespaces
> > to group segments of similar type together. Is this wrong now?
> >
>
>
> It's not wrong, exactly. But it's pointless if you're doing it for the "old"
> reasons. The Oracle course doco. for performance tuning *still* mentions
> putting tables and indexes into seaparate tablespaces because it avoids I/O
> contention when the table is updated. And that's the old myth which is still
> very prevalent.
>
> As was done to death here a while back, it's simply not true. When a table
> is updated, the index maintenance activities are *serialized*, so that they
> take place after the table update. Meaning that there is *no* I/O
> contention. So putting indexes into a separate tablespace for some
> performance gain is a complete waste of time.

It may have been done to death, but I think it still doesn't explain away what happens when you have many people pounding on the same table and index, updating, reporting or both. If you don't separate across LUN's or disks, as appropriate, you get hot spots (where the physical spot may be extremely elongated due to striping - but the logical spot is still hot, and there is a multiplicative effect when two spots are "close," by whatever the hardware/software defines as "close"). If you just look at it one process at a time, you might as well use MS SQL.
>
> However, it still generally makes sense to split them. Do you feel the need
> to back up your indexes at the same rate at which you back up your tables?
> After all, you could always just re-create the indexes if need be. But in
> the same tablespace, both tables and indexes must be backed up at the same
> time.
>
> If you want to perform maintenance on a data file, you offline the
> tablespace. If you offline a tablespace containing both indexes and tables,
> no-one can do any work at all. In separate tablespace, you could offline the
> index one, and people could still get at their data, albeit rather more
> slowly than they're used to.
>
> And so on. Splitting them is a *management convenience*, not a path to
> better performance.

To a point, but even though the hardware technology has improved to make it less important for performance, the software technology keeps catching up to make it just as important. At least, on a heavily used system. With only one LUN on a desktop raid, it's just like the old sparcs with one built-in disk [sigh...].

jg

--
@home is bogus.
We used to spend all night getting drunk and rebuilding pointer files.
 Now we spend all night reconstructing referential integrity.  Can't
get drunk.  That's progress?