Re: Help: Settle an argument regarding EXTENTS

In article <ILTL2.922$Ek.376895_at_news1.mia>,   "Allan Kelly" <ac_kelly_at_bellsouth.net> wrote:
> We currently have a development project in progress. The whole application
> revolves around an Oracle 7.3.4 database. The users have told us that they
> anticipate having roughly 100 GB of data that will have to be archived off
> regularly after reaching this 100 GB high-water mark after 6 months of use.
> There are approximately 120 tables in the main schema. One of which, will
> hold approximately 57-60 GB of data when the high-water mark is reached and
> weekly archival begins (at the six month mark).
>
> My problem is: we have a contractor DBA hired to manage the database and who
> stubbornly insists on allocating the complete storage size each table will
> occupy after six months in the INITIAL EXTENT. Example: for the main table
> described above, he wants to build the table with INITIAL set to 60 GB and
> NEXT set to 1.5 GB. He insists on building the other tables the same way.
>
> Knowing Oracle as I do, I find this more than a little disturbing. However,
> this DBA insists that "that's the way he's always built tables", with no
> other explanation or facts to back up his assertion.
>
> Trying to keep an open mind here, I am asking: Is there any benefit to this
> scheme he proposes? Let me hear your pro's and con's, please.
>
> Allan
>

The number and sizing of extents should be determined for each application to database level relationship. Hard and fast rules are generally wrong when applied to environments other than the one there were developed upon.

Pre-allocating full size initial extents has several advantages to the DBA and database. For one it means that the recognized necessary disk has to be available day one. It also means that the space will not be taken up by other OS level applications before the tablespaces need to be expanded to use the space. But it is not the only correct way to allocate objects.

Properly sized extents have only a small, generally unnoticable, determinal effect on performance for tables. I have seen performance problems that Oracle support said were related to too many extents existing at the database level. The internal operations that read and updated the free space extents, fet$, and user object extents, uet$, can be adversly affected by having a large number of extents at the database level. I have not seen this since before 7.2. A well selected set of standard extent sizes for initial and next will probably be a better long term plan.

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 30 1999 - 09:19:45 CST