Re: Are multiple extents still a problem with LMT?

Comments embedded.
On Apr 4, 8:30 am, "Andy Kent" <andykent.bristol1..._at_virgin.net> wrote:
> > 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.

False. I understand quite well 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.

'Interleaved' extents don't cause this problem, as contiguous extents would create the same situation. Problems are created when two objects with differing extent sizes create extents in the same tablespace; odds are the extent sizes are not multiples of each other and thus cause fragmentation as one object cannot use what the other object has left free when most of the tablespace has been consumed. Not so with uniform extent sizing, as any object in the tablespace can reuse any space freed when any other object is dropped/truncated/ reorganised.

> Even if
> extents are contiguous 8i doesn't automatically figure that out.

And why should it?

> You
> can reduce the problem with COALESCE but that will make no difference
> where interleaving has happened.

Again, it won't matter as the number of extents (unless this number is extremely large) won't affect performance to a noticeable degree.

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

Yes, which is WHY you don't use dictionary-managed tablespaces any longer. Moving this to 9iR2 or 10g will correct that issue.

> 2. Large queries and especially full scans are compromised because the
> data blocks do not occupy contiguous space so seek time is increased.

No, they aren't. What compromises large queries is insufficient sort space and insufficient RAM as swapping occurs and creates performance issues. Also affecting large queries is a lack of usable indexes which forces full table scans and increases the memory required to process the request which then goes back to the second item I mentioned: insufficient RAM. The seek time is minimal compared to the time required to swap data in and out of memory.

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

I'll agree with that statement.

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

There is nothing to 'mitigate' with 2 unless you have thousands of extents for a given object. This is what you fail to understand.

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

David Fitzjarrell Received on Wed Apr 04 2007 - 09:44:29 CDT