Re: Separate Indexes and Data

Hi!

In VLDB environments, it is mostly cheaper to restore and recover the index tablespace datafile in case of block corruption. In my experience, I've been lucky and have been able to get rid of corruptions that way, but I'm sure some people have worse experiences, especially when redologs are corrupted as well.

Anyway, rebuilding a huge index is much more expensive operation than recovering & restoring a datafile (rebuild requires a lot of IO, CPU and temp space). With 9i, the recovery is even better, if you use RMAN - you just can restore&recover one single block is you want. There is one nice exception -> if the corruption occurs in a local index partition, it is possible to rebuild only this particular partition, without need to recover anything. (Partitioning can be considered alsi as a high availability feature, in addition to performance improvements).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, September 30, 2003 8:34 PM

> I'd be very interested to know how many people have their index
tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current.
>
> I understand the argument proffered is separating indexes and data can
mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables.
>
> On the size of the segments: The paper entitled "How To Start
Defragmenting and Start Living" or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper
> Never mentioned what to do when an object that used to fit nicely into
the 128k extent category now more properly belongs to the 4M category. If you move the data, large holes are left in the other tablespace, and while this does not impact Oracle performance, it does mean that your physical backups are larger than necessary. I am in the process of migrating from uniform to autoallocated extents. This means extents of different sizes share the same tablespace. The extent sizes being multiples of each other. This removes the argument about not having indexes and data in the same tablespaces due to their different sizes.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Monday, September 29, 2003 8:10 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Thomas,
>
> It *is* a good idea to separate index data from heap data into different
tablespaces. But the reason isn't solely to eliminate I/O competition. Even if I/O competition isn't an issue for you (and the OFA Standard doesn't say that it will be), then it's *still* a good idea to separate your index data from your heap data, for reasons including:
>
> * Index segments have different backup and recovery requirements than
their corresponding heap segments. For example, as Peter mentioned, if you have an index block corruption event, then it's convenient to just offline, kill, and rebuild an index tablespace. If the indexes and data are mixed up in a single tablespace, this is not an option. Another
> example: If you construct your backup schedule to make media recovery time
a constant, then you probably don't need to back up your indexes on the same schedule as you back up your heaps. But unless they're in different tablespaces, this isn't an option either.
>
> * Index segments are usually smaller than their corresponding heap
segments. Using separate tablespaces allows you to use a smaller extent size to conserve disk storage capacity.
>
> I don't think I ever wrote that you need to put indexes and their
corresponding tables/clusters on separate disks, but you do need to be
> *able* to do that if your I/O rates indicate that you should.
>
> For the original OFA Standard definition, please see section 3 of the
document called "The OFA Standard--Oracle for Open Systems," and section 5 of "Configuring Oracle Server for VLDB," both available for free at www.hotsos.com.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> Thomas Day
> Sent: Monday, September 29, 2003 9:05 AM
> To: Multiple recipients of list ORACLE-L
>
>
> My struggle is not with the directory layout OFA.
>
> It is with the "mythical" OFA that every DBA that I have talked to knows
all about. Where ORACLE says that if you are a good and competent DBA you will separate your table data and your index data into two separate tablespaces so that one disk head can be reading index entries while another disk head is reading the table data. You've never run into that?
>
>
>
>
>
> Tim Gorman <tim
>
> @sagelogix.com> To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: cc:
>
> ml-errors Subject: Re: BAARF
>
>
>
>
>
> 09/28/2003 09:44
>
> PM
>
> Please respond
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
> Thomas,
>
> Please pardon me, but you are off-target in your criticisms of OFA.
>
> It has never advocated separating tables from indexes for performance
purposes. Ironically, your email starts to touch on the real reason for separating them (i.e. different types of I/O, different recovery requirements, etc). Tables and indexes do belong in different tablespaces, but not for reasons of performance.
>
> Cary first designed and implemented OFA in the early 90s and formalized it
into a paper in 1995. Quite frankly, it is a brilliant set of rules of how Oracle-based systems should be structured, and a breath of fresh air from the simplistic way that Oracle installers laid things out at the time. It took several years for Oracle Development to see the light and become OFA-compliant, and not a moment too soon either. Just imagine if everything were still installed into a single directory tree under ORACLE_HOME? All of things you mention here have nothing to do with OFA.
>
> Please read the paper.
>
> Hope this helps...
>
> -Tim
>
> P.S. By the way, multiple block sizes are not intended for
> performance
> optimization; they merely enable transportable tablespaces
between
> databases with different block sizes.
>
>
> on 9/25/03 11:04 AM, Thomas Day at tday6_at_csc.com wrote:
>
> >
> > I would love to have a definitive site that I could send all RAID-F
> > advocates to where it would be laid out clearly, unambiguously, and
> > definitively what storage types should be used for what purpose.
> >
> > Redo logs on RAID 0 with Oracle duplexing (y/n)?
> > Rollback (or undo) ditto?
> > Write intensive tablespaces on RAID 1+0 (or should that be 0+1)? Read
> > intensive tablespaces on RAID ? (I guess 5 is OK since it's
> cheaper
> > than 1+0 and you won't have the write penalty)
> >
> > While we're at it could we blow up the OFA myth? Since you're
> tablespaces
> > are on datafiles that are on logical volumns that are on physical
> devices
> > which may contain one or many actual disks, does it really make sense
> to
> > worry (from a performance standpoint) about separating tables and
> indexes
> > into different tablespaces?
> >
> > We have killed the "everything in one extent" myth haven't we?
> Everybody's
> > comfortable with tables that have 100's of extents?
> >
> > And while we're at it, could we include the Oracle 9 multiple
> blocksizes
> > and how to use them. The best that I've seen is indexes in big
> blocks,
> > tables in small blocks --- uh, oh, time to separate tables and
> indexes.
> >
> > Maybe we will never get rid of the OFA myth.
> >
> > Just venting.
> >
> > Tired of arguing in front of management with Oracle certified DBAs
> that
> > RAID 5 is not good, OFA is unnecessary, and uniform extents is the
> only
> way
> > to go. Looking for a big stick to catch their attention with.
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tim Gorman
> INET: tim_at_sagelogix.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Day
> INET: tday6_at_csc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).