RE: ASSM and high volume concurrent inserts (permuted into DMT vs LMT vs SM_LMT)

From: Mark W. Farnham <>
Date: Fri, 18 Dec 2009 15:23:20 -0500
Message-ID: <>

One thing is the scaling. If I only have to consult the bitmap for one file to allocate from that file, then I only compete for the bitmap with others attempting to get stuff from that one file. The other thing is tablespace transportability. Sure seems a lot simpler if you take your space management stuff along with you. But a pair of files should work also.  

From: [] On Behalf Of Powell, Mark
Sent: Friday, December 18, 2009 1:07 PM
Subject: RE: ASSM and high volume concurrent inserts  

I can understand placing space management bitmaps in the data files that the bitmap covers as that just seems logical, but I also wondered if Oracle considered placing all the bitmaps in a bitmap table contained in the system tablespace insteadand if so why this idea lost out to using the data files. For an active data file the bitmap should be in the buffer cache anyway, but I suspect the table method would provide superior performance for a lot of DBA space queries that calculate total used and free space.  

Just a curiousity question that on one here is likely to be able to answer since only the Oracle developers would know the real answer.    

From: Mark W. Farnham [] Sent: Friday, December 18, 2009 12:43 PM To:; Powell, Mark; Subject: RE: ASSM and high volume concurrent inserts

Nice one Tim! I had indeed not thought of that. It does point out the screaming need for the ability to split the metadata in an LMT into a separate file. But that is an enhancement request, not current fact, and it would be that the "split metadata" LMT would consist of at least two files (though one would be pretty small). Definitely a valid use of DMT until and unless SM_LMT exists.  

As for unlimited growth earlier in the thread I just recommend a generous but achievable maxsize rather than unlimited. If something tries to grow infinitely you're going to have something go splat regardless.  



From: [] On Behalf Of Tim Gorman
Sent: Friday, December 18, 2009 11:46 AM To:; Subject: Re: ASSM and high volume concurrent inserts  

Responding to the question "does anyone think dictionary managed tablespaces are ever a good choice anymore"...  

The one scenario where I've found DMT to be preferable to LMT is where the underlying storage is migrateable or otherwise has some initialization latency for infrequent I/O requests. Mainframes have long used hierarchical file-systems, where a seldom-accessed file is transparently moved or "migrated" to offline storage, requiring a "recall" to primary storage when the left-behind file-header is accessed. the problem that LMT tablespaces have is that every query on the DBA_EXTENTS and DBA_FREE_SPACE views requires access to the bitmapped extent-map blocks residing right in the datafile. So, in an environment that is possibly likely to grow onto such storage, now or in future, it is worth considering retaining the ability to create DMTs by creating the database with the SYSTEM tablespace DMT. Use LMT tablespaces in most situations, but convert to DMT (using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL) prior to moving a tablespace to migrateable storage.      

Received on Fri Dec 18 2009 - 14:23:20 CST

Original text of this message