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

This wait occurs when the ITL setting of the table does not meet the requirements of concurrent transactions. Data blocks are the smallest iUnix units that oracle can emit. In a data block, whenever a transaction needs to modify a data block, it needs to obtain an available ITL slot in the data block header, which records the id of the current transaction, the undo data block used, and the corresponding scn, whether the transaction is committed or not. Furthermore, the setting of ITL slots is determined by ini_trans,max_trans. After 10g, the nax_trans parameter is ignored.

SQL > create table trans_test (id number,name varchar2) initrans 1 maxtrans 1

Table created

SQL > select ini_trans,max_trans,table_name from user_tables a where a. TRANSs test

INI_TRANS MAX_TRANS TABLE_NAME

1 255 TRANS_TEST

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.

For ini_trans, the default value for max_trans is 1 at table level and 2 at index level. Generally speaking, there is no need to make special settings. Can be configured according to the needs of the business.

The following settings are available for reference:

For tables with large tables with tens of thousands of data levels, initrans recommends that they be set to 8: 16.

For intermediate tables, the amount of data ranges from 1 million to 10 million, and initrans recommends that it be set to 4: 8.

For normal tables, initrans recommends that you set it to 1: 4.

There are three ways to solve this waiting event:

Increase INITRANS

1) Depending on the number of transactions in the table we need to alter the value of INITRANS.

Here it has been changed to 50:

Alter table INITRANS 50

2) Then re-organize the table using move (alter table move;)

3) Then rebuild all the indexes of this table as below

Alter index rebuild INITRANS 50 Increase PCTFREE

If the issue is not resolved by increasing INITRANS then try increasing PCTFREE. Increasing PCTFREE holds more

Space back and so spreads the same number of rows over more blocks. This means that there are more ITL slots

Available overall:

1) Spreading rows into more number of blocks will also helps to reduce this wait event.

Alter table PCTFREE 40

2) Then re-organize the table using move (alter table service_T move;)

3) Rebuild index

Alter index index_name rebuild PCTFREE 40 Teng A Combination of increasing both INITRANS and PCTFREE

Alter table PCTFREE 40 INITRANS 50

2) Re-organize the table using move (alter table move;)

3) Then rebuild all the indexes of the table as below

Alter index rebuild PCTFREE 40 INITRANS 50

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.