Richard,
Sorry for the confusion, let's see if I can clear it up a bit.
Background information:
our production database is monitored by a hosting company. This company
will extend the datafile sizes if there is less than 20% free space in
the tablespace. Each time they make a change, it counts against the
total number we have "bought" for the month. If we go over that number,
it's an additional charge. when they extend the datafiles, they extend
them just enough to allow for 25% free space.
This is a data warehouse, with daily data loads. when the data is added
to the index, there is a temporary drop in free space. So we allocate
more space to the tablespace than we believe the index partition will
use (it uses about 20GB, we allocate 40GB). This is what I mean by
"overallocate". Index partitions are created with a default of
pctfree=10. There are occasional deletes and re-inserts if the daily
load does not run properly or the data sent to be loaded is incomplete.
I do not have control over the load process.
Once the month has been loaded, there will be no more extension of the
used space in the index partition. However, there is a lot of space
that is not being used. We want to reclaim this space. So I rebuild the
partitions, using a smaller pctfree. I then use the total space (from
dba_data_files) minus the free space (from dba_free_space) to determine
how much space is used. Calculate in a "hedge" for that silly rule of
"at least 20% free space) and resize the data file down.
It may seem like a lot of work (it isn't, as I've automated it
completely, down to figuring out which tablespace's data files should
be resized) but it does reclaim the disk space. I recovered 130GB of
space that I can use before having to ask (beg? plead? grovel?) the
operations group for more disk.
Works for me. ..... Besides, it was fun to code it :)
Rachel
- Richard Foote <richard.foote_at_bigpond.com> wrote:
> 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
>
=== 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:44:25 CDT
