Re: Help: Settle an argument regarding EXTENTS

Andrew,
 Thanks for the in-depth reply. That helps greatly. My problem is that we are running on HP-UX 10.2 and our Unix administrators are not utilizing striping. Therefore (for example) I have been allocated 15 logical volumes with two datafiles on each for the BIG table mentioned. Each volume is 4 GB in size mapped directly to a physical disk that is 4 GB in size in an EMC tower.
 The best I remember, extents can't extend across datafiles. Is that correct? Under those circumstances (assuming I remember that correctly), we are limited to 4 GB per extent if we use one datafile per logical volume.  As far as the "FLAMES", I don't do those. But you have hit the nail on the head. I am a permanent employee here and the "consultant DBA" (I worked consulting for 8 years before sttling here) will disappear May 31. Our manager over this project wants to understand but is simply a "pointy-headed-boss" (according to Dilbert) -- he's OK, he just doesn't understand client-server. I have to justify my position somehow. That's why I'm asking for pro's and con's to get both sides.

Allan

-----Original Message-----
From: Andrew Babb [mailto:andrewb_at_mail.com] Sent: Monday, March 29, 1999 7:29 pm
To: Allan Kelly
Subject: 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:58:11 CST