Re: LOCALLY MANAGED EXTENT PERFORMANCE

> 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 <tim_at_evdbt.com> wrote:
>> Oh, we measure free space percentage as well -- that's just a lot easier to
>> 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 very
>> 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...

Because only using one threshold or the other does not deal with enough failure scenarios.

There are situations where one more extent allocation will push my free-space percentage from 75% to over 100%.

There are situations where adding five more extents to a segment won't run out of space, but we're close enough to "full" (i.e. 95% full) that it would be prudent to add another datafile.

I don't want to choose which of these error conditions are prevented automatically. I want to monitor both thresholds and add automatically so I can get my (much-needed!) beauty sleep. It just so happens that one of those thresholds is really easy to monitor and report on, and the other is devilishly difficult to calculate efficiently.

And autoallocate pushes that latter problem from "devilishly difficult" off the edge into "hold your nose and take a guess", which was my original argument in this thread...

>
>> 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 really
>> 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.

I try to avoid "rules of thumb" (ROT), but we all have them anyway. Forgive me if I don't quote numbers, but instead describe the reasoning. From that, you can pick your own numbers, I hope...

Since the most important thing that datafile size really impacts is the speed of restore from backup, I want to know something about the application's "mean-time-to-repair" (MTTR) requirements before deciding on a maximum datafile size. The tighter the MTTR, the more likely I'll opt for smaller (i.e. 4G, 8G) max datafile sizes. The more copious and capable the backup media, the larger the max datafile size.

But choose a max file size and stick to it. It can be changed if needed, but it shouldn't be necessary often. The goal is a small number of distinct file sizes, with "small number" being something like 3, 4, 5 distinct sizes, ideally.

If it's a 32-bit platform of any kind, I still hold to 2G as the max datafile size, notwithstanding enhancements to permit larger files. Too many bugs center around exceeding the 2G barrier on 32-bit platforms for me to risk one wink of sleep on it...

--
http://www.freelists.org/webpage/oracle-l