Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sqllder hangs at 99-100% CPU on select of fet$

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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 11 Sep 2003 06:37:12 +1000
Message-Id: <3f5f8bee$0$28119$afc38c87@news.optusnet.com.au>


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.
>

Yes, you should have INITIAL=NEXT. We've come a long way since version 7. People used to do things like 'big initial then small nexts' quite a lot back then, because I don't think we'd really nailed down that different extent sizes yield bad fragmentation. Or at least, the message was slow getting out.

As Brian also says, the other thing people used to do was set PCTINCREASE= to something other than zero. A particularly permicious thing was to set it to 1. There were all sorts of justifications for that at the time, but they were all based on extremely bad theory, and PCTINCREASE should always and universally be zero.

I would indeed round up to 64MB based on your statistics, though that would take you right to the hundreds of extents from the word go. If you're going to be loading into this table often, I think I'd prefer the 256MB extents.

Regards
HJR Received on Wed Sep 10 2003 - 15:37:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US