Oracle automatic segment space management (ASSM)

From Oracle 9i, there are two methods to manage a Segment Space: manual Segment Space Management and automatic Segment Space Management (ASSM );

ASSM 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.

For ASSM, you only need to control a parameter related to space usage: PCTFREE.You can also accept other parameters when creating a segment, but these parameters are ignored.

In terms of storage/segment features, the storage settings applied to ASSM segments are only:

BUFFER_POOL

PCTFREE

INITRANS

MAXTRANS (for 9i only, this parameter is ignored for all segments in 10g and later versions .)

BUFFER_POOL: tables can be put into the keep or other tables, such as ALTERTABLE Table_Name STORAGE (BUFFER_POOL KEEP );

PCTFREE: this parameter is used to tell oracle how much space should be reserved on the block to complete future updates. The default value is 10%.If the free space percentage is higher than the value specified in PCTFREE, this block is considered free.

When ASSM is used, PCTFREE will limit whether a new row can be inserted into a block, but it does not control whether a block is on FRRELIST, because ASSM does not use FREELIST at all, in ASSM, PCTUSED is also ignored.

PCTFREE has three settings: too high, too low, and just right. If the PCTFREE block is set too high, it will waste space. If you set PCTFREE to 50%, and you never update data, each block will waste 50% of the space. However, in another table, 50% may be very reasonable. If the row is small at first, you want to double the row size, but if the PCTFREE setting is too small, updating the row will cause row migration.

Row migration refers to rowmigration, which means that a row becomes too large to be placed together with other rows in the block for creating this row, and placed in other blocks.

If such row migration accounts for a large proportion, and a large number of users are accessing these rows, the access speed will start to slow down, becauseThe additional I/o and I/o-related latencies will increase the access time, and the buffer cache efficiency begins to decrease because two blocks need to be cached, if there is no row migration, only one block needs to be cached.In addition, the table size and complexity increase.

How to Set PCTFREE (ASSM ):

If you insert a large amount of data to be updated, and these updates will frequently increase the size of rows, you can set a relatively high PCTFREE, this can reserve a large amount of space on the block after insertion.

If you only want to complete the INSERT or DELETE operation on the table, or the UPDATE operation only has a small impact on the row size, you can set a relatively low PCTFREE.