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)
--- [EMAIL PROTECTED] 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" <[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 > === 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).
