Oracle Expert tuning Secret (III.)

Oracle Adjusts Oracle's competition

One of the benefits of Oracle is that it manages the free space in each table space. Oracle handles the space management of tables and indexes so that we don't have to understand the internal workings of Oracle's tables and indexes. However, for an experienced Oracle tuning expert, he needs to understand how Oracle manages the extent and free blocks of data for a table. This is important for systems that have a high insert or update to adjust.

To master the adjustment of objects, you need to understand the behavior of the freelists and freelist groups, which are related to the values of Pctfree and pctused parameters. This knowledge is particularly important for enterprise resource planning (ERP) applications, where incorrect table settings are often the reason for the slow execution of DML statements.

The most common mistake for beginners is to assume that the default Oracle parameters are best for all objects. Unless disk consumption is not a problem, when you set the table's Pctfree and pctused parameters, you must consider the average length of the president and the database, so that the empty blocks are effectively placed in the 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 can result in significant processing delays.

Freelists is important for the efficient reuse of space in an Oracle tablespace, and it is directly related to the Pctfree and pctused settings for these two storage parameters. Ü 玴 ctused is set to a high value, the database will reuse the block as quickly as possible. However, the high performance and efficient reuse of the table blocks is antagonistic. When you adjust Oracle tables and indexes, you need to carefully consider the need for high performance or efficient space reuse, and set the table parameters accordingly. Here's a look at how these freelists affect Oracle's performance.

When a request needs to be inserted into a table, Oracle will go to the freelist to find a block that has enough space to hold a row. As you may know, the freelist string is placed in the first block of a table or index, and this block is also called the segment Header (segment header). The sole purpose of the Pctfree and pctused parameters is to control how blocks are in and out of the freelists. Although Freelist link and unlink are simple Oracle features, setting freelist link (pctused) and unlink (Pctfree) does have an impact on Oracle performance.

The basic knowledge of the DBA is that the Pctfree parameter controls Freelist Un-links (The block is removed from the freelists). Setting pctfree=10 means that each block retains 10% of the space used as a row extension. pctused parameters are controlled by Freelist Re-links. Setting pctused=40 means that the block is returned to the freelists of the table only if its use is less than 40%.

Many novices have misunderstood the handling of a block after returning to Freelists. In fact, once the block is added to the freelist as a result of a delete operation, it will remain in freelist even if the space is used more than 60%, the block is removed from the freelist only when the Pctfree is reached.

Summary of requirements for table and index storage parameter settings

Some of the following rules are used to set freelists, freelist groups, Pctfree, and pctused storage parameters. As you know, the values of pctused and Pctfree can easily be modified by the ALTER TABLE command, and a good DBA should know how to set the best values for these parameters.

There is a contradiction between the efficient use of space and high performance, and the table storage parameter is the control of this aspect of the conflict:

. For efficient reuse of space, you can set a high pctused value, although side effects require additional I/O. A high pctused value means that the relatively full block will be placed in the freelist. As a result, these blocks can only accept a few rows of records before they are full again, resulting in more I/O.

. In pursuit of high performance, you can set pctused to a low value, which means that Oracle does not place blocks of data into freelists until it is almost empty. The block will be able to receive more rows before it is full, thereby reducing I/O to the insert operation. Keep in mind that the Oracle extension new block is more performance-efficient than the existing blocks are reused. For Oracle, extending a table consumes less resources than managing freelists.

Let's review some common rules for setting object storage parameters:

． Pctused is often set to receive a new line. It is no use for us to accept free blocks for a single line. Doing so will slow Oracle's performance because Oracle will attempt to read 5 "dead" free blocks before extending the table to an empty block.

． The presence of chained rows in the table means that Pctfree is too low or db_block_size too little. In many cases, raw and long raw columns are large enough to exceed the size of the largest Oracle block, when chained rows cannot be avoided.

． If a table has an SQL statement inserted at the same time, it needs to have a statement that is deleted at the same time. Running a single purge of the work will put all the free blocks in a freelist, and no other freelists that contains any free blocks appears.

． The freelist parameter should be set to the maximum value that the table updates at the same time. For example, if at any time a table has a maximum of 20 users performing an insert operation, the table's parameters should be set to freelists=20.

It should be remembered that the value of the freelist groups parameter is only useful for Oracle Parallel server and real application clusters. For this type of oracle,freelist groups should be set to the number of Oracle Parallel server instances that access the table.