Oracle Freelist and HWM Principle Discussion and Related Performance Optimization
ZTE Tour Chongqing Institute
Key words: freeelist, hwm, storage parameters, segment, block, dump, optimization
Thesis:
Recently, the important role of Freelist gradually be known for Oracle DBA, and there are also some related discussions on the Internet. This article uses Freelist to discuss the principle of the storage management of Oracle, involving the principles of Oracle segment block management, and the Freelist algorithm. A reuse characteristic HWM closely related to FreeElist is closely related to SQL performance, and this article also provides a introduction. Based on the principle discussion, the commonly used storage parameter analysis method is introduced, and the storage optimization involved, the HWM optimization and the FreElist competition optimization are explained.
Abbreviate language:
ASSM: Auto Segment Space Management
HWM: High Water Mark
DBA: Data Block Address
OLTP: Online Transaction Process
OPS: Oracle Parallel Server
1 Introduction
Oracle's spatial management and storage parameter management is an important part of Oracle management and optimization. As the core parameters in the Oracle underlying storage parameters, Freelist has a significant impact on Oracle's storage management and performance optimization, while existing Oracle documents is relatively lacking. Although Oracle 9i has already appeared ASSM, it is still necessary to understand the Freelist for deep tuning.
Recently, the important role of Freelist gradually be known for Oracle DBA, and there are also some related discussions on the Internet. This article uses Freelist to discuss the principle of the storage management of Oracle, involving the principles of Oracle segment block management, and the Freelist algorithm. A reuse characteristic HWM closely related to FreeElist is closely related to SQL performance, and this article also provides a introduction. Based on the principle discussion, the commonly used storage parameter analysis method is introduced, and the storage optimization involved, the HWM optimization and the FreElist competition optimization are explained.
These principles analysis and performance optimization are based on exploration, limited to space and my own experience may have limitations, deviations or fallacies.
In order to accurately describe some of the structural and fields of the fields.
It is limited to this article that this article does not discuss the same important Block structure, free List Groups for OPS performance have important implications, so this article discusses in a single Free List Group. For the in-depth discussion of Block, the introduction of Free List Group and the optimization of important parameters such as PCTUSED and PCTFREE, see References and Information.
2. Discussion on principles
FreeList as a core parameter of an Oracle store management. Its behavior is controlled by Oralce, we generally do not need to master and control. But we may encounter these problems, when inserting a record, will it insert into that block? Is it a new block or an old block with data? When is the paragraph expanded, how to expand? There is only one record in the table, but the price of SELECT is thousands of blocks, why? If we know the way Oracle's storage management method is clear, it is clear and natural.
2.1 Oracle logical storage structure
Oralce's logical storage structure is managed in table space, segments, districts, blocks. The block is the most basic unit for managing the storage space, and the Oracle database is logically read in units when performing input and output operations. The area is composed of a series of continuous blocks, and Oralce is in the region based on spatial allocation, recycling, and management. The segment consists of multiple zones, which may be continuous or discontinuous, and the next object has a segment. Table space accommodation segment and zone. At the time of the generation segment, the initial extents will be assigned, the first block of the initial area is formatted into Segment Header, and is used to record free List description information, Extens information, HWM information, etc.
2.2 Free List Concept
Free List is a one-way linker for positioning blocks that receive data. In the tablespace of the dictionary management, Oracle uses free List to manage unallocated memory blocks. Oracle records blocks with free space for INSERT or UPDATE. Idle space comes from: 1. All blocks exceeding HWM, which have been assigned to segments, but have not been used. 2. All blocks under HWM and chain into the Bli List are reused. Free List has the following properties
l Flag indicates that free list is used (1) or unused (0)
l Free List chain's first address DBA (Data Block Address)
l Free List chain's address DBA
The information of Free List is usually retained in Segment Header, here is given to the Segment Header Block Dump clip:
NFL = 3, NFB = 1 TYP = 1 nxf = 0
Seg Lst :: flg: unused lhd: 0x00000000 LTL: 0x00000000
Seg Lst :: flg: used lhd: 0x03c00233 LTL: 0x03c00233
Seg Lst :: flg: used lhd: 0x03c00234 LTL: 0x03c00234
Seg Lst :: flg: unused lhd: 0x00000000 LTL: 0x00000000
Segment header:
==> NFL: Number of Free Lists / Block
==> NFB: NUMBER OF Free List Blocks Segment Header
==> TYP: block Type
==> NXF: Number of Transaction Free Lists
Segment List:
==> flg: flag buy or unused the free list
==> lhd: Head of free list
==> LTL: TAIL OF Free List
There is a tag FLG in each block to indicate if the block is chained into the Free List chain. If this flag is placed, the block is rearward to the DBA of the next block in the Free List chain. If the current block is the end-up block of the chain, the rearward point value is 0.
Here is a fragment of Block Dump on 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 of Last Block Cleanout
==> ITC Number of Itl Slots
==> flg o = on freeelist, - = not onfreelist
==> 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, it is A, B, C, D, E respectively.
It will form segment header-> a-> b-> c-> d-> e--
At the same time, segment header-> e
2.3 Free List Categories
There are 3 types of Free List, Master Freelists (PRFL), and Transaction Freelists.
2.3.1 Master Free List:
There is an Master Free List in each segment, automatically generates during segment. For each paragraph, there is such an idle space pool, which is common for each process, and the idle space is on the block of Master Free List. Since Master Free List is public, the Master Free List will increase when multiple processes are inserted into the same segment simultaneously.
2.3.2 Process Free Lists
In order to reduce the competition problem of Master Free List, another Free List is introduced to be called Process Free Lists, created according to the parameters in the create / alter. Such multiple Free Lists can share the management of idle space to improve OLTP applications Performance of spatial allocation management of the transaction when you are highly concurrently inserted and updated. Create Freelists, for example: Create Table flg (........................................................................................................................................................................................................................................................ The default Freelists is 1, and the Process Free Lists will not be created. When Freelists> = 2, create the Process Free Lis.
The process is selected by using the Process Free List, and the formula is as follows:
SELECT LIST Entry = (PID% NFL) 1
NFL: FREELISTS definition number of Process Free List
2.3.3 Transaction Free Lists
Dynamically created when Oracle needs. A Transaction Free List is a Free List that is used to give a transaction. There are at least 16 Transactions Free Lists per segment, and this value will grow when needed until the SEGMENT HEADER block is reached. One transaction will need to assign a TX Free Lists Entry: DELETE or Update when it is released in the case.
2.4 Free List behavior
2.4.1 FreeElist Link and Unlink Operation
Freelist is managed in a first out of the queue (LIFO). That is to say that the last block of Link to Freelist has the first opportunity to unlink. When the hollow space in the block is increased to greater than PCTFREE, the block is placed in FreeList. Blocks in Free List can be used to make Update or Insert. When there is not enough space in the block for INSERT operation and the use space is greater than the PCTUSED, the block is removed from the free list.
After the block is in the DELETE or UPDATE operation, if the space is used to PCTUSED, the block is again LINK to Free LIST. When the free List is added each time, it is the header of the LINK to the list.
For example: 120 block numbers in the consideration section are from 1 to 120. There are 6 blocks on the free List and assume that HWM is 80. (Block actually uses DBA numbers)
10-> 24-> 45-> 46-> 65-> 80- |
Now INSERT operations, you need 400 Bytes space. It is assumed that the block 10 is insufficient, but the block 24 is available space. Now the data is inserted into block 24, and now the remaining space of block 24 is less than the PCTUSED of the table. Therefore, block 24 is removed from the Free List linked list. The purpose of the PCTFree and PCTUSED parameter is to control the data block into / remove from the list of Free List. Now Free Lists like this:
10-> 45-> 46-> 65-> 80- |
Then use the data of the same segment in the same transaction, so that blocks 54 and 67 fall below PCTUSED. This block is now added to the Free List chain. Free list is like this:
67-> 54-> 10-> 45-> 46-> 65-> 80- |
2.4.2 Transaction Free List Algorithm
Scan all TX Free List in the Segment Header block, check if tx free list entry is not assigned to Transaction, how to find unused Entry or empty TX Free LIST that has already submitted transaction. If the above search process fails, the new Entry will open in the TX Free Lists area in the Segment Header block. If there is no space to generate, the transaction must wait for the release of Entry.
The maximum number of free lists in Segment Header:
Block size max # freeelists
---------------------------
2k 24
4K 50
8K 101
16K 204
The use of the DELETE or UPDATE released by the transaction T1:
l Reuse immediately by T1
l When T1 Commit is reused by other business, the process is reused, the process is, for example,:
2.5 HMW concept
The High Water Mark represents the largest (TOP LIMIT) block for a table. The High Water Mark has been mentioned in Segment Header in Segment Head, and is generally increased in Oracle inserting data (not always 5 blocks, see the HMW growth mode in the flowchart in 2.4.2).
The information about HWM in Segment Header Block is described below:
Extent Control:
Extent Header :: Spare1: 0 Space2: 0 #Extents: 13 #Blocks: 1429
Last Map 0x00000000 # Maps: 0 Offset: 4128
Highwater :: 0x020004d0 EXT #: 12 BLK #: 275 EXT SIZE: 475
#blocks in seg. HDR's FreeElists: 5 # blocks Below: 1229
Mapblk 0x00000000 offset: 12
Unlocked
==> spare1: this field is no longer used (old inc #, now always 0)
==> Space2: this field is no longer used (old ts #, now always 0)
==> #EXTENTS: Number of Extents Allocated to Segment
==> #blocks: Number of Blocks Allocated to segment
==> Last Map: Address of Last Extent Map Block
0 IF extent map is entirely in the segment header
==> #maps: Number of Extent Map Block
==> Offset: Offset to End of Extent Map
==> HWM DBA: Address of Block At Highwater Mark
==> Ext #: HWM EXTENT NUMBER RELATIVE TO Segment
==> BLK #: HWM Block Number Withnin Extent
==> Ext size: HWM EXTENT SIZE (IN Blocks)
==> #blocks in seg. HDR's Free List
==> #Blocks Below: Number of Blocks Below HWM
==> MapBLK DBA: DBA of Extent Map Block Containing HWM Extent
IS 0 if hwm is in the segment header
==> Offset: Offset With EXTENT MAP Block
Is The Ext # IF HWM IS in Segment Header
==> Locked by: if locked by a transaction, the xid is displayed
HWM can be said to be the boundary between the stored space that has been used and the unused storage space. During the table, the HWM is moving in one direction. HWM may move in the increasing direction when inserting records, but the HWM does not move in the opposite direction when deleting the record. See 2.4.2. The figure below shows the location of HWM in a certain data segment.
Figure 2.5
High Water Mark is important because it impact on full-tuning scan performance. When a full mesh scan is implemented, Oracle reads the blocks under all High Water Mark even if they are empty. When there are many unused blocks under the High Water Mark, implement full mete scans increase additional unnecessary I / O. It will also populate a lot of many spaces in the global shared area.
3. Analytical method
The storage parameters are basically something of Oracle Internal, so Oralce does not provide a good means to analyze. However, for DBA, some information can be obtained by block Dump and DBMS_SPACE.
3.1 Extract BLOCK and Free List Information
Create DBMS_SPACE use stored procedure 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 varcha2, 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,
Last_used_extent_file_id => l_lastusedexTfileId,
Last_used_extent_block_id => l_lastusedextBlockId,
Last_used_block => 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 buy ext blockid', l_lastusedextblockid);
p ('Last Used Block', L_last_USED_BLOCK);
END;
The process has been created.
SQL> CREATE TABLE T1 (a char (1000)) Storage (freeelists 3); Table has been created.
SQL> SET ServerOutput on;
SQL> EXEC SHOW_SPACE ('T1');
Free blocks ........................... 0 <== Number of Blocks on Freelist
Total Blocks ........................... 5 <== Total Data Blocks in Segment
Total Bytes ............................. 20480 <== Total bytes in Segment
Unused blocks ......................... 4 <== Total Unused block in segment
Unused bytes .............................. 16384 <== Total unused bytes in segment
Last Used Ext FileID ............................... == file id of last used extent
Last Used Ext Blockid ................... 562 <== block id of last used extent
Last use block ......................... 1 <== last used block in eXtent
The PL / SQL process has been successfully completed.
Further use techniques for show_space can be refer to Document 5. The segment header block is dumps the SEGMENT HEADER Block using the data obtained above.
SQL> ALTER SYSTEM DUMP DATAFILE 15 block 562;
In udump / ora10792.trc
*** 2004-09-08 15: 29: 57.343
Start Dump Data Blocks TSN: 27 File #: 15 Minblk 562 Maxblk 562
Buffer TSN: 27 RDBA: 0x03C00232 (15/562)
SCN: 0x0000.064560E4 SEQ: 0x02 flg: 0x00 Tail: 0x60e41002
FRMT: 0x02 Chkval: 0x0000 Type: 0x10 = DATA Segment Header - Unlimited
Extent Control HEADER
-------------------------------------------------- ---------------
Extent Header :: Spare1: 0 Space2: 0 #Extents: 1 #Blocks: 4
Last Map 0x00000000 # Maps: 0 Offset: 2080
Highwater :: 0x03c00233 EXT #: 0 BLK #: 0 EXT SIZE: 4
#blocks in seg. HDR's FreeElists: 0
#Blocks Below: 0
Mapblk 0x00000000 offset: 0
Unlocked
Map header :: next 0x0000000000 #EXTents: 1 Obj #: 60033 Flag: 0x40000000
Extent Map ------------------------------------------------ -----------------
0x03c00233 Length: 4
NFL = 3, NFB = 1 TYP = 1 nxf = 0
Seg Lst :: flg: unused lhd: 0x00000000 LTL: 0x00000000
Seg Lst :: flg: unused lhd: 0x00000000 LTL: 0x00000000
Seg Lst :: flg: unused lhd: 0x00000000 LTL: 0x00000000
Seg Lst :: flg: unused lhd: 0x00000000 LTL: 0x00000000
End Dump Data Blocks TSN: 27 File #: 15 Minblk 562 MaxBlk 562
For the description of the fields in the above block, and related tests. This article is no longer listed in this article due to the space limit. Refer to Document 7.
The DUMP method for non-Segment Header's Data Block is similar. Data Block's Structure and Segment Header Block are different. If you need to understand, you can consult the reference documents and information.
3.2 Extract HWM Information
3.2.1 HWM location
The HWM location is calculated according to the formula below:
HWM = useed byte = Total Bytes - Unused Blocks
Total Bytes and Unused Blocks can be extracted with Show_Space.
HWM information can also be obtained by Analyze Tables. The DBA_TABLES view contains columns available to each tablespace analysis. Where Blocks represents HWM, EMPTY_BLOCKS represents unused space.
3.2.1 Space Using Information in HWM
To compare the number of blocks of the data line and the number of total blocks under the High Water Mark, the proportion of unused space under HWM can be displayed with the following formula.
P = 1- r / h
R: number of blocks with blocks of data lines
H: The number of blocks under HWM.
r can be obtained by the following method:
Oracle7:
Select Count (Distinct Substr (RowID, 15, 4) || Substr (RowID, 1, 8)) from schema.table;
Oracle8 and Oracle9:
Select Count (Distinct Substr (RowID, 7, 3) || Substr (RowID, 10, 6)) from schema.table;
If the result of the formula calculation is 0, it is not necessary to reconstruct the table. If the result P is greater than 0, you should consider whether the system status and application needs to decide whether the total group table is required.
4. Optimize
4.1 Handmade Recycling Storage Space
Blocks above the High Water Mark have no effect on performance, but it will cost space. How to spatially is a problem that considers, you can decide to recycle the empty block.
It is assumed that the storage diagram of the table T1 is shown in Figure 2.5, using the ALTER TABLE ... DEAALLOCATE UNUSED statement to reclaim the space above HWM. such as:
Alter Table T1 Deallocate Unused;
The storage schematic of T1 after recovery is shown in Figure 4.1.1
Figure 4.1.1
If the Keep keyword is used in the ALTER TABLE ... DEALLOCATE UNUSED statement, you can keep the specified size free space after HWM, such as:
Alter Table T1 Deallocate Unused Keep 10K;
The storage schematic of T1 after recovery is shown in Figure 4.1.2
Figure 4.1.2
4.2 Delete Table
According to 3.2.1, you can get the use of the blocks of the HWM. How to P is greater, affect full-table scan performance, while it will consume space. If it is possible to confirm that the application has a good index hard to use a full mete scan, then the empty block below High Water Mark, despite the space, it will not affect the access. If you are not sure, then you need to consider the deletion form.
The operation of the deletion will delete all the records in the table and reset the HWM tag. Table will become a holiday after the decreasy.
Deleting a reduction in Oracle is only two ways:
1. Use the DROP statement
First remove the entire table with the DROP statement, and then rebuild this table. During delete-reconstruction, all indexes, integrity constraints, and triggers associated with the table are lost, and all objects that depend on the table will become Invalid status, and the authorization of the original pairing table will also be invalid. So use this way to delete the record price in the table is too large.
2. Use truncate statements
The TRUNCATE statement belongs to the DDL statement and does not generate any fallback information, and is automatically submitted immediately. Do not affect any database objects and licenses associated with the deleted table when performing a TRUNCATE statement, and the triggers defined in the table are not triggered. Further, when the target is deleted, the HWM will reset, and the storage space that has been assigned to the table will be reclaimed.
When executing a TRUNCATE statement, you can control whether the released area is reclaimed into the table space by the Drop Storage clause and the Reuse Storage clause. How to make a TRUNCATE of the online system, do not want the top length to lock, then you can use the Reuse Storage clause, only HWM reset.
4.3 Free List Optimization
Free list competition appears in multiple processes using the same Free List and trying to simultaneously modify the Free List header data block. You can check the competition by querying the Class type of the view V $ Waitsate.
The main reason for generating Data Block type competition is that multiple processes are trying to simultaneously modify the Free List header data block. However, it will also appear when the process is ready to read the block into the buffer CATHE, and another process needs to access the same block. If you can capture Buffer Busy Waits in V $ SESSION_WAIT, you can determine that the P3 in V $ SESSION_WAIT is to be determined. A 0 or 1014 represents a read type, and other values are the type of competition.
The next step needs to be determined to compete involve those paragraphs. If you can capture Waits in V $ SESSION_WAIT, you can use the value of P1 and P2 (corresponding file and block) in DBA_EXTENTS. How to be a table, it is likely to rebuild the table to create more Process FreeElists. One method for calculating how many freeelist needs to create is a block close to HWM in DUMP, check the number of interested transaction lists, for specific methods, see 3.1. The peak plus 1 of the Interested Transactions is 1 is the value of the minimum process freelists.
As can be seen from 2.3 and 2.4, using multiple Free List may result in more empty blocks unused, or may result in faster expansion. If performance is the focus of current concern, so many Free Lists can be used to increase concurrent access capabilities, of course, will increase the consumption of additional spaces. However, if the size of the space is first considering, it is recommended to use Single Freelist to make the parameters Freelists = 1, of course, it is not possible to improve the performance of concurrency transactions.
V $ WaitStat also shows the competition of other types of Class, including Segment Header and Free List. When multiple transactions that appear in the same Free List Group requires simultaneous updates their Free List header records. There are a variety of ways to solve this problem such as the rebuild table uses more free list groups, or adds _bump_highwater_mark_count size, or adjust the application itself. References and information:
1. "Freelists and freelist groups. Scope & application"
2. "INITRANS, MAXTRANS, FREELISTS AND FREELIST GROUPS, PCTFREE AND PCTUSED", MIKE AULT
3. "Freelist Internals: An Overviewknowledge", Xpert for Oracle Administration
4. "Blockdump - 8.x data segment header in oracle"
5. "Asktom dbms_space_free_space", http://asktom.roacle.com
6. "Data Blocks and Freelists", http://www.ixora.com.au
7. "Physical Structure of Data Block", http://www.itpub.net
8. "Oracle 9i for Windows NT / 2000 Data System Training Course", Tsinghua University Press
Some of the above articles can be found on my blog website http://blog.9cbs.net/youbo2004, which is very good for researching Free List, Free List Group, and Block.
