Oracle Pctfree,pctused,initrans,maxtrans

Reproduced in

Pctfree
When a block's usage space reaches the 100-pctfree value, the block is detached from the free list. For example, Pctfree is 10, then when the block uses 90% it will be out of freeelist, and subsequent inserts will not use this block.
If DML is often required after data in a table is inserted, we need to set the Pctfree parameter to a larger size to avoid the occurrence of row and row links. If a table is not modified or deleted after inserting the data, then we can not set the pctfree smaller, such as 5, or even smaller, so that each block of the table can contain more records, thus reducing the cost of accessing this table. And if there is a table, the block of hot block conflict is very serious, we can also increase the pctfree to reduce the number of records in each data block, thereby mitigating the hot block conflict. In fact, the better way to reduce the heat block conflict is to put this table in the block_size smaller table space, but in the actual production environment we often find it difficult to be so fortunate that we can only solve the problem by means of expediency when we discover that the hot block conflict exists.
When adjusting the pctfree, we should note that this parameter can be dynamically adjusted, but we adjust the Pctfree parameter only to the new data insert function, for already filled with old block of data, is not working, to completely solve the problem of row migration, must adjust the parameters, The table is reorganized to work on all data blocks in the table. There are many ways to reorganize tables, such as ALTER table ... MOVE or Exp/imp.
Since the default pctfeee is 10, if set to a new value of 0, or 1, you can save nearly 10% of the space, and read the disk faster. For example Pctfeee=0, the original data placed on the 100 extent, now as long as the 90 extent on it.
For example, create a very simple table:
CREATE TABLE Test_space
(
Name varchar2 () NOT NULL
)
Tablespace crmii
Pctfree 0
Initrans 2
Storage
(Initial 64K
Next 1K
Minextents 1
Maxextents Unlimited
);
--ADD comments to the columns
Comment on column test_space. Name is ' name ';
Since Oracle recommends that we use extent management local segment Space Managment Auto, we will be aware of Pctfree when we build the table. So for a permanent table, pctused can basically enter the historical stage because the temporary table does not have the storage parameter part.
If you want to change the Pctfree parameter after the object is created, you can use the following command
sql> ALTER TABLE T1 Pctfree 20;
Table altered.
The Pct_free parameter values can be viewed through dba_tables,dba_indexes, such as the following
Sql> Select Table_name,pct_free from user_tables where table_name= ' T1 '
TABLE_NAME Pct_free
--------------- ----------
T1 20
Initrans
Each block has a block header. There is a transaction table in the header of this block. Some entries are created in the transaction table to describe which transactions lock which rows/elements on the block. The initial size of this transaction table is specified by the object's Initrans setting. For tables, this value defaults to 1, and the index's Initrans defaults to 2. The transaction table expands dynamically as needed, up to Maxtrans entries (assuming there is enough free space on the block). Each assigned transaction entry needs to occupy the storage space of the 23~24 bytes in the block header. Note that for Oracle 10g,maxtrans it is ignored and all segments are Maxtrans 255.

That is, if something locks the data of this block, the identity of the transaction is recorded in this place, and of course the transaction must first look at whether the place is already occupied, and if so, to see if that transaction is active. If you are inactive, such as committed or rolled back, you can overwrite this place. If the activity, then need to wait (latch effect)
Therefore, if there is a lot of concurrent access to use this block, then the parameters can not be too small, otherwise the resource competition will cause system concurrency performance degradation.
Test the block allocation and ITL management of Oracle concurrent transactions,
Omitting most of the testing process, the approximate results are summarized as follows:
1. Initrans = 1 o'clock Concurrent multiple insert transactions (up to 5 of this test) do not wait for the group to be plugged due to ITL contention, and the strategy that ORACLE takes is to use a number of blocks for each insert thing assignment, so that there is no conflict between sessions. Unless the paragraph has no extra blocks (the second case is irrelevant to the topic).
2.INITRANS = 1 O'Clock Concurrent multiple update transactions (up to 7 of this test) will not cause the wait due to ITL contention, when Oracle, in addition to using the default ITL, dynamically expands the required ITL, tightly in the very extreme circumstances will be waiting, (Of course, application-level deadlocks or waits are irrelevant to this topic.)
1) The block does not have free space, and note that the settings for the parameters cannot be too small.
2) The total number of ITL used by the block exceeds the maximum allowable value of the ITL for the Block min (round (BLOCK_SIZE*0.5/24)-2, 255). To achieve this extreme situation the actual production situation is difficult and should be less likely than the deadlock of the business SQL.
Summary: When the table is created unless it is clear that most of the time there is no need to adjust the Initrans parameter, usually 1-4 is enough, the Initrans set very large when Oracle has a bad block of the bug, and the free parameter is to be careful not to change the small, Unless you already know the consequences of the change.
As a basic common sense, it is necessary to understand Initrans. Initrans, the initial table for handling row-level lock transactions in each block is 1, the index is 2, and if there are many rows in the same piece that are concurrently updated, it is recommended that the value be increased to serializable isolation level, at least 3. But in fact, it is not appropriate to modify the default value because the transaction needs to write the transaction entry information on the block, which means that the larger the Initrans, the greater the amount of space to consume and the longer the time.
Pctused
When a block undergoes a delete operation that reduces the used space to the pctused value, the block is re-added to the freelist, and subsequent inserts will use the block.
Maxtrans
In the old version is to control the maximum concurrent transactions, but the new version has been automatically so processed, if necessary can also be set, this parameter and pctused, basically can be eliminated!
Summarize:
Pctfree is important, but for a query system you can basically set it to 0.
pctused since 10g Oracle uses bitmap-to-segment and data block management, this parameter can be eliminated basically
Initrans, it's important, but if it's not a special setting, don't change it.
Maxtrans, at least 10g or above, belongs to the elimination ranks.

This article is from the original link: http://www.net527.com/Oracle_shujuku/Oracle_renzheng/5911.html

Oracle Pctfree,pctused,initrans,maxtrans