"Go" Oracle freelist and HWM principle and performance optimization

article turned from: http://www.wzsky.net/html/Program/DataBase/74799.html

Recently, the important role of freelist has gradually been recognized by Oracle DBA, and there are some related discussions on the Internet. This article takes freelist as the clue to the Oracle's storage management principle to carry on the thorough discussion, involves the Oracle section block management principle, the freelist algorithm and so on. And a reuse characteristic HWM closely related to Freelist, which is closely related to SQL performance, this paper also introduces the principle analysis. On the basis of principle discussion, this paper introduces the method of storage parameter analysis, and explains the storage optimization, HWM optimization and freelist competition optimization.

Abbreviations :

Assm:auto segement Space Management

Hwm:high Water Mark

Dba:data Block Address

Oltp:online Transaction Process

Ops:oracle Parallel Server

1. Introduction

Oracle's space management and storage parameter management is an important part of Oracle's management and optimization. Freelist as the core parameter in Oracle's underlying storage parameters, its behavior has a significant impact on Oracle's storage management and performance optimizations, while existing Oracle documents are less content. Although Oracle 9i has ASSM, it is still necessary to understand freelist as an in-depth tuning.

Recently, the important role of freelist has gradually been recognized by Oracle DBA, and there are some related discussions on the Internet. This article takes freelist as the clue to the Oracle's storage management principle to carry on the thorough discussion, involves the Oracle section block management principle, the freelist algorithm and so on. And a reuse characteristic HWM closely related to Freelist, which is closely related to SQL performance, this paper also introduces the principle analysis. On the basis of principle discussion, this paper introduces the method of storage parameter analysis, and explains the storage optimization, HWM optimization and freelist competition optimization.

These principles analysis and performance optimization are based on the discussion, limited to the length and personal experience may exist limitations, deviations or fallacies.

In order to accurately describe some of the structure and fields in the text, the description is directly in English. Confined to space this article does not discuss the same important block structure in more depth, and the free list group, which has a significant impact on OPS performance, is not mentioned in this article, so this article is discussed under a single free list group. For an in-depth discussion of block, the introduction and optimization of the free list group, and optimization of important parameters such as pctused and Pctfree, see References and materials.

2. Principle Discussion

Freelist as a core parameter for Oracle Storage management. Its behavior is controlled by Oralce internal control, we generally do not need to grasp and control. But we may encounter these problems, when inserting a record, will be inserted into that block? Is it a new block or an old block of data? When did the segment expand, and how did it expand? There is only one record in the table, but the cost of making a select is thousands of blocks, why? If we understand the principles of Oracle's storage management approach, the solution to these problems and performance optimization is clear and natural.

2.1 Oracle's logical storage structure

The logical storage structure of ORALCE is managed by table space, segment, zone, and block. Blocks are the most basic unit that Oracle uses to manage storage space, and the Oracle database is logically read and written in blocks for input and output operations. The area is composed of a series of contiguous blocks, and the Oralce is based on the district as the basic unit for space allocation, recycling and management. Segments are made up of multiple zones, which can be contiguous or discontinuous, and typically an object has a segment. The table space accommodates segments and extents.

When the segment is generated, the initial zone (initial extents) is allocated at the same time, and the first block of the initial zone is formatted as the segment header and is used to record the free list description, extents information, HWM information, and so on.

2.2 Free list Concept

The free list is a one-way list used to locate blocks that can receive data, and in a dictionary-managed table space, Oracle uses free list to manage unallocated storage blocks. Oracle logs blocks with free space for insert or update. Free space comes in two ways: 1. All blocks over HWM in the segment have been assigned to segments, but have not yet been used. 2. All the blocks under HWM in the paragraph that are linked into the free list can be reused. The free list has the following properties

L flag indicates free list is used (1) or unused (0)

The address of the first block of the free list chain DBA (Data block addr)

L Address of the end block of the free list chain DBA

The free list information is usually kept in the segment header, where the segment header block dump fragment is described:

 NFL = 3, NFB = 1 Typ = 1 nxf = 0 SEG LST:: flg:unused L hd:0x00000000 ltl:0x00000000 seg LST:: flg:used lhd:0x03c00233 ltl:0x03c00233 SEG LST:: flg:used lhd:0x03c0 0234 ltl:0x03c00234 SEG LST:: flg:unused lhd:0x00000000 ltl:0x00000000 Segment Header: ==> nfl:number  of free Lists/block ==> nfb:number of the free list blocks + segment header ==> typ:block type ==> nxf:  Number of transaction free lists Segment list: ==> Flg:flag used or UNUSED the free List ==> lhd:head of free lists ==> ltl:tail of free list 

