Re: Automanagement of extent sizing

!! Please do not post Off Topic to this List !!

Inline answers...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, September 14, 2001 7:45 AM

> !! Please do not post Off Topic to this List !!
>
>
> Don,
>
> Just to confirm a point that I think that you are making:
>
> Each tablespace should only have one extent size in it?

Yes, in any given tablespace there is ONLY one extent size. Initial = next for everything and every (initial extent size) = (every other initial extent size) - for everything in the tablespace. (Except for SYSTEM).

> Once you get too many extents, you move the object up to the next-size
> tablespace?

If you must. Better in my opinion is to size for growth. For example, if you have a table that is currently 200M, but it will grow to 20G in two years, prefer initially putting it into a tablespace appropriate for a 20 GB table. The percentage of "wasted space" will be fairly high initially, but will decrease as it grows - and you won't have to move it later.

Consider that, on average, one half of one extent will be as yet unused - "wasted". As the number of extents grows, that fixed amount of space gets to be a smaller percentage of the total. For example, a uniform extent policy:

A table consists of N extents
Each and every extent is of size M
Average "wasted space" = M/2
  (If anyone wants to extend this line of reasoning for parallel loads, etc. - feel free!)
Total space in N extents = N*M
The ratio of wasted space to total space is (M/2)/(N*M) = M/[2(M*N)] = 1/(2N)  {Sanity check!: 4 extents, 0.5 extent unused -> 1/8 of space is "wasted". 1/(2*4) = 1/8. It checks.}
To convert to a percentage, multiply by 100. => Average percentage of total space for table extents that is "wasted space" W = 100*[1/(2N)] = 50/N
As the value of N (the number of extents) increases, the value of W (the percentage of wasted space) decreases.

[Note: This, of course, does not consider free space within blocks, ILT space, and all the other geeky Oracle stuff - nor should it. All that will be present whether you have one extent or a thousand. It is a separate issue entirely.]

> Thanks,
>
> Cherie

You are quite welcome!

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Don Granaman
  INET: granaman_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).