Re: block sizes

Thank you Daniel (and Howard), that's what I figured, but wanted to get a different opinion from the board. I just happened to come across this article, and it struck me as being outdated, yet it was supposedly fairly recently posted (not sure exactly when though).

Cheers,

Brick

damorgan wrote:
>
> No!
>
> You'd look pretty darn stupid wasting 160K on a very small table. And even
> worse using 160M on a table holding hundreds of gigabytes.
>
> Daniel Morgan
>
> Bricklen wrote:
>
> > 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 Mon Feb 18 2002 - 12:25:27 CST