Re: sqllder hangs at 99-100% CPU on select of fet$

Should I always have the initial and next extent sizes be the same?

Like I said, I inherited this database and on this table in particular, the Initial Extent is 2,097,152,000 and the Next Extent is 41,943,040. I did the math from dba_extents and this table has the one initial extent at the aforementioned size, and 261 of the next extents, for a total of 13,044,285,440 bytes. The database is 4K (4096) blocks, so that would make the "ideal" size 13,044,285,440 / 249 = 53,386,689. I assume I should round that up to the nearest power of 2 to 64MB. Should I set that for both the initial and next?

Don.

On Wed, 10 Sep 2003 11:04:08 +1000, Howard J. Rogers wrote:

> No such thing, really. The way I would do it is: start with the assumption
> that on a 2K block system, 121 extents is ideal. On a 4K block database, 249
> extents. On an 8K block system, 505 is the magic number (there are technical
> reasons for those numbers being good, though it won't kill you to exceed
> them moderately).
>
> Now measure the complete existing size of your table by looking in
> dba_extents where segment_name='BLAH', and adding up the BYTES for all the
> extents listed.
>
> Divide the total size of the table (not forgetting to add in some extra to
> allow for a reasonable amount of future growth) by the ideal number of
> extents, and you have your ideal extent size. Sort of.
>
> On an 8K block system, with a 500MB table, that would mean (worst case)
> extent sizes of about 1M each. I'd probably go 8M myself and have done with
> it. If the table was 16GB, worst case would be 32MB extents... I'd probably
> go 64MB.
>
> It wouldn't hurt you to stick to the extent sizes that Oracle 8i and 9i use
> when 'autoallocating' locally managed tablespaces: 64K, 1M, 8M, 64M and
> 256M. Just pick from that list the size that gets you a number of extents
> under the 'ideal' numbers of extents I mentioned above. If everything inside
> a dictionary-managed tablespace comes in one or other of those 5 sizes, then
> you won't totally eliminate the possibility of fragmentation, but you will
> certainly have minimised it.
>
> Regards
> HJR
Received on Wed Sep 10 2003 - 09:20:45 CDT