Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Table and index Extent Sizing ?

Re: Table and index Extent Sizing ?

From: Howard J. Rogers <howardjr_at_www.com>
Date: Tue, 27 Feb 2001 02:39:39 +1100
Message-ID: <3a9a78e5@news.iprimus.com.au>

"yakub" <yakub_at_erols.com> wrote in message news:3A9A4C0B.9C1BBFDA_at_erols.com...
> I have updated an algorythm that computes the initial and next extents
> with respect to space management of data and indexes . That is, I
> compute for the Space Available for Data (SAD) based on average
> rowsize, PctFree and blocksize.
>
> Question 1: Does anyone have an algorthym

Wasn't he the guy who lost the presidential election?? ;-)

>that can be used to compute
> the initial and next extents sizes?

There's been a lot of talk on this group lately about a really neat algorithm that says, putting it briefly, create a few tablespaces with standard extent sizes (I seem to recall 160K, 1M and 128M being mentioned, but I can't remember for sure), and house your tables in the appropriate one according to their growth and sizing habits. Whatever the details, such an approach in principle has much to recommend it, guaranteeing as it does zero fragmentation within a tablespace. The very last thing I'd be trying to do is attempting to make 'intelligent' choices about extent sizes on a segment-by-segment basis.

> Question 2: Should one also strive to get all of the data into one
> extent?

Ideally, 1 extent would make life easy, but no -there's absolutely no requirement for that, particularly on tables which are accessed randomly. Tables which are subject to frequent full tablescans may benefit from being housed in a single extent, but I doubt you'd notice the difference. Do try not to have more than half a dozen extents per segment, though. And there are good performance reasons for abiding by what used to be absolute Oracle rules in terms of the number of extents... on 2K block systems, for example, it used to be the case that you simply could not have more than 121 extents. Now, the number allowed is actually unlimited -but 121 is still a good maximum to manage by. 4K, it was 255. 8K was 505 (if I recall correctly). So the allowance is generous.

Regards
HJR
>
> Please explain.
> Thanks,
> Yakub
>
Received on Mon Feb 26 2001 - 09:39:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US