Re: block sizes

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 - 11:57:09 CST