Oracle9i automatic segmented space management (10-minute reading experiment)

What can I do if I continue the experiment for 10 minutes? The Oracle automatic space management mechanism is introduced. The results are as follows:

Find a suitable article: 1 minute 15 seconds.

------------------------------------

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. From Oracle8i, Oracle began to automate Object Management in tablespace. 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.

So far: 2 minutes 30 seconds.

Two Methods for space management

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

Local Management of tablespace (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 segment space management (assm )?? The tablespace of assm is implemented by adding the segment space management auto clause 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.

4 minutes 10 seconds.

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　　　 5m　　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:

　　　　 　　create table　　　 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 this tablespace, The pctused value for the independent object will be ignored, oracle9i uses bitmap arrays to automatically manage the table and index freelist 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.

6 minutes 20 seconds.

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> create table　　2 test_table　　3 (c1 number)　　4 tablespace　　5 asm_test　　6 pctfree 20 pctused 30　　7 storage　　8 ( freelists 23 next 5m ) ;　　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.

So far: 8 minutes 30 seconds.

Buffer no longer busy waiting

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

 

The total time is 9 minutes 40 seconds.

I don't think the knowledge is intended to be memorized. After reading this article, you only need to understand the basic principles and advantages and disadvantages of the two Oracle storage management mechanisms. As for the specific syntax, you can check it again when using it.