Re: Help: Settle an argument regarding EXTENTS

Hi Allan,

You are correct about extents not being able to spread physical data files, but I am surprised that the EMC tower does not offer some form of raid.

I suppose one reason that he (the DBA) maybe wanting to allocate the datafile portion is that the headaches for yourself will be less. (You can't use AutoExtending Datafiles), so allocating the physical data files (15*4Gb) gives you the physical space requirement for the fact table. However, I question why you want to create the table with minextents set to 15 with initial and next at 4Gb on day one.

Also, don;t forget that a datafiles takes 1 Oracle Block for the file header, so if you say 'create tablespace ... datafile ... size 4,000,000Kb' you only get 3,999,996Kb (assuming 4K Blocks) for yourself. This is one of the reasons I create my datafiles in Kb's instead of Mb's or Gb's.

Suggestion that might help both ways....

1. Create the entire tablespace or most of it.

CREATE TABLESPACE BIG

  DATAFILE '/u01/oradata/.../big_01' size 4000004k
         , '/u02/oradata/.../big_02' size 4000004k
         , '/u03/oradata/.../big_02' size 4000004k
         , ....
         , '/u13/oradata/.../big_13' size 4000004k
         , '/u14/oradata/.../big_14' size 4000004k
         , '/u15/oradata/.../big_15' size 4000004k
  DEFAULT STORAGE ( INITIAL 1000000k
                    NEXT    1000000k
                    PCTINCREASE 0
                    MAXEXTENTS UNLIMITED );

2. Create the table using default allocation

CREATE TABLE BIG
  ( ... )
  TABLESPACE BIG; This way you have the both of best worlds, not to much maintance on the tablespace, and the ability to see the object grow. Also, you could use human partitioning (if the application needs it) via the Alter Table Allocate Extent Clause.

ALTER TABLE BIG
  ALLOCATE EXTENT ( DATAFILE '/u02/oradata/.../big_02' );

Another reason for wanting smaller than maximum extent sizes.

Rgds
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:35:29 CST