RE: ASSM and high volume concurrent inserts
Date: Fri, 18 Dec 2009 18:06:45 +0000
Message-ID: <7C4BF3B32B80CC44AE37D31B172415933A5DCB32CD_at_GVW1337EXC.americas.hpqcorp.net>
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 [mailto:mwf_at_rsiz.com] Sent: Friday, December 18, 2009 12:43 PM To: tim_at_evdbt.com; Powell, Mark; Oracle-L_at_freelists.org 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.
Regards,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Friday, December 18, 2009 11:46 AM To: mark.powell2_at_hp.com; Oracle-L_at_freelists.org 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 18 2009 - 12:06:45 CST