what Jay said :)
in the beginning (hopefully this will have settled down), there were a
large number of deletes of daily data as the load process was "refined"
(ie, bugs were fixed)
- JayMiller_at_tdwaterhouse.com wrote:
> I assume that what Rachel is referring to is the fact that indexes
> will
> generally not release much space when the underlying rows are
> deleted. They
> just keep growing, so if you have a large indexed table that
> frequently
> deletes and inserts the indexes can grow to fairly ridiculous sizes
> over a
> period of time. We just went through the exercise of rebuilding
> indexes on
> a db supporting a 3rd party app and reclaimed about 70% of the
> allocated
> index space.
>
> Jay Miller
> Sr. Oracle DBA
> x68355
>
>
> -----Original Message-----
> Sent: Sunday, October 12, 2003 7:39 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
>
=== 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).
Received on Mon Oct 13 2003 - 17:49:24 CDT