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

Does 7.3.4 have the minimum extent clause for a tablespace? 8.0 does. That way you can at least have some sort of order in your DMT tablespaces.

Tanel.

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:3F5F38C5.A25BFB4D_at_remove_spam.peasland.com...
> 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"
Received on Wed Sep 10 2003 - 14:52:32 CDT