Local management table space, dictionary management table space and ASSM features in Oracle

Dictionary Management table Space-DMT Local management table space-LMT

The Dictionary Management table space (dictionary-managed tablespace, DMT), 8i previously includes a table space management model that can be used in the future, managing the space use of tablespaces through a data dictionary.

Oracle uses two dictionaries to record the use of extents: sys.fet$ Records idle Extents,sys. The extents used by Uet$ records. When partitioning is allocated and released, the two dictionaries are modified. is a DML operation, there is competition, each action is a transaction, will produce the undo record. A periodic merge operation is required. Low efficiency

The Local management table space (locally Managed tablespace LMT), a new table space management mode that appears after 8i, manages the space use of tablespaces through bitmaps.

A bitmap in the data header file to record the use of the table space in the file, each bit represents the data area, by changing the bit value to indicate the allocation of extents or release, does not belong to DML operations, and will not produce undo records, can automatically track the continuous free space. Advantages: There is no need to use recursive SQL calls on the dictionary sys.fet$ and sys.uet$; reduce competition for data dictionaries; Undo records are not generated; The periodic merge operation is no longer required.

Example: A locally managed tablespace was created with an interval uniform size assigned to 64K

sql> Create Tablespace leonarding

DataFile '/ora01/oem/leonarding 01.dbf ' size 100m

Extent management Localuniform. Size 64k;

The local administration tablespace (LMT)--LMT is implemented by adding extent MANAGEMENT to the definition syntax of tablespace. Unlike the Tablespace (DMT) formerly managed by the dictionary, LMT automates extension management and maintains that Oracle DBAs will not be used to specify next storage parameters to manage the extended size. The only exception to this principle is the time when the table was created with Next and minextents.

The tablespace of automatic segment Space Management (ASSM)--ASSM is realized by adding segment spaces MANAGEMENT auto clause to the definition syntax of tablespace. By using bitmap freelist instead of a traditional one-way list of links FREELIST,ASSM Tablespace automates freelist management and cancels the designation of pctused, Freelists, and freelist for separate tables and indexes Groups the ability to store parameters.

Automatic segment Space Management (ASSM)

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Before 920, the management and allocation of the remaining space of the table were freelist by the connection list, because there was a serial problem in the freelist, so it was easy to cause the contention of the segment head and the waste of the space (in fact, this is not obvious), The main thing is that DBAs need to spend a lot of effort managing these contentions and monitoring the space utilization of the tables.

Automatic segment Space Management (ASSM), which first appeared in Oracle920. With ASSM, the connection list freelist replaced by a bitmap, which is a binary array that quickly and efficiently manages storage extensions and remaining chunks (free blocks), thereby improving the nature of segmented storage, ASSM the segments created on the table space, and another salutation called bitmap Managed segments (BMB section)

Create tablespaceleonarding

DataFile '/ora01/oem/leonarding01.dbf '

Size 10m

EXTENT MANAGEMENT local--Turn on LMT

SEGMENT space MANAGEMENT auto--Turn on ASSM;

Once you have defined the tablespace, tables and indexes can be easily moved to the new tablespace, with ASSM Local admin tablespace skipping any values specified for pctused, next, and freelists.

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

The new management mechanism uses bitmaps to track or manage each block allocated to the object, and how much space is left in each block based on the state of the bitmap, such as >75%,50%-75%,25%-50% and <25%, which means that the bitmap actually employs four state bits to replace the previous pctused, When to use the data block is determined by the set of Pctfree.

One of the great advantages of using ASSM is that bitmap freelist can certainly mitigate the burden of buffer busy wait, a problem that was a serious problem in previous versions of Oracle9i.

Buffer is no longer busy waiting

In the absence of multiple freelist, each Oracle table and index has a block of data in the header of the table that manages the remaining chunks used by the object and provides a block of data for any new data rows created by any SQL insert declaration. Buffer busy waiting occurs when the data block within the data buffer is not available because it is locked by another DML transaction. When you need to insert multiple tasks into the same table, the tasks are forced to wait, while Oracle assigns the remaining blocks at the same time.

With ASSM, Oracle claims to significantly improve the performance of DML concurrency, because different parts of the same bitmap can be used simultaneously, eliminating the ability to serialize the search for the rest of the space. Based on Oracle's test results, using bitmap freelist eliminates all fragmentation headers (Resource) contention, and can also get super fast concurrent insert operations

Limitations of ASSM

Although ASSM shows an exciting feature and simplifies the work of Oracle DBAs, there are some limitations to Oracle9i's bitmap segmentation:

1. Once the DBA is assigned, it cannot control the storage behavior of independent tables and indexes within the tablespace.

2. Large objects are not able to use ASSM, and separate tablespace must be created for tables that contain LOB data types.

3. You cannot use ASSM to create a temporary tablespace. This is determined by the transient nature of the temporary segment of the sort.

4. Only locally managed tablespace can use bitmap segmentation management.

5. Performance problems may occur when using ultra-high-capacity DML (such as inserts, updates, and deletes).

Author: 51cto Blog Oracle Little Bastard