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" <[EMAIL PROTECTED]> 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 > > > --- [EMAIL PROTECTED] 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." <[EMAIL PROTECTED]> > > > Date: 2003/09/30 Tue PM 01:34:28 EDT > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > > 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 > > > [EMAIL PROTECTED] > > > > > > -----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 <[EMAIL PROTECTED]> > > > > > 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).