In each block there is a tag flg to indicate whether the block is linked into the free list chain. If this flag is placed, the back pointer in the block points to the DBA of the next block in the free list chain. If the current block is the last block of the chain, the forward pointer value is 0. This gives a fragment of block dump located on the free list

Block Header dump:0x03c00235 Object ID on Block? Y seg/obj:0xe2d8 csc:0x00.6264c61 itc:1 flg:o typ:1-DATA fsl:1 fnx:0x3c00234 ver:0x01 ==> seg/obj Object ID in dictionary ==> csc SCN for last block cleanout ==> ITC number of ITL slots ==> flg o = O n freelist,-= not on Freelist ==> typ 1 = DATA 2 = INDEX ==> fsl ITL TX freelist slot ==> fnx DBA of NEXT block on Freelist

For example, if there are five blocks in the free list, respectively, A,b,c,d,e

It will form segment header->a->b->c->d->e--|

Meanwhile segment Header->e

2.3 Free List Category

There are 3 categories of free lists in the segment, that is, Master freelists (MFL), Process freelists (PrFL), and Transaction freelists.

2.3.1 Master Free List (public idle space Pool):

There is a master free list in each segment, which is generated automatically when the segment is created. For each segment, there is a pool of free space that is common to each process, and the free space is on the block of the master a list. Because the master free list is common, the master free list competes more when multiple processes insert rows into the same segment at the same time.

2.3.2 Process Free Lists

In order to reduce the competition of Master free list, another free list called process free lists is introduced, which is created according to the parameters freelists in SQL command create/alter. This allows multiple free lists to allocate the management of idle space to improve the performance of the spatial allocation management of OLTP applications for highly concurrent insert and update transactions. Created by specifying the parameters of the Create Table/cluster or index clause storage freelists, for example: Create TABLE FLG (...). STORAGE (... Freelists 10 ...). The default freelists is 1, and the process free lists is not created at this time. When freelists>=2, create process free lists.

Processes using the Process free list are selected based on the Oracle PID (process ID) of the processes, with the following formula:

SELECT list entry = (PID% NFL) + 1

Nfl:freelists the number of process free lists defined

2.3.3 Transaction Free Lists

Dynamically created when Oracle needs it. A transaction free list is a free list that is used exclusively for a transaction. Each segment has at least 16 transactions free lists, and this value grows as needed until the size limit of the segment header block is reached. A transaction only needs to be assigned a TX free Lists entry when space is freed (DELETE or UPDATE) in the block, and no TX-Lists entry exist.

2.4 Free list behavior

2.4.1 Freelist Link and Unlink operation

Freelist is managed by a last-in-first-out queue (LIFO) mode. That is to say, finally, the block with link to Freelist has the first chance to unlink. When free space in the block is increased to greater than Pctfree, the block is placed in Freelist. The blocks in the free list can be used for update or insert. When there is not enough space in the block for the insert operation and the space is greater than pctused, the block is removed from the free list.

After the block is in the delete or UPDATE operation, if space is used to fall under Pctused, the block is again linked to the free list. Each time a block is added to the free list, it is the link to the list's head.

For example: consider that there are 120 block numbers from 1 to 120 in a segment. There are 6 blocks on the free list and assume that HWM is 80. (Block actually uses DBA number)

10->24->45->46->65->80-|

Now insert operation requires a bytes space. Assume that there is not enough space over block 10, but that the Block 24 is available. Now the data is inserted into block 24, and now the remaining space of block 24 is less than the pctused of the table. So block 24 is removed from the free list list. The purpose of the PCTFREE and pctused parameter is to control the movement of data blocks from the list of free lists into/out of the behavior. Now free lists like this:

10->45->46->65->80-|

Then delete the same segment data in the same transaction so that blocks 54 and 67 fall under pctused. Now these blocks are added to the free list chain. The free list chain now looks like this:

67->54->10->45->46->65->80-|

2.4.2 Transaction free List algorithm

Scan all of the TX free lists in the segment header block to see if there is not yet a TX no list entry assigned to transaction, and if not, will be looking for unused entry or an empty TX full list that has committed the transaction. If the search process fails, the new entry will be opened in the TX free lists zone in the segment header block. If there is no space to generate, the transaction must wait for the entry to be released.

Maximum number of free lists in the segment header:

Block Size Max # freelists

----------- -----------------

2 K 24

4K 50

8K 101

16k 204

The use of free blocks (DELETE or UPDATE) freed by transaction T1:

L was immediately reused by T1.

