Re: Help: Settle an argument regarding EXTENTS

Hi Allan,

Being the physical and logical designer for the last system I worked on, and also being an Oracle DBA for the last 7 years, here is my 0.02.

I was very adamant about having the disk space allocated to the DBA department on day one, and we got round this by allocating the UNIX file systems by System / Application and Database. OK, the Sys Admins did encroach on the Database space, but I had the allocation signed off and I could get the space back when I needed it (in 4 months for you).

Now to reduce database build times, (I had 12 of these things to build in 12 countries), I used Auto Extending Datafiles, with the initial DATAFILE size set at about 3 months or 1/4 size required (I needed 12 months), and then I just created the tables and indexes using default tablespace storage parameters. (I had previously sized the objects into categories of Small, Medium, Large, XLarge and XXLarge, with XXLarge being allocated out at 25Mb's Initial and Next. The categories of Small, Medium, Large and XLarge were sized at 40Kb, 200Kb, 1Mb, and 5Mb. What I did was to place each object in a category based upon the anticipated size in 12 months (6 for you), and allowed the object to have 5 extents at this time. Obviously, this does not work on the XXLarge and in this category, I had the largest object (Fact Table) at 191 extents anticipated or 47Gb. I used unlimited max extents BTW on all objects.)

Why.... because we were backing this database up nightly, we did not want to back up unused, but allocated, disk space. OK, I know there is hardware compression on tape hardware nowadays, and Oracle's empty, unused blocks, are full of zeroes that compress wonderfully, but why bother to back this space up. Also, as I indicated above, the Sys Admins wanted to borrow the disk space, and the client would wonder where the space had gone to if there was a request after 6 months for more spindles.

How.... did we manage this, well the database monitoring tool deployed new about the two important design considerations, the 5 extents rule (what we used for Small, Medium, Large and XLarge) and Auto Extending datafiles. When an object in the Small, Medium, Large and XLarge category got to SIX extents, a warning was raised and sent to the management console, and when the object got to NINE extents, an Error was raised and sent to the management console. This allowed the DBA, to review the object against anticipated sizing and to return to the Application Development Manager, asking what had gone wrong (if required). The product had to be aware of Auto Extending Datafiles, because the old rule of 'there's less than 5% Free Space in this tablespace' no longer applied because we new that the File System had space for us to grow into, so we had to take datafile allocated size from the sys.filext$ table and not dba_data_files.

I suppose one other thing with allocating small, and allowing to grow (visably) to maximum size, is that it's not a surprise in 6 months time. OK, the database monitoring tool can be configured to track the HWM in the table (and indexes), and to alert when it gets to 95% utilization, but most DBA's want to know what is happening physically.

The final comment, (PLEASE NO FLAMES HERE), is that you say the DBA is a contractor (I prefer consultant for myself), and the contractor is working on behalf of yourself (the client). Therefore a good contractor will always work with the client to ensure that what's delivered is what the Client wants and can understand. Contractors leave at the end of the assignment leaving the work (aka SYSTEM) to the permanent staff who have to fix problems at 3:00am, 1 year after the system is live and the contractor is (hopefully) on to the next site.

Rgds
Andrew

Allan Kelly 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
Received on Mon Mar 29 1999 - 18:29:28 CST