What is Oracle ASSM to improve segmented storage?

The following articles mainly introduce how Oracle ASSM improves segmented storage. In practice, we want to maintain its most powerful and flexible database position, therefore, Oracle has been creating new mechanisms to simplify and block the storage of tables and indexes in several related versions recently released.

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. It appears in Oracle9i for the first time. With OracleASSM, The Link List freelist is replaced by bitmap. It is a binary array that can quickly and effectively manage storage extensions and free blocks of remaining 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 management of tablespaceLMT)-LMT is implemented by adding the extent management local clause to the definition Syntax of tablespace. Unlike tablespaceDMT, which was originally managed by the dictionary, LMT automates 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 tablespace of Oracle ASSM 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  
  
  
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 OracleASSM, 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.