Re: Help: Settle an argument regarding EXTENTS

Allan,

Just saw that it's the Sys Admins not wanting to use striping, is their a reason for this (at the hardware level)? If given the chance, I would use the striping to speed the application up, at the risk of resilience. Also, is the application I/O intensive, or could you get away with RAID 5 for resilience.

Andrew

Allan Kelly wrote:

> 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 - 19:39:01 CST