Deep talk about Oracle Freelist experience

Oracle has a lot to learn about. Here we mainly introduce Oracle Freelist, including Freelists and other aspects. One of Oracle's advantages is that it can manage free space in each tablespace. Oracle is responsible for table and index space management, so that we do not need to understand the internal operations of Oracle tables and indexes. However, for experienced Oracle tuning experts, he needs to understand how Oracle manages table extent and idle data blocks. This is very important for systems with high insert or update values.

To be proficient in object adjustment, you need to understand the behaviors of Freelists and Freelist groups. They are related to the values of pctfree and pctused parameters. This knowledge is especially important for the application of Enterprise Resource Planning ERP, because incorrect table settings in these applications are usually the cause of slow execution of DML statements.

For beginners, the most common error is that the default Oracle parameter is the best for all objects. Unless disk consumption is not a problem, when you set the pctfree and pctused parameters of the table, you must consider the average length of rows and the size of the database blocks, in this way, empty blocks are effectively put into Freelists. When these settings are incorrect, the resulting Freelists are also "dead" blocks, because they do not have enough space to store a row, which will lead to a significant processing delay.

Oracle Freelist is very important to effectively reuse the space in the Oracle tablespace. It is directly related to the settings of the pctfree and pctused storage parameters.

By setting pctused to a high value, the database will re-use the block as soon as possible. However, high-performance and effective reuse of table blocks are opposite. When adjusting Oracle tables and indexes, you must carefully consider whether high performance or effective space reuse is required, and set table parameters accordingly. The following describes how Freelists affect Oracle performance.

When a request needs to insert a row into the table, Oracle Freelist looks for a block with enough space to accommodate a row. You may know that the Freelist string is placed in the first block of a table or index, which is also called the segment header ). The unique purpose of pctfree and pctused parameters is to control how a block is imported and exported in Freelists. Although Freelist link and unlink are simple Oracle functions, setting Freelist link (pctused) and unlink (pctfree) does have an impact on Oracle performance.

According to the DBA's basic knowledge, the pctfree parameter is used to control the forthcoming block of Freelist un-links to be removed from Freelists ). Setting pctfree = 10 means that each block retains 10% of the space for Row Expansion. The pctused parameter controls Freelist re-links. Setting pctused = 40 means that only when the block usage is lower than 40% will the data be returned to Freelists in the table.

Many new users have some misunderstandings about how to process a block that is returned to Freelists. In fact, once a block is re-added to Freelist due to a delete operation, it will remain in Freelist even if the space usage exceeds 60%, data blocks are moved from Freelist only when pctfree is reached.

1. Oracle Spatial Learning Experience
2. Master talk about Oracle Spatial
3. Comprehensive summary of Oracle Data Types
4. Quick introduction to Oracle SQL
5. Oracle Dual table