Whilst the vast majority of extents will be the "published" ones - 64k, 1m, et al, you occasionally get variants.
I have only three gripes with auto-allocate: a) you can't perform the "can I extend" check on your tablespaces. You cannot predict with 100% certainty what the size of the next extent will be. You have to make assumptions. b) you minimise but not eliminate the fragmentation problem. Its rare but its relatively easy to concoct an example where you have 'n' bytes of free space, but cannot allocate an extent of size less than 'n' bytes c) Cynicism. If there is no problem with 'n' extents (n > insert high number here) , why does Oracle implement a solution designed to keep a lid on their number. Besides extent map blocks, is there some serious problem that Oracle is not telling us at the (say) million extent mark? If there is, then what is the problem. If there is not, then why doesn't Oracle abandon the concept altogether and just enforce something similar to what we see in file systems where every extent is a strict (say) 1m in size. Cheers Connor --- "MacGregor, Ian A." <[EMAIL PROTECTED]> wrote: > My criticism of the defrag paper was that it did not > address what to do when a segment grew large enough > to belong in a tablespace with a larger uniform > extent size. Moving the segment creates holes in > its original tablespace which may close only in the > fullness of time. Physical backups of the files > comprising the original tablespace include this > wasted space, this is compounded by how many days > backup you keep available, and the number of copies > of backups. > > You have chosen to get around the segment migration > problem by using one very large extent size for > everything. Don't you find 5M extents wasteful? > What is your block size and the median number of > used blocks for your segments outside of the system > tablespace? How many such segments are there?. > > Also many of us use a single backup system to > support multiple databases. The number of segments > outside the system tablespace here is over 125,0000. > Making all segments at least 5M in size would have > a major impact on file sizes, which in turn would > have a major impact on backup times, and possibly > the size of the tape library needed. > > I'm interested in the flaws in autoallocate. Does it > allocate the wrong amount of space? > > > Ian MacGregor > [EMAIL PROTECTED] > > -----Original Message----- > Sent: Tuesday, September 30, 2003 10:50 AM > To: Multiple recipients of list ORACLE-L > > > 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)? > > > Rollback (or undo) ditto? > > > Write intensive tablespaces on RAID 1+0 (or > should that be 0+1)? > > > Read > > > intensive tablespaces on RAID ? (I guess 5 is OK > since it's > > cheaper > > > than 1+0 and you won't have the write penalty) > > > > > > While we're at it could we blow up the OFA myth? > Since you're > > tablespaces > > > are on datafiles that are on logical volumns > that are on physical > > devices > > > which may contain one or many actual disks, does > it really make > > > sense > > to > > > worry (from a performance standpoint) about > separating tables and > > indexes > > > into different tablespaces? > > > > > > We have killed the "everything in one extent" > myth haven't we? > > Everybody's > > > comfortable with tables that have 100's of > extents? > > > > > > And while we're at it, could we include the > Oracle 9 multiple > > blocksizes > > > and how to use them. The best that I've seen is > indexes in big > > blocks, > > > tables in small blocks --- uh, oh, time to > separate tables and > > indexes. > > > > > > Maybe we will never get rid of the OFA myth. > > > > > > Just venting. > > > > > > Tired of arguing in front of management with > Oracle certified DBAs > > that > > > RAID 5 is not good, OFA is unnecessary, and > uniform extents is the > > only > > way > > > to go. Looking for a big stick to catch their > attention with. > > > > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Tim Gorman > > 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: Thomas Day > > 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: Cary Millsap > > 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: MacGregor, Ian A. > > 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: <[EMAIL PROTECTED] > 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: MacGregor, Ian A. > 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). ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ________________________________________________________________________ Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