When T1 commit is reused by other transactions that require free blocks, the process is as follows:

2.5 HMW Concept

High water mark represents the largest (top limit) block used by a table. The high water MARK recorded in the segment header has been mentioned in 2.1 and generally grew by 5 blocks when Oracle inserted data (not always 5 blocks, see HMW growth patterns in the flowchart in 2.4.2).

Segment Header block with HWM related information is described as follows: EXTENT control:extent header:: spare1:0 space2:0 #extents: #blocks: 142 9 Last map 0x00000000 #maps: 0 offset:4128 highwater:: 0x020004d0 ext#: blk#: 275 ext size:475 #blocks in Seg. HDR ' s freelists:5 #blocks below:1229 mapblk 0x00000000 offset:12 unlocked ==> spare1:this field is No longer used (old inc#, now all 0) ==> space2:this field is no. longer used (old ts#, today always 0) ==> #extents: Number of extents allocated to segment ==> #blocks: Number of blocks allocated to segment ==> Las T map:address of last extent map block 0 if extent map was entirely in the segment header ==> #maps: Number of Extent map block ==> Offset:offset to end of extent map ==> HWM dba:address of the block at Highwater Mark ==> ext#: HWM Extent number relative to segment ==> blk#: HWM block number within extent ==> ext size:h WM Extent size (in Blocks) ==> #blocks in seg. HDR ' s freelists:number of blocks in SEG. HDR ' s free list ==> #blocks below:number of blocks below HWM ==> mapblk Dba:dba of extent map block Contai Ning HWM Extent is 0 if HWM are in the segment headers ==> Offset:offset within extent map block is the ext# If HWM is in segment headers ==> Locked by:if Locked by a transaction, the XID is displayed

HWM can be said to be the dividing line between already used storage space and unused storage space. During the use of the table, the HWM moves in one Direction, HWM may move in the increased direction when the record is inserted, but HWM does not move in the opposite direction when the record is deleted. See 2.4.2. Shows the location of the HWM in a data segment.

High water mark is important because of its impact on full table scan performance. When a full table scan is implemented, Oracle reads all blocks under the high water mark even if they are empty blocks. When high water MARK has many unused blocks, implementing a full table scan will add additional unnecessary I/O. It also fills a lot of empty blocks in the global shared area.

3. Analysis methods

Storage parameters are essentially Oracle internal, so ORALCE does not provide a good way to analyze them. However, for DBAs, it is possible to obtain some information by means of block dump and Dbms_space.

3.1 Extracting block and free list information

Create a stored procedure used by dbms_space

Show_space sql> Create or replace procedure Show_space (P_segname in Varchar2, P_owner in varchar2 default User, P_type in varchar2 default ' TABLE ', p_partition in varchar2 default NULL) as l_free_blks number; L_total_blocks number; L_total_bytes number; L_unused_blocks number; L_unused_bytes number; L_lastusedextfileid number; L_lastusedextblockid number; L_last_used_block number; Procedure P (P_label in varchar2, p_num in number) is Begin Dbms_output.put_line (Rpad (p_label,40, '. ') | | p_num ); End Begin Dbms_space.free_blocks (Segment_owner = p_owner, segment_name = p_segname, Segment_type = P _type, Partition_name = p_partition, freelist_group_id = 0, free_blks = l_free_blks); Dbms_space.unused_space (Segment_owner = p_owner, segment_name = p_segname, Segment_type = P_type, Partition_name = p_partition, total_blocks = L_total_blocks, total_bytes = l_total_bytes, unused_blocks = l_unused_blocks, unused_bytes = l_unused_bytes, las t_used_extent_file_id = L_lastusedextfileid, last_used_extent_block_id = L_lastusedextblockid, Last_used_blo ck = L_last_used_block); P (' Free Blocks ', l_free_blks); P (' Total Blocks ', l_total_blocks); P (' Total Bytes ', l_total_bytes); P (' Unused Blocks ', l_unused_blocks); P (' Unused Bytes ', l_unused_bytes); P (' last used Ext FileId ', L_lastusedextfileid); P (' last used Ext blockid ', l_lastusedextblockid); P (' Last used Block ', l_last_used_block); End

The process has been created.

The dump method for the data block of the non-segment header is similar to the above. The structure of the data block is different from the segment header block, which can be consulted if you need to know.

3.2 Extracting HWM information

3.2.1 HWM Location

The HWM position is calculated according to the following formula:

HWM = useed byte = Total bytes-unused Blocks

Total bytes and unused blocks can be extracted using show_space.

