Re: Migrate from DMT to LMT when fet$ and uet$ counts > 700,000

Charles wrote:

> All,
>
> I have a 1.8TB database with all Dictionary Managed Tablespaces.
>
> It's a very active SAP Business Warehouse application.
>
> We've sort of reached 'critical mass' with the fragmentation of it,
> and now SMON coalescing is so slow that "Disk Space Transaction" locks
> are hanging up the entire db.
>
> I've added events to disable SMON coalescing while we deal with this.
>
> Oracle is telling me I must run tablespace coalescing at 'quiet' times
> prior to migrating the tablespace to Locally Managed Tablespaces.
>
> I want to migrate them now to eliminate the contention on the fet$ and
> uet$ tables in the SYSTEM tablespace.
>
> I await Oracle's technical reasons why I cannot migrate now.

I imagine they are going to get you to run that appalling package/procedure dbms_space.migrate_to_local (or something like that). It's does an abysmal job in the way it standardises extent sizes when moving to LMT mode... namely, it doesn't bother. It takes whatever extent map that is currently in the tablespace, and builds a bitmap to represent it. So if the tablespace is fragmented to hell before you start, it is fragmented to hell after you finish.

I would guess that they want you to coalesce before running said package and procedure because they want you to believe the package/procedure has actually improved the situation, whereas what it actually does is merely convert a bad situation into a slightly different bad situation.

My advice has been and remains: never run migrate_to_local. If you want to convert things to locally managed tablespace, then do it the 'proper' way, which is to create new locally managed tablespaces with appropriately-sized extents, and then move your tables one at a time into the new tablespace.

That is an incredibly expensive operation, of course, both in terms of disk space requirements, and in terms of length of time to complete, and the amount of physical I/O performed, and the length of time of non-access to the data whilst it's being moved. But if you want a job done properly, then the expense is worth it.

In any event, if you *are* going to simply migrate_to_local, then Oracle's suggestion to coalesce what you've got first has enormous merit, because otherwise you'll just convert one steaming pile of mess into another similar steaming pile.

> I have done the migration in all non-production instances of this
> warehouse application; they ran seemingly ok and those databases are
> performing well, according to the warehouse users.
>
> What is your experience with this? What's the problem or the bad
> results in the LMT if I migrate these tablespaces now?
>
> Am looking for the quick fix now, to be following to reorgs later.

I do wish people would wake up and realise: this is Oracle. There are no quick fixes. There's only doing the job right. Or doing it wrong.

Regards
HJR
> Thank you.
>
> Charles
Received on Fri Oct 22 2004 - 07:26:40 CDT