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

Ideally, you should have INITIAL=NEXT and PCTINCREASE=0. Actually, the ideal situation is to use LMTs and start living! But if you want to avoid fragmentation of free space from being the issue that you are experiencing, then set INITIAL=NEXT and PCTINCREASE=0. This doesn't stop anyone from manually specifying different values one a CREATE <segment_type> command though.

HTH,
Brian

Don Seiler wrote:
>
> 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

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"