Features of local tablespace management and dictionary tablespace management. What are the features of assm?

Dictionary-managed table space (DMT) is a table space management mode that can be used before and after 8 I. It is used to manage the space usage of the table space through the data dictionary.

Oracle uses two dictionaries to record extents usage: SYS. Fet $ to record idle extents and SYS. uet $ to record extents used. When allocating and releasing partitions, you must modify the two dictionaries. It is a DML operation and has competition. Every action is a transaction and an undo record is generated. The same operation method is required for space merging. Low Efficiency

 

Local table space management (LMT) is a new table space management mode that will appear after 8 I. It uses bitmap to manage the space usage of the table space.

A Bitmap in the Data header file is used to record the usage of the database in this file. Each bit represents the data block. By changing the bit value, it indicates that extents is allocated, used, or released, and is not a DML operation, it does not generate undo records and can automatically track consecutive idle space. Advantages: recursive SQL calls on the dictionary SYS. Fet $ and SYS. uet $ are not required; competition for data dictionary is reduced; UNDO records are not generated; and periodic merge operations are no longer required.

For example, a local table space is created and the size of the interval is allocated to 64 KB.
SQL> Create tablespace demo
Datafile '/ora01/OEM/oemdemo01.dbf' size 10 m
Extent management local uniform size 64 K;

Query the table select * From dba_tablespace;

Automatic segment space management (assm)

Before 920, the management and allocation of the table's remaining space were completed by the connection list freelist, because freelist had a serial problem, therefore, it is easy to cause competition and waste of space (which is not obvious ), most importantly, DBAs need to spend a lot of energy managing these contention and monitoring the space utilization of tables.

Automatic segment space management (assm) appears in oracle920 for the first time. With assm, the connection list freelist is replaced by bitmap. It is a binary array that can quickly and effectively manage storage extensions and remaining blocks (free blocks ), therefore, it can improve the nature of segmented storage. The segment created on the assm tablespace is also called bitmap managed segments (BMB segment)

Create tablespace demo
Datafile '/ora01/OEM/demo01.dbf'
Size 5 m
Extent management local -- Turn On LMT
Segment space management auto -- Turn On assm;

 

Once you have defined tablespace, tables and indexes can be easily moved to the new tablespace in various ways, local tablespace with assm will omit any values specified for pctused, next, and freelists.

When a table or index is assigned to this tablespace, The pctused value for the independent object is ignored, and Oracle9i uses a bitmap array to automatically manage the freelist of the table and index in tablespace. This next extension clause is out of date for tables and indexes created in the tablespace of LMT, because the tablespace managed locally manages them. However, the initial parameter is still required because Oracle cannot know the size of the initial table load in advance. For assm, the minimum initial value is three blocks.

The new management mechanism uses bitmap to track or manage each block allocated to an object. The remaining space of each block is determined based on the bitmap status, for example,> 75%, 50%-75%, 25%-50% and <25%, that is, the bitmap uses four status bits to replace the previous pctused. When to use the data block, it is determined by the set pctfree.

A major advantage of using assm is that bitmap freelist can certainly reduce the burden of buffer busy waiting (buffer busy wait), which was a serious problem in earlier versions of Oracle9i.

Reprinted: http://blog.csdn.net/yuan22003/article/details/6714341