Re: RE: Separate Indexes and Data

Hi Rachael,

You have me a little confused here.

What do you mean by "We over allocate space" ? To the index segments or to the tablespace ?

Why the need to rebuild the indexes ? How are they using more space than required ?

What do you mean that you adjust the pctfree so you can determine "how small you can resize them to" ?

You seem to go to a lot of trouble, I'm just failing to see what it all achieves ???

Cheers

Richard
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 01, 2003 4:34 AM

> Nuh uh, not me... I have never used or experimented with auto-allocate.
>
> I separate indexes and tables so that I can reclaim space by rebuilding
> the indexes into smaller space.
>
> I've just completed writing the scripts for the following:
>
> we have a data warehouse, partitioned on the biggest table on date by
> month. There are 10 or 11 indexes on this table. We overallocate space
> when we create the new partition for the next month. Data is loaded
> daily. The hosting company has an automated procedure to add space to
> the datafile if the used space percentage is greater than some number
> (we get charged each time they do this, and they never allocate enough
> space so they do it over and over towards the end of the month).
>
> since the indexes are increasing on a daily basis, we overallocate the
> space. The next month, I go out, determine the
> partition/tablespace/datafiles that need to be resized (naming
> standards rule in this case), rebuild the indexes into an interim
> tablespace, rebuild them back to the original one with a smaller
> pctfree and then determine how small I can resize them down to.
>
> If there were table data in these tablespaces, I'd be out of luck on
> trying to reclaim space
>
>
> --- rgaffuri_at_cox.net wrote:
> > the defrag paper was written back in 1998 I believe. Uniform extents
> > were a good solution pre-9i. We use them here on our 8i databases. I
> > stick with an uniform 5m extent size even though I have tables that
> > can fit into 128k extents, but feel that the overall time savings by
> > using 1 extent size makes up for this.
> >
> > unfortunately unlike most systems we cannot break up our tables into
> > different tablespaces. We use transportable tablespaces to batch
> > publish data to data marts. New tablespaces mean additional
> > transportable tablespaces and more places for stuff to go wrong.
> >
> > I saw some posts on dejanews recently from some pretty experienced
> > DBAs stating that there may be 'flaws' in auto-allocate leading to
> > poor extent sizes that leads to fragmentation. I believe Rachel
> > Carmichael made a post on here a few months back with the similiar
> > experience(could be wrong). Due to even the 'small' chance of flaws
> > in auto-allocate, Im thinking of waiting for version 10g before using
> > it. Just to be safe. Not worth risking a defrag on a production
> > system.
> > >
> > > From: "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
> > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > Subject: RE: Separate Indexes and Data
> > >
> > > 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)?
> >
> === message truncated ===
>
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search
> http://shopping.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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: Richard Foote
  INET: richard.foote_at_bigpond.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).