Use assm to improve segmented storage in Oracle databases

In order to maintain its strongest and most flexible database position, Oracle has been creating new mechanisms in recent releases to simplify and block the storage of tables and indexes. Starting from Oracle8i, OracleTablespaceAutomate object management internally. The first enhancement was originally called local tablespace Management (or simple writing LMT ). In LMT, Oracle removes the information in tablespace from the table space in the data dictionary and directly saves it to tablespace itself. This has become a de facto standard in Oracle9i, because it reduces the burden on data dictionaries.

The second major enhancement of table space is automatic segment space management (assm), which appears in Oracle9i for the first time. With assm, The Link 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.

　　Two Methods for space management

Let's start by comparing the two types of space management:

Local tablespace Management (LMT)-LMT is implemented by adding the extent management local clause to the definition Syntax of tablespace. Unlike the original tablespace (DMT) managed by the dictionary, LMT automates the extended management and keeps Oracle DBAs from being used to specify the next storage parameter for managing the extended size. The only exception to this principle is that next and minextents are used in table creation together.

Automatic partition space management (assm) -- The tablespace of assm isSegmentThe space management auto clause is added to the definition Syntax of tablespace. By using the bitmap freelist instead of the traditional one-way link list freelist, The assm tablespace will automate the management of freelist, disable the ability to specify parameters for pctused, freelists, and freelist groups for independent tables and indexes.

It is commendable that these two spatial management methods are optional, and Oracle veterans may still use more detailed methods as long as they wish. It is important to note that the management of Bitmap segments is optional in Oracle9i and can only be implemented at the tablespace layer. The original system can continue to use traditional methods to manage freelist.

　　Bitmap freelist challenges traditional space management

Before I discuss bitmap freelist and traditional space management, let's see how bitmap freelist is implemented. I will start from using the section space management Automatic Parameter creation tablespace:

Create tablespace Asm_lmt_ts Datafile 'C:/Oracle/oradata/Diogenes/asm_lmt.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. The code I created is as follows:

CreateTable New_cust Tablespace Assm_lmt_ts As Select * from customer; Alter index cust_name_idx rebuild tablespace assm_lmt_ts;

Note that when a table or index is assigned to thisTablespaceLater, the value of pctused for independent objects will be ignored, while Oracle9i will use bitmap arrays to automatically manage the freelist of tables and indexes 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.

There is still some debate about whether the 10 thousand method is the best method for Oracle. In large databases, individual object settings bring about a huge difference in performance and storage.

　　Pctfree Problems

The pctfree parameter is used to specify the remaining space of a data block, which will be reserved for future expansion of data rows. If pctfree is not set properly, the SQL update statement may cause a large number of data row fragments and broken links.

The data row is still small when it is saved, but is extended later. In this case, pctfree settings are particularly important. In such a system, pctfree is usually set to 95, which tells Oracle to keep 95% of the Data Segment space for future expansion of data rows.

　　Pctused Problems

Incorrect pctused settings (such as being too small) will lead to a sharp reduction in SQL insert statement performance. If there is not much space left in the data block, an excessive amount of I/O will be generated during the SQL insert operation, because the re-use of the Oracle data block will be quickly filled up. From an extreme point of view, if pctused is not correctly set, the remaining space in the data block will be smaller than the average length of the table data row. In this case, Oracle will try to retrieve the block from the freelist chain five times. After five attempts, Oracle will raise the table level and free up five new data blocks for the insert operation.

With the assm of Oracle9i, pctused no longer controls the relink threshold of table data blocks, however, you must rely on Oracle to determine when the block will have enough space to be placed in freelist.

Although Oracle9i ignores parameters such as pctused, freelists, and freelist groups with locally managed tablespace and assm, Oracle still does not provide error information when they are used for table definition:

SQL> CreateTable 2 test_table 3 (C1 number) 4 tablespace 5 asm_test 6 pctfree 20 pctused 30 7 storage 8 (freelists 23 next 5 m ); Table created.

If you do not remember that the locally managed tablespace with assm will omit any values specified for pctused, next, and freelists, this will be a very serious problem.

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. Now let's take a closer look at this feature.

When no freelist exists, each Oracle table and index has a data block in the table header to manage the remaining blocks used by objects, it also provides data blocks for new data rows created by any SQL insert statement. When the data block in the data buffer is locked by another DML transaction and cannot be used, the buffer wait will occur. When you need to insert multiple tasks into the same table, these tasks are forced to wait, and Oracle will assign the remaining blocks at the same time, one at a time.

With assm, Oracle claims to significantly improve the performance of DML concurrent operations, because different parts of the (Same) bitmap can be used at the same time, this eliminates the serialization of the search for the remaining space. Based on the test results of Oracle, the use of the bitmap freelist will eliminate competition for all segment headers (resources) and achieve ultra-fast concurrent insert operations (figure ).

　　

Figure A test result of Oracle's SQL insertion using the bitmap freelist

　Limitations of assm

Although assm shows exciting features and can simplify the work of Oracle DBA, the bitmap Management of Oracle9i still has some limitations:

Once the DBA is assigned, it cannot be controlled.TablespaceStorage behavior of internal independent tables and indexes.

A large object cannot use assm, and a separate tablespace must be created for a table containing the lob data type.

You cannot use assm to create a temporary tablespace. This is determined by the short-lived feature of temporary segmentation during sorting.

Only the locally managed tablespace can use bitmap segment management.

Performance problems may occur when ultra-high-capacity DML (such as insert, update, and delete) is used.