Re: Are multiple extents still a problem with LMT?

On Apr 4, 6:24 am, "Andy Kent" <andykent.bristol1..._at_virgin.net> wrote:
> I'm going to migrate the tablespaces of an old 8i/w2k database I've
> inherited from DMT to LMT as a first shot in addressing seriously
> degraded performance in queries that have to go to disk.
>
> I need to decide whether to allow for table rebuilding in the downtime
> I ask for. The whole database is under a Gig but everything has been
> created with 64k blocks and a couple of tables have run to several
> thousand extents. Will LMT eliminate most of the effects of extent
> fragmentation or are undersized table extents going to create a
> similar performance hit? The box only has the one disk so things are
> always going to be messy to some degree.
>
> Andy

You have only one disk? Period?? Your performance issues won't be solved by having larger extents for these objects or by rebuilding said objects. Even tuning the SQL statements won't likely have much effect, but such a task would be preferable to the exercise in futility you've suggested. Create your tablespaces with uniform extent sizes (whicih you've done already in a DMT configuration so 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). Your performance problems stem from this 'one-disk wonder' you're using, not from the number of extents. Since you have only one disk I can also expect you have far less RAM than would be advisable, yet another performance bottleneck as many of the transactions are likely swapping out to disk instead of processing in memory.

The prudent suggestion would be to seriously consider what you want to do with this system and configure the memory and disk resources accordingly, spending whatever resources are available to increase both. Then go about your rebuild/reorganization if you feel it's necessary. Doing such tasks now won't help you as you've already stretched the available resources to their limits.

David Fitzjarrell Received on Wed Apr 04 2007 - 08:02:54 CDT