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

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:4178fc76$0$20128$afc38c87_at_news.optusnet.com.au...
> 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
>

Thank you for the reply.

Can you please elaborate on what you mean by a steaming mess, in technical terms.

I believe I can not simply reorganise this database in LMTs to correct the problem; I believe I need an interim step to alleviate the problem.

I believe that if I were to reorg into proper LMTs now, then the final drop of the object in the DMT will only increase the coalescing problem. Oracle will convert the dropped object to a temporary object and them SMON will have to deal with it as a DMT object and coalese it. That seems to me to only exacerbate the problem.

Please tell me, in as detailed technical terms as you can, what mess will be created by Migrate-to-local prior to me properly reorging the 1.8TB database into proper LMTs.

This is generally what I originally asked.

C Received on Sun Oct 24 2004 - 11:00:16 CDT