Re: logical storage philosophy?

There is no single answer to your question - otherwise Oracle would not have made the parameters available.

In fact, things have changed between Oracle 6, Oracle7 and Oracle8 as well as with changes in disk storage capability and tablespace limits.

Once upon a time it (early Oracle7) was very important to minimize the number of extents - some of my benchmarks indicated serious performance knee at around 16 extents. This disappeared around version 7.2

I now use extents as a measure of growth - plan on a reasonable number of extents to start (if I can make it in one - fine, but I don't bust myself trying to accomplish that. I still try to keep it under 10 extents, unless things like partitioning or disk requirements force an increase). However, I do like to plan on 1 extent per 'basic growth monitor time' - for example, if the system plans to grow by twice the original size in 2 years, I may set my mental growth monitor to 3 months. My next extent you be set to 1/8 of the initial size (100% growth over 8 three month periods) and then I can flip off a quick report showing whether I am under/over/on th growth target.

Prior to Oracle8/8i, max extents was a hard limit. Now it's a convenient warning system - set to an arbitrary but high number relatrive to where I should be, (say 100 extents when I'm expecting 20 under normal growth) and if the user complains I can quickly lift that limit and know I have run into a serious unplanned issue I have to deal with.

If I plan on monitoring the system closely or using automatic events, I usually set the percnt increase to 0. I find this one mostly useful when I have lots of available disk (so exponential growth is not serious) and the user has defined a business case showing growth over time.

Mind you, the above is 'my' preference - these parameters have caused a lot of ongoing, and sometimes amusing, debate over the years. It really comes down to experience.

Hope this helps
/Hans

Fred A G wrote:

> Bon jour, tout le monde!
>
> I am searching through Metalink and other sites for a source on storage
> parameters policies, but nothing much comes up. Aren't there any around?
> I am also interested to hear about the philosophies used by the database
> management people here, when it comes to Oracle logical storage
> parameters (like initial, next, max extent, pct increase, and so on).
>
> For instance, what behaviour do you expect for initial and additional
> extents. Aim for one big (enough) inital and only a few additional per
> year or at most per month? Is the max extent limit (e.g. 121) a
> safeguard and for normal operation it should be near unreachable, or is
> unlimited the way to go?
>
> Regards
> /Fad
Received on Mon May 14 2001 - 02:07:02 CDT