!! Please do not post Off Topic to this List !!
Don,
Just to confirm a point that I think that you are making:
Each tablespace should only have one extent size in it?
Once you get too many extents, you move the object up to the next-size
tablespace?
Thanks,
Cherie
"Don Granaman"
<granaman@home To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
.com> cc:
Sent by: Subject: Re: Automanagement of extent
sizing
[EMAIL PROTECTED]
om
09/14/01 04:30
AM
Please respond
to ORACLE-L
!! Please do not post Off Topic to this List !!
I've been using LMTs in production systems since 1999 with no problems.
I've
been doing uniform extents for (almost) everything (enforced through
controlling
the DDL) since 1990 - and wouldn't have it any other way.
My strategy is simple - assign objects to tablespaces bases on three
criteria.
1) How big are they (or will they get)
2) What is the nature of the object (Table? Index? Read-only?
Truncate/Reload, etc.)
3) Separate objects that are likely to contend with each other into
different
tablespaces.
Pretty basic stuff really.
Other basic "best practices", in my opinion, are:
* A few different extent sizes are all you need - for the entire database
(except SYSTEM). The most common example is to use only 128K, 4M, and 128M
for
anything (except perhaps rollback segments and temp - which may each have
their
own "uniform extent size" and, of course, SYSTEM). These aren't carved in
stone, but are good choices.
* Size for the future, not the present. You really don't want to have to
move
those monster objects from a tablespace of one extent size to one of the
next
larger extent size very often.
* A few different datafile sizes are all you really need - perhaps 3, 4 or
maybe
5. Extend this extent sizing policy a bit further - to datafiles. Unless
you
have a really compelling reason for gargantuan datafiles, don't use them!
For
example, 15 * 2 GB datafiles for a 30 GB tablespace is preferred over 3 *
10 GB
datafiles or 1 * 30 GB datafile. The additional checkpoint overhead for
additional datafiles is not usually significant and the smallest unit of
backup
and recovery is a datafile. (Lets not cloud the issue with discussions of
incremental backups, etc.) Would you rather have to restore and recover a
2 GB
datafile or a 10 GB datafile? The former I would think.
I know that autoextend is popular, but consider fragmentation at the
filesystem
level also. Consider this: You create N * datafiles on a brand new
filesystem,
using virgin disks and load them up with data. A month later, all of these
have
autoextended 5 times each (assume - in round robin fashion). How many
different
contiguous file chunks are now on this filesystem? Answer: 5 * N. Each
file
would have 5 discontiguous "extents". Is this a problem? Probably not
much of
one, but "it depends"... I prefer to just create datafiles out of a small
set
of sizes and add a new datafile when a tablespace needs more space. The
other
advantage is that moving files about is a lot simpler - you only have a few
files sizes and a few "hole sizes" ("hole size" = size of space vacated by
a
moved datafile).
If you are using raw devices, remember two things.
1) A single raw volume can contain at most one datafile - it never gets
fragmented the way a filesystem can.
2) You CAN resize or autoextend a datafile on a raw volume - up to the size
of
the volume.
The paper so frequently mentioned "How to Stop Defragging and Start Living"
is a
good one and the latest in the line of evolution, but it certainly isn't
the
first one or the only one. This philosophy has been around, but perhaps
not
well known or commonly adopted, for well over a decade. Popular published
papers on this topic go back to at least the mid-1990s. One classic is
Cary
Millsap's "Space - The Final Frontier". For many years, at every major
conference there were at least a couple of presentations that were some
variation on this same theme. Don't be concerned too much about uniform
extents. LMTs are relatively new, but the practice is very well
established and
thoroughly proven.
I will add one last thing though. There is a potential "gotcha". If you
have
an object that is routinely cleaned out and reloaded (say via drop|truncate
and
recreate|reload) in batch, you may not want 1000+ extents. When in doubt
about
where to put something, based on its size and probable number of extents,
it is
usually better to "round up".
-Don Granaman
[OraSaurus - Honk if you remember UFI!]
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 13, 2001 3:15 PM
> !! Please do not post Off Topic to this List !!
>
>
> How many people are using the new 8i and 9i feature for automatic space
> management? I'm specifically interested in the management of extent
> sizing.
>
>
> For those of you that are using it, what has your experience been with
it?
> What have you learned? What are the pros and cons? What sort of
> strategy have you used to implement it?
>
>
> We are considering using this feature for a new project. Is this a good
> idea? What would be the "right" way to implement this?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Don Granaman
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