HWM information can also be obtained through analyze tables. The Dba_tables view contains columns that you can use for each table spatial analysis. Where blocks represents unused space for blocks that have been used, i.e. hwm,empty_blocks.

3.2.2 HWM Space Utilization Information

To compare the block number of blocks with data rows and the total number of blocks under high water mark, you can use the following formula to show the proportion of unused space under HWM.

p = 1-r/h

R: Block number of blocks with data rows

The number of blocks under H:HWM.

R can be obtained by the following methods:

Oracle7:select count (Distinct substr (ROWID, 15,4) | | substr (ROWID, 1,8)) from Schema.table;oracle8 and Oracle9:select cou NT (Distinct substr (ROWID, 7,3) | | substr (ROWID, 10,6)) from schema.table;

If the formula calculates the result p is 0, you do not need to rebuild the table. If the result p is greater than 0, you should consider the system condition and application needs to determine whether the total group table is required.

4. Optimization

4.1 Reclaim Storage space manually

Blocks above the high water mark have no effect on performance, but can consume space. How space size is a matter of consideration, you can decide to reclaim empty blocks. Assuming table T1 is stored in 2.5, use ALTER TABLE ... The deallocate unused statement can reclaim space above HWM. Like what:

ALTER TABLE T1 DEALLOCATE unused;

If the Alter TABLE ... Deallocate The Keep keyword is used in the unused statement, you can keep free space of the specified size after the HWM, for example:

ALTER TABLE T1 deallocate unused keep 10K;

4.2 Limitation of the table

The use of the following blocks can be obtained according to 3.2.1 HWM. How p is greater than when the full table scan performance will have an impact, while also consuming space.

If it is possible to confirm that the application has a good index that does not use a full-table scan, then the empty blocks below high water mark will not have an impact on access, although it is space-intensive. If you are unsure, you need to consider the table of limitations.

Deleting a table deletes all records in the table and resets the HWM tag. The table will become an empty table after the deletion.

There are only two ways to delete a table in Oracle:

1. Using the DROP statement

Use the drop statement to delete the entire table before rebuilding the table. During the delete-rebuild process, all indexes, integrity constraints, and triggers associated with the table are lost, and all objects that depend on the table become invalid state, and the original contention against the table is invalidated. Therefore, it is too expensive to delete the records in the table in this way.

2. Using the TRUNCATE statement

The TRUNCATE statement belongs to a DDL statement and does not produce any fallback information and is immediately automatically committed. The execution of the TRUNCATE statement does not affect any database objects and authorizations associated with the truncated table, nor does it touch the triggers defined in the publication. In addition, when the subscript is truncated, the HWM will be reset and the storage space allocated for the table will be reclaimed.

When you execute a TRUNCATE statement, you can control whether the freed area is reclaimed into a tablespace by using the drop storage clause and the REUSE storage clause. How to make the online system truncate, do not want to lock the table for a long time, then you can use the Reuse storage clause, only HWM reset.

4.3 Free list optimization

The free list competition occurs when multiple processes use the same free list and try to modify the free list header data block at the same time. You can check the contention by querying the view V$waitsate's class type as data Block records.

The main reason for the contention of the data block type is that multiple processes attempt to modify the free list header data block at the same time. However, it also occurs when the process is ready to read the block into buffer cathe, and another process needs to access the same block. If you can capture the buffer busy waits in v$session_wait, you can determine the category by querying the P3 in v$session_wait. A 0 or 1014 represents the read type, and the other value is the type that modifies the competition.

The next step is to determine which segments the competition involves. If you can capture waits in v$session_wait, you can find the segment name in dba_extents with the values of P1 and P2 (corresponding to file and block). How to be a table, you will most likely need to rebuild the table to create more process freelists. One way to calculate how many freelist to create is to dump blocks close to HWM in some segments, checking the number of interested transaction lists, as described in Figure 3.1. The peak of the number of interested transactions plus 1 is the minimum required process freelists value.

As you can see from 2.3 and 2.4, using multiple free lists can result in more empty blocks being unused, and may cause segments to expand more quickly. If performance is the focus of the current concern, then many free lists can be used to increase concurrent access and, of course, increase the consumption of additional space. However, if the space use size is the first factor to consider, it is recommended to use single freelist, so that the parameters Freelists=1, of course, can not improve the performance of concurrent transactions.

V$waitstat can also show competition for other types of classes, including the segment header and the free list. Occurs when multiple transactions in the same free list group need to update their free list header records at the same time. There are several ways to solve this problem, such as rebuilding the table with more free list groups, or increasing the _bump_highwater_mark_count size, or adjusting the application itself.