Oracle expert tuning secret (3)

zhaozj2021-02-16  111

Adjusting one of the advantages of Oracle's competition Oracle that it can manage free space in each table space. Oracle is responsible for handling space management of the table and index, so we can make us do not need to understand the internal operation of Oracle's tables and indexes. However, for experienced Oracle tuning experts, he needs to know how Oracle is how to manage the extent and idle data blocks. This is very important for the system that adjusts high INSERT or UPDATE. To be proficient in the adjustment, you need to understand the behavior of the Freelists and FreeList groups, they are related to the value of the PCTFree and PCTUSED parameters. These knowledge is particularly important for the application of the Enterprise Resource Program (ERP), because in these applications, incorrect table settings are usually the reasons for the DML statement to perform slow. For beginners, the most common mistake is that the default Oracle parameter is best for all objects. Unless the disk's consumption is not a problem, otherwise the PCTFREE and PCTUSED parameters of the table must consider the block size of the database, which empty blocks are effectively placed in FreeElists. When these settings are incorrect, those get Freelists are also "dead" blocks because they do not have enough space to store a row, which will result in significant processing delay. Freelists is important to effectively reuse spaces in Oracle tablespace, which is directly related to the settings of the two storage parameters of PCTFree and PCTUSED. ü  玴 CTUSED is set to a high value, and the database will reuse the block as quickly as possible. However, the blocks of high performance and effectively reuse tables are opposite. When adjusting Oracle's forms and indexes, you need to carefully consider the need for high performance or valid space reuse, and therefore set the parameters of the table. Let's take a look at how these FreeELists affect the performance of Oracle. When there is a request to insert a row into the table, Oracle will find a block with enough space to accommodate a line in FreeList. You may know that the Freelist string is placed in the first block of the form or index, and this block is also called a segment header. The unique purpose of the PCTFree and PCTUSED parameters is to control how the blocks are available in Freelists. Although FreeElist Link and Unlink are simple Oracle features, it is true that Freelist Link (Pctused) and UnLink (PCTFREE) do have indeed effect on Oracle. The basic knowledge of DBA knows that the PCTFree parameter is to control freelist un-links (the block is removed from FreELISTS). Setting PCTFree = 10 means that each block retains 10% of space for row expansion. The PCTUSED parameter is to control freeelist re-links. Setting PCTUSED = 40 means that only when the block is used below 40%, it will return to Freelists in the table. Many newcomers have some misunderstandings for the process after returning to FreElists. In fact, once a deleted operation, the block is rejoined into the freeelist, it will always be retained even if the space is used in FreeList, which is only dried by the data block by Freelist when the PCTFree is reached. . The requirements for tables and index storage parameters settings summarize that some of the following rules are used to set FreELists, FreeList Groups, PCTFREE, and PCTUSED storage parameters. You also know that the value of PCTUSED and PCTFREE can be easily modified through the alter table command, a good DBA should know how to set the best value for these parameters.

转载请注明原文地址:https://www.9cbs.com/read-10204.html

New Post(0)