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

Problem description

An alarm occurs during the execution of update:

ORA-00600: internal error code, parameters: [4521], [11], [18446744073606860068], [11], [399824003], [18446744073709551615], []

Related concepts ITL description

ITL (Interested Transaction List) is a part of the Oracle data block, which is located in the data block (block header). Itl is composed of xid,uba,flag,lck and scn/fsc, which is used to record all transactions in the block. An itl can be regarded as a transaction record. Of course, if the transaction has been committed, then the location of the itl can be used repeatedly, because the itl is similar to records, so it is sometimes called the itl slot. If a transaction has not been committed, then the transaction will always occupy an itl slot, and the itl records the transaction information, the entry of the rollback segment, the transaction type, and so on. If the transaction has been committed, then the itl slot also holds the SCN number when the transaction was committed.

The minimum number of ITL is 1, which is controlled by the parameter initrans (because of compatibility, oracle allocates two itl in the storage block of the object, so the minimum value of initrans is actually 2), the maximum value is 255.It is controlled by the parameter maxtrans, and the maximum parameter cannot be modified after 10g. Itl is a concept of block level, and an itl occupies the space of block 46B. The parameter initrans means that a portion of the storage space except block header in the block cannot be recorded (46B*initrans). When there is still a certain free space in the block, oracle can use free space to build itl for transaction use. Without free space, itl waiting may occur because the block cannot allocate a new itl.

Give a brief description of the flow of the transaction

To start a transaction, first Oracle assigns XID to the transaction, finds a rollback segment, stores the transaction information in the rollback segment header block, assigns a undo block to the transaction, and writes the address of the undo block to the transaction table (UBA address).

The transaction is ready to modify a data block and write transaction information (XID, UBA (this UBA points to the corresponding undo block) in the transaction slot of the header of the data block.

Start modifying the data, storing the pre-modified image of the block in the undo block.

Problem information acquisition

Generally speaking, ORA-600 errors are recorded in the background. Here are the records we found.

According to the contents of the record, we can get the corresponding trace file. If we look carefully, we can see the following information.

Exception statement (the part covered in red is the statement, which is not shown here because the customer statement is involved and has nothing to do with this blog)

The functions involved

Reasons for ERROR

24 ITL slots are occupied

A large number of row locks are generated.

Cause analysis

According to mos, the comparison phenomenon is found.

Version compound, error consistent

Function calls are consistent

The reason is that the event is caused by the full ITL transaction slot.

Solution

Increase the ITL capabilities of the table or index by recreating the table or index and modifying the INITRANS or PCTFREE parameters so that more concurrent transactions can be handled.

Add INITRANS

Depending on the number of transactions in the table, we need to change the initial value. Change here to 50:

Alter table INITRANS 50

Then reorganize the table using move (alter table move;)

Then rebuild all the indexes of this table, as shown below

Alter index rebuild INITRANS 50

Add PCTFREE

If adding INITRANS doesn't solve the problem, try adding PCTFREE. Increasing the PCTFREE leaves more space, so spread the same number of rows over more blocks. Overall, this means that more ITL slots are available.

Extending rows to more blocks also helps to reduce this wait event.

Alter table PCTFREE 20

Then reorganize the table using move (alter table move;)

Rebuild the index

Alter index rebuild PCTFREE 20

Increase the combination of INITRANS and PCTFREE

Set INITRANS to 50 and pctbrush free to 20.

Alter table PCTFREE 20 INITRANS 50

Reorganize tables using move (alter table move;)

Then rebuild all the indexes of the table, as follows

Alter index rebuild PCTFREE 20 INITRANS 50; reference

ORA-00600: Internal Error Code, Arguments: [4521] (Doc ID 2648741.1)

Troubleshooting waits for 'enq: TX-allocate ITL entry' (Doc ID 1472175.1)

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.