| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Stop defragmenting and start ...
Stu Goss of Burlington Coat Factory Warehouse presented a paper at 1990 IOUG
(Anaheim) entitled "Keeping it together with GLUE"
GLUE was a BCF utility that read the freespace from each tablespace and figured out the sizes of JUNK001 tables that must be created to "GLUE" all the freespace into contiguous chunks to repair the highly variable fragment sizes left after Oracle Applications upgrade scripts. The point was to run GLUE after all the next sizes had been "repaired" to one of a few carefully chosen sizes (mutual submultiples definitely, but preferably one size per tablespaces). At that time, the university crowd was very keen on pctincrease 50 so that students didn't have to do sizing and the number of extents was self limiting. We were quite loudly accused of being less than intelligent (I'll tell Mogens and maybe Pete the actual words in person, I wouldn't repeat them here) in setting pctincrease to zero and having to so carefully reset the pctincrease and next sizes for the HUGE applications data dictionary, and where did we get off changing Oracle's chosen storage characteristics, anyway.
My paper "Managing many instances on many drives" at the same conference explained grouping tables in tablespaces by growth rate and using one next size per tablespace execept for the smallest tables where you might save space (disk was not cheap back then) by using a few common multiples. NB: The instances in my paper's title was a slight misnomer, these were NOT Oracle Parallel Server instances, merely 8 databases on something like 60 disk drives, which qualified for "many" of each in 1989.
GLUE was obsoleted by overloading non-zero pctincrease default on the tablespace (note to developers -- overloading is ALWAYS a bad idea), leading to the hilarious popourri of sizes that I *believe* had something to do with inspiring Cary to write his famous paper.
Please note that GLUE was *not* some attempt to eliminate freespace fragmentation for performance, but rather to make the report of available free fragments of the maximum next size for a tablespace simpler, and remember this was in the context of no unlimited extents, max 62 files, max 8K blocksize, and only one blocksize per database. In that context, you might need serious lead time to plan an outage to add a file, because if you were close to 62, you might need to plan to unload, drop the tablespace, remake the tablespace with a bigger file, and then reload.
NB: If you find a copy of either of those papers, please carefully disregard most of the advice. I believe they were both truthful and insightful in 1990, but most of the concerns addressed have thankfully been obsolete for a long time.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Don Granaman
Sent: Saturday, June 12, 2004 2:45 PM
To: oracle-l_at_freelists.org
Subject: Re: Stop defragmenting and start ...
Cary's paper is the first such that I remember. It served as validation - that I wasn't a raving radical (at least not in that respect). I started doing "uniform extents" per tablespace in about 1991/1992 - within a year or so of when we got Oracle6 (with the TPO option!). [It wasn't divine inspiration then - a co-worker suggested it after we encountered some "fragmented free space" issues and it just made sense.] At the time the "recommended practice" was to size the initial extent large enough to hold all the initial data and the next extent significantly smaller - with a number of variations on the theme. "Recommended practice" then was also to "export/import to compress extents". Of course, this is when backups were always cold backups and users logged off and went home at 5 PM.
There were a number of such papers between Cary's and the "Stop Defragmenting and Start Living" (SDSL) paper, but they all seem to have been forgotten - the latter is the one you always hear about. I would say that it became "recommended practice" to set pctincrease 0 the first time you seriously considered the alternative - especially the default of pctincrease 50!
This is similar to the "tuning revolution" discussion of a couple of years ago. Good ideas and sound logic rarely prevail immediately on merit alone. They have to marinate in slow growth praxis, then there is a sort of community sense of profound revelation when they finally become generally accepted. For the death of the BCHR and such as the holy grail of tuning, that was IOUG-A Live! 2002 (?2001?). For uniform extents, I think it was about 1997/1998.
Speaking of uniform extents... I anyone else annoyed at the way that ASSM does extent sizing - and can waste a *lot* of space in multi-datafile tablespaces with large objects? Its the best argument I've seen yet for monster datafiles ;-)
-Don Granaman
uniformly sized OraSaurus
> I originally wrote this in 1994 as an engagement summary document for a
> power company in Colorado. I don't have the exact date anymore.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 Boston
> - SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
> - Hotsos Symposium 2005: March 6-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Wolfgang Breitling
> Sent: Thursday, June 10, 2004 2:13 PM
> To: oracle-l_at_freelists.org
> Subject: RE: Stop defragmenting and start ...
>
> My copy of Cary's paper "Oracle7 Server Space Management - An Oracle
> Services
> Advanced Technologies Research Paper", which was my first encounter with
the
>
> concept of uniform extents - and a certain Cary Millsap - is dated
"Revision
>
> 1.4b (95/10/31)"
> I was immediately hooked and converted.
>
> Quoting Lex de Haan <lex.de.haan_at_naturaljoin.nl>:
>
> > Robyn,
> >
> > I quickly checked my courseware archives,
> > and for sure Cary Millsap talked about uniform extent sizes in 1996.
> > to be more precise: he probably talked about this even before 1996,
> > but my private collection does not contain any hard evidence :-)
> >
> > Kind regards,
> > Lex.
> >
> > ---------------------------------------------
> > visit my website at http://www.naturaljoin.nl
> > ---------------------------------------------
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Robyn
> > Sent: Thursday, June 10, 2004 16:02
> > To: oracle-l_at_freelists.org
> > Subject: Stop defragmenting and start ...
> >
> >
> > Hello,
> >
> > Can someone tell me when uniform extent sizing became a recommended
> > practice? The 'Stop defragmenting and Start Living' paper has a
copyright
> > date of 1998, and I remember implementing it on some databases back in
> > 1999, but I was wondering if it there were earlier references to this
> > approach.
> >
> > Robyn
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
>
>
> --
> regards
>
> Wolfgang Breitling,
> Oracle 7,8,8i,9i OCP DBA; Oaktable member
> Centrex Consulting Corporation
> www.centrexcc.com
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sun Jun 13 2004 - 23:19:01 CDT
 |
 |