Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: LOCALLY MANAGED EXTENT PERFORMANCE


From: jungwolf <>
Date: Tue, 26 Apr 2005 16:26:26 -0500
Message-ID: <>

Pardon me for taking things out of order. I think the following gets to the main issue we're discussing.
On 4/26/05, Tim Gorman <> wrote:
> Oh, we measure free space percentage as well -- that's just a lot easier =
> do and not the main topic of the thread.


> We monitor both percentage of free space within tablespaces as well as
> ability to perform "N" extensions. After all, either one can bite you ve=
> very quickly, and usually at night or on weekends...

I guess that's the question I have. With either one you are watching the number dwindle to 0 with set warning and alert thresholds. To me they are two different ways to calculate the answer to the same question: "How much space left?". Why look at extents for this information when percentage (or bytes) is much easier to calculate and handle?

I guess I'm just not seeing what you gain by looking at extents.=20 There will be special circumstances such as when extent sizes are large compared to space, but otherwise...

> Just because features exist doesn't make them good ideas for everything..=

True, true. My answer to the interview question "tell me about a project that failed" involves a horrific misapplication of partitioning...

> If you use autoextension, what will you end up with in terms of datafile
> sizes? Obviously, a mish-mash of odd-sized files. With some really real=
> big files that are really really busy.

That's a good, rich question that leads into many areas. Do you have a set max datafile size? How are you dividing objects into tablespaces? (By size, type, access, application organization, partition scheme, dartboard throws, any/all/none?) Are you partitioning? Do partitions get their own tablespaces? Do you have pruning or archiving requirements? What is your SLA on restore times?  Are you keeping track of extent numbers (under 4k)? How solid are the sizing and growth estimates? Oracle 9.2 limits you to 32GB files, 10g seems to have the sky as the limit.

Whew! So, I (we?) have gone far afield. Basically I mean to say that of course one doesn't throw ATTM at an object and set autoextend on files and walk away. For brevity I'm snipping a lot of good points you raise that need to be addressed when designing DB policies.

> > Finally, you said:
> >> Think it through.
> > I'd love to hear how you come to a different conclusion.
> File management within file-systems is no different than segment manageme=
> within tablespaces. Uniform-sized objects are easier to manage over time
> than odd-sized objects. Plain and simple. That's what I meant by "think=
> through". The discussion about backup/restore (above) is one such
> consideration...

Wellll, I only partially agree. Uniform-sized objects are easier to manage when _I_ have to manage them, but the various tools Oracle has added to the RDBMS have taken a lot of the sweatwork out of managing the well-behaved objects. LMT, ATTM, autoextend; a halfway decent sizing document; and some monitoring scripts can go a long way towards taming the small and medium stuff.

That leaves the large stuff, and that's where I agree it is easier to manage them when they are mainly uniform.


Received on Tue Apr 26 2005 - 17:30:42 CDT

Original text of this message