Re: Magic number ( 5 * DB_BLOCK_SIZE )

A copy of this was sent to "Jonas M" <joao_tomas_at_hotmail.com> (if that email address didn't require changing) On Tue, 5 Jun 2001 12:22:20 +0100, you wrote:

>Hi
>
>While studying by Oracle Books , I've come accross this tip several times:
>
>you should use uniform extent sizes : multiples of 5 * DB_BLOCK_SIZE
>
>Why do they insist on this value ? I thought the ideal situation would be to
>fit all my data ( from a regular table ) in just one extent...
>isn't that so ? Is it better to have several extents , and get the data
>spread among them ?
>

well, the 5*db_block_size is generally the minimum or what we round off to -- its not the suggested size, its a multiple of that in a dictionary managed tablespace.

it is not optimal to have the data in one extent.

If we access the data via an index, the index points to a file/block/slot on the block. If the extent size was 1 block -- that would work just as well if the extent size was 2gig (max extent size).

If we full scan, as long as the extents are multiples of your multi-block read count, it won't matter if you have 1, 100, or more extents. In a multi-user system, with disk arrays, with volume managers, with raid, etc -- the chance that the disk head will be positioned exactly where you need it to pick up and do your next read is approximately 0%.

If you have everything in one extent, you by defintion have everything in one file, you may be missing out on opportunities to distribute IO.

the bottom line -- a single extent is not something to strive for, not something to achieve. With the exception of tiny tables -- it is not something you should be concerned with.

>
>Thanx in advance
>
>
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp