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

On Thu, 11 Sep 2003 06:37:12 +1000, Howard J. Rogers wrote:

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

Yes this is how the database was designed long before I had even come on board.

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

Our PCTINCREASE parameters are all 0. The only benefit to a non-zero value is having SMON automatically coalesce tablespace. I thought this was good, but that was because I thought coalesce also meant defragment. This appears to not be the case so it didn't seem worth it to change.

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

We went with 64MB, but also changed the table architecture and split up the data into multiple tables, differing by months. Like "tablename_200309". We have 3 other areas of the application that already do this to allow for just dropping the table to delete the older data every month.

Thanks to you and Brian for the info. Received on Fri Sep 12 2003 - 10:02:24 CDT