Re: block sizes

Thank you Howard, I was following the same line of thinking, and the reason I posed the question was in regards to locally managed tablespaces, and some sizing issues, etc.

As a matter of fact, it was a link on your site that led me to that article.

Cheers,

Bricklen

"Howard J. Rogers" wrote:
>
> As with all simplifications, this one has costs (not so sure, for example,
> that I would happily kiss goodbye to 160K of space for an 8-row table, such
> as the States of Australia lookup table). But this article's general thrust
> is not too far off the mark. In the days of humungous and cheap hard disks,
> extent management doesn't need to be the finickity time-waster it once was.
> Locally managed tablespaces lend themselves to this sort of policy very
> nicely, too... so Oracle's general thrust is in this sort of direction.
>
> I myself would prefer something between the 160K and the 5M extent... and
> definitely something between 5 and 160M.
>
> I like the idea of 5-block multiples, though.
>
> Regards
> HJR
> --
> ----------------------------------------------
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
> "Bricklen" <bricklen_at_shaw.ca> wrote in message
> news:3C6E7388.3AFDC21B_at_shaw.ca...
> > Does the following excerpt still hold true?
> >
> > Taken from http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
> >
> > 2.1.3 The Extents Size for all Data Tablespaces Should be one of 160K,
> > 5120K, or 160M
> > These three extent sizes are all that you will ever need for tables and
> > indexes. Using more than these three extent sizes will
> > not significantly improve performance or space utilization but it will
> > complicate administration and potentially induce
> > fragmentation.
> > The smallest extent size, 160K, is large enough to make scanning
> > efficient. It is small enough that disk wastage is
> > insignificant for even small tables. One might think that 160K is a lot
> > of disk space to waste on segments that only have a
> > very small amount of data. However, in practice, the labor required to
> > manage smaller extents is not worth the small savings
> > in disk cost. For example, if a database has ten thousand tiny tables
> > with an extent size of 160K, then a maximum of 1.6G of
> > disk will be wasted. The cost of this much disk will be swamped by the
> > labor cost of dealing with fragmentation and
> > inefficient scans for the lifetime of the database. Also, if the segment
> > only uses a few blocks, the remaining blocks will be
> > kept above the ' high water mark' of the segment and therefore will not
> > hurt performance during scans. Unless your database
> > has many tens of thousands of very small segments, don' t use an extent
> > size smaller than 160K.
> >
> > The medium extent size, 5120K, is large enough to handle all but the
> > very largest segments of large databases. <snip>
> > As a rule, this extent size will mostly be used for very large segments
> > that cannot be partitioned either because partitioning is not available,
> > or because the segment type does not allow partitioning.
> >
> > When you create a new segment, you need to have a very rough estimate of
> > the ultimate size of the segment in order to assign
> > it to a tablespace with the appropriate extent size. Follow these three
> > simple rules when assigning a segment to a tablespace.
> > 1) Segments smaller than 160M should be placed in 160K extent
> > tablespaces.
> > 2) Segments between 160M and 5120M should be placed in 5120K extent
> > tablespaces.
> > 3) Segments larger than 5120M should be placed in 160M extent
> > tablespaces.
> > These extent sizes are chosen to be a multiple of five blocks since
> > Oracle7 will round all extent sizes to a multiple of five
> > blocks. In Oracle8 extents won' t be rounded to a multiple of five
> > blocks if they are a multiple of the minimum extent size
> > for the tablespace. For Oracle8 databases, choosing the following extent
> > sizes is a little simpler.
> > 1) Segments smaller than 128M should be placed in 128K extent
> > tablespaces.
> > 2) Segments between 128M and 4G should be placed in 4M extent
> > tablespaces.
> > 3) Segments larger than 4G should be placed in 128M extent tablespaces
Received on Sat Feb 16 2002 - 18:14:54 CST