Oracle data block optimization parameters

Condition for Oracle to automatically manage the free space of blocks: When a row for an insert or update operation has enough free space in a data block, and the idle space fragment status cannot be full

Condition for Oracle to automatically manage the free space of blocks: When a row for an insert or update operation has enough free space in a data block, and the idle space fragment status cannot be full

Two parameters used for data block optimization: pctfree and pctused are generally used in OLTP systems. There is no relationship between the two. When one of the conditions is met, the free-list will be displayed/removed.

Condition for Oracle to automatically manage the free space of a block: When a row for an insert or update operation has enough free space in a data block, and the idle space fragment status cannot meet the needs of a row of data. The reason why volume El does not always automatically fragment (merge idle space) is that this leads to a certain amount of system resource overhead.

Pctfree: when the data volume in the data block reaches this value, data cannot be inserted.

Pctused: when the space occupied by data blocks is less than this proportion, the data blocks will be used again

Row connection: a row is stored in multiple blocks. This is because the length of the row exceeds the available space of one block, that is, the row link spans multiple rows. It is generally the Insert operation.

Row migration: when a data row is not suitable for being placed in the current block but is located in another block (with sufficient space), a pointer is retained in the original block. Pointers in the original block are required because the ROWID of the index still points to the original position. It is usually the update operation.

PCTFREE: the percentage of space reserved for a block. It indicates when a data block can be inserted. The default value is 10, indicating that when the available space of the data block is less than 10%, it cannot be inserted and can only be used for update. That is, when a block is used, it can be inserted until it reaches pctfree, which is in the rising stage.

PCTUSED: when the data in a block is smaller than a certain percentage, it can be re-inserted. Generally, the default value is 40, that is, 40%, that is, when the data is less than 40%, you can also write new data, which is in the descent phase.

I. calculation formula:

PCTFREE = (Average Row Size-Initial Row Size) * 100/Average Row Size

PCTUSED = (100-PCTFREE)-Average Row Size * 100/Availabe Data Space

SQL> select table_name, AVG_ROW_LEN from user_tables where table_name in ('tab1', 'tab2 ');

Ii. Examples

Assume that you can store 100 pieces of data in one block, and PCTFREE is 10 and PCTUSED is 40. Then, insert data into the block continuously. If you store 90 pieces of data, new data cannot be stored. This is controlled by pctfree and the reserved space is used for UPDATE.

When you delete a data row, can you insert a new data row? No. 41 data records must be deleted, that is, less than 40 data records can be inserted. This is controlled by pctused.

NOTE: If ASSM is enabled on the tablespace, you can only specify PCTFREE when creating a table. Otherwise, you can specify PCTFREE and PCTUSED.



3. Adjust pctfree and pctused

1. Use pct_free and pct_used of user_tables to view

Select a. table_name, a. pct_free, a. pct_used, a. * from user_tables;

Note:

Pctfree indicates the percentage of update operations to be retained. If this value is exceeded, data cannot be inserted.

Pctused indicates the lowest percentage of data. If pctfree is reached, data cannot be inserted. If pctused is reached after the delete operation, data can be inserted.



2. If you are using automatic tablespace management, you do not need to set the pctused

Altertable tablename pctfree values;

Note:

The default value of pctfree is 10. It mainly depends on the size of the updated data. You can check max_row_len of the table. If it is large and frequently updated, you can consider adding this value.

Pctused mainly depends on the size of the deleted data. If it is large, you can increase the value. If it is not frequent, you can set a smaller value of 30-40.

Pctused + pctfree <90



3. View automatic tablespace Management

Select tablespace_name, segment_space_management from user_tablespaces; -- If segment_space_management is set to auto, the tablespace is automatically managed.

It is mainly used to save table space.

4. Use of pctfree and pctused

The value of pctfree is between 20 and 25, and the value of pctfree is 4 or 5, which can be used in static or read-only tables.

Do not set pctused to more than 40 or 50 unless you strictly manage the available space.

Generally, these two locations cannot reach 90. Otherwise, the Oarcle will spend more time on processing space utilization. The following are some references:

Pctfree 5 and pctused 40 are suitable for static or read-only tables.

Pctfree 10 and pctused 50 are suitable for integrated OLTP systems with no existing row lengths added to update activities after a row is inserted.

Pctfree 20 and pctused 40 are suitable for the OLTP system that adds existing rows to the update activity after the row is inserted.