Re: block sizes

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