Re: Are multiple extents still a problem with LMT?

> I can't understand why 'extent fragmentation' is a problem as you do not
> have any odd-sized extrents which cannot be reused by other objects

You seem to be misunderstanding how extent allocation works. If two tables are grabbing extents alternately then their extents will be interleaved and there is no way you can avoid having a whole load of extents of the default size for the table or tablespace. Even if extents are contiguous 8i doesn't automatically figure that out. You can reduce the problem with COALESCE but that will make no difference where interleaving has happened.

Having too many extents creates two distinct problems: 1. Access to the dictionary increases with the number of extents and the dictionary itself may fragment if the number is large. This can be a considerable overhead (especially in sub-optimal situations where the system and data tablespaces are on the same disk). 2. Large queries and especially full scans are compromised because the data blocks do not occupy contiguous space so seek time is increased. However where datafiles use filesystem space the filesystem is likely to add its own fragmentation overhead so making everything [logically] contiguous within a datafile may be a lost cause.

Now, I know that moving from DMT to LMT sorts out 1. What I'm trying to establish is whether table rebuild should be added to the equation in order to mitigate 2.

Rest assured I am also tuning SQL (ANALYZE had never been run!), indexes, SGA and undo/ redo.

Andy Received on Wed Apr 04 2007 - 08:30:09 CDT