Shulou(Shulou.com)06/01 Report--

If no (physical_attributes_clause) parameters are specified when creating a table:

If you omit this clause, then Oracle Database sets PCTFREE to 10, PCTUSED to 40, and INITRANS to 1.

Oracle has deprecated the MAXTRANS parameter, which is automatically adjusted by default, with a maximum value of 255. 5.

Meet enq: TX-allocate ITL entry in the database

Solution method

It is normal to have a small amount of ITL waiting in the system, but only when it affects the system (such as awr report, this event is found in top 5 events), or directly affects the application (such as deadlock, and if a session is found to have a large number of waiting ITL), we need to take corresponding measures to deal with it. For different reasons leading to ITL waiting, we should take different measures to deal with it.

Insufficient INITRANS

This will only happen on the data block of the table, as in our example above: the number of ITL on the data block does not reach the MAX TRANS limit, and the free space is less than 24 bytes. Tables where this happens are usually UPDATE frequently, causing the reserved space (PCTFREE) to be filled up. If we find that this kind of ITL wait has affected the system, we can increase the INITRANS or PCTFREE of the table (depending on the amount of concurrent transactions on the table, generally, if the concurrency is high, it is recommended to increase the INITRANS first, otherwise, the increase of PCTFREE is preferred).

It is important to note that if you use ALTER TABLE to modify these two parameters, it will only affect the new data block, not the existing data block-- to do this, you need to export / import the data and rebuild the table.

Insufficient MAXTRANS

This situation is caused by high concurrency: the number of transactions on the same data block has exceeded the number of ITL actually allowed (as mentioned earlier, ITL slot cannot take up more than half of the block size, such as the 8K limit of 169K). Therefore, to solve this kind of problems, we need to start from the application, reduce the concurrency of transactions; long transactions, on the premise of ensuring data integrity, increase the frequency of commit, modify to short transactions, and reduce resource occupation events. For OLAP systems (for example, there is a data entry module with high concurrency), you can consider increasing the block size.

Recursive transaction ITL contention

This kind of waiting is usually caused by the frequent insertion and modification of data by concurrent transactions in the system, which is often accompanied by "enq: TX-index contention" events. The fundamental solution is to reduce index splitting, such as using big data blocks, reducing inefficient and inefficient fields in the index, and so on.

Physical_attributes_clause:

INITRANS integer:

Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1, with the following exceptions:

The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater.

The default value for an index is 2.

In general, you should not change the INITRANS value from its default.

Each transaction that updates a block requires a transaction entry in the block. This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.

The INITRANS parameter serves the same purpose in the statements that create and alter tables, partitions, clusters, indexes, materialized views, and materialized view logs.

MAXTRANS Parameter:

MAXTRANS "= this parameter has been deprecated.

In earlier releases, the MAXTRANS parameter determined the maximum number of concurrent update transactions allowed for each data block in the segment. This parameter has been deprecated. Oracle now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

Existing objects for which a value of MAXTRANS has already been set retain that setting. However, if you attempt to change the value for MAXTRANS, Oracle ignores the new specification and substitutes the value 255 without returning an error.

The content is extracted from:

SQL Language Reference---8 Common SQL DDL Clauses

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.