Shulou(Shulou.com)05/31 Report--

How to understand the INITRANS and MAXTRANS parameters in Oracle, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

INITRANS:

INITRANS refers to the space on a BLOCK that is initially pre-allocated to parallel transaction control (ITLs).

When a ROW on BLOCK is locked by a transaction update, there will be a lock in BLOCK header ITL allocate. When the next transaction updates the same row, it will find that it is already locked by the previous transaction, and will first check whether the transaction is active? If so, the later transaction will be blocking, wait) if a table needs to have a large number of transaction access at the same time, you should set the INITRANS to be larger, which can reduce the ITL and dynamically expand the Overhead.

For tables INITRANS defaults to 1 for indexes 2

MAXTRANS:

MAXTRANS means that if there is not enough INITRANS space, the ITL will be automatically extended until the maximum value is the MAXTRANS value, which is set to 255by default. However, if there is not enough BLOCK space, it may not be able to continue to expand to 255ITS space.

Each block has a block header. There is a transaction table in the header of this block. 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 (the INITRANS of the index defaults to 2). The transaction table dynamically expands as needed, up to a maximum of MAXTRANS entries (assuming there is enough free space on the block). Each allocated transaction entry takes up 23 million 24 bytes of storage space in the header of the block. Note that for Oracle 10g maxtrans, the MAXTRANS of all segments is 255.

That is to say, if something locks the data of this block, the identification of the transaction will be recorded in this place. Of course, that transaction needs to see if the place is already occupied, and if so, check to see if the transaction is active. If there is no activity, such as a commit or rollback, you can overwrite this place. If active, you need to wait (role of the latch)

Therefore, if there are a large number of concurrent accesses using this block, the parameters should not be too small, otherwise resource competition will lead to a decline in system concurrency performance.

Tested block allocation and ITL management for ORACLE concurrent transactions

Omitting most of the testing process, the approximate results are summarized as follows:

1. When INITRANS = 1, multiple INSERT transactions (up to 5 in this test) will not wait for group congestion due to ITL contention. The strategy adopted by ORACLE is that each INSERT transaction has been operated and belongs to an inactive thing, waiting only for commit or rollback, so that there is no conflict between sessions, unless there are no extra blocks in the segment (the second case has nothing to do with this topic).

When 2.INITRANS = 1, multiple UPDATE transactions (up to 7 in this test) will not cause waiting due to ITL contention. In this case, in addition to using the default ITL, the ITL required for dynamic expansion will wait only in very extreme cases (of course, the deadlock or wait at the application level has nothing to do with this topic).

1) there is no FREE space for the BLOCK. Please note that the setting of the FREE parameter cannot be too small.

2) the total number of ITL used by the block exceeds the maximum ITL allowed by the block min (round (block_size*0.5/24)-2255).

It is difficult to achieve such an extreme situation and the actual production situation should be less likely to occur than the deadlock of the business SQL.

Summary: when creating a table, unless it is already clear, there is no need to adjust the INITRANS parameters in most cases, usually 1-4 is enough, when the INITRANS setting is very large, the ORACLE has a bad block of BUG, in addition, the FREE parameter should not be changed at will, unless you are already clear about the consequences of the change.

SQL > create table xx (x number) storage (initial 64k next 64k) initrans 2

Table created.

SQL >

SQL > create table an as select * from xx

Table created.

SQL > select dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from xx

No rows selected

SQL > INSERT INTO XX SELECT 11 FROM DUAL

1 row created.

SQL > select SEGMENT_NAME,EXTENT_ID,BLOCKS,BYTES from user_extents where segment_name = 'XX'

SEGMENT_NAME EXTENT_ID BLOCKS BYTES

--

XX 0 8 65536

SQL > select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_name='XX'

TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE

XX VALID 10 40 2 255 65536 65536 1 2147483645

SQL >

SQL > INSERT INTO a SELECT 11 FROM DUAL

1 row created.

SQL > commit

Commit complete.

SQL > select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_name='A'

TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE

A VALID 10 40 1 255 65536 1048576 1 2147483645

SQL > select SEGMENT_NAME,EXTENT_ID,BLOCKS,BYTES from user_extents where segment_name ='A'

SEGMENT_NAME EXTENT_ID BLOCKS BYTES

--

A 0 8 65536

SQL > INSERT INTO XX SELECT 12 FROM DUAL

1 row created.

SQL > INSERT INTO a SELECT 12 FROM DUAL

1 row created.

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from xx

X FILE# BLOCK#

11 1 94665

12 1 94665

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from a

X FILE# BLOCK#

11 1 102801

12 1 102801

SQL > INSERT INTO XX SELECT 13 from dual

1 row created.

SQL > INSERT INTO a SELECT 13 FROM DUAL

1 row created.

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from xx

X FILE# BLOCK#

11 1 94665

12 1 94665

13 1 94665

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from a

X FILE# BLOCK#

11 1 102801

12 1 102801

13 1 102801

SQL > INSERT INTO XX SELECT 14 from dual

1 row created.

SQL > INSERT INTO a SELECT 14 FROM DUAL

1 row created.

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from xx

X FILE# BLOCK#

11 1 94665

12 1 94665

13 1 94665

14 1 94665

SQL >

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from a

X FILE# BLOCK#

11 1 102801

12 1 102801

13 1 102801

14 1 102801

SQL > commit

Commit complete.

SQL > INSERT INTO XX SELECT 15 from dual

1 row created.

SQL > INSERT INTO a SELECT 15 from dual

1 row created.

SQL > INSERT INTO XX SELECT 16 from dual

1 row created.

SQL > INSERT INTO a SELECT 16 from dual

1 row created.

SQL > commit

Commit complete.

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from xx

X FILE# BLOCK#

11 1 94665

12 1 94665

13 1 94665

14 1 94665

15 1 94665

16 1 94665

6 rows selected.

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from a

X FILE# BLOCK#

11 1 102801

12 1 102801

13 1 102801

14 1 102801

15 1 102801

16 1 102801

6 rows selected.

SQL >

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from xx

X FILE# BLOCK#

11 1 94665

12 1 94665

13 1 94665

14 1 94665

15 1 94665

16 1 94665

21 1 94665

22 1 94665

23 1 94665

24 1 94665

25 1 94665

X FILE# BLOCK#

26 1 94665

31 1 94665

32 1 94665

33 1 94665

34 1 94665

35 1 94665

36 1 94665

18 rows selected.

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from a

X FILE# BLOCK#

11 1 102801

12 1 102801

13 1 102801

14 1 102801

15 1 102801

16 1 102801

21 1 102801

22 1 102801

23 1 102801

24 1 102801

25 1 102801

X FILE# BLOCK#

26 1 102801

31 1 102801

32 1 102801

33 1 102801

34 1 102801

35 1 102801

36 1 102801

18 rows selected.

Open another window to insert:

INSERT INTO XX SELECT 21 from dual

INSERT INTO XX SELECT 22 from dual

INSERT INTO XX SELECT 23 from dual

INSERT INTO XX SELECT 24 from dual

INSERT INTO XX SELECT 25 from dual

INSERT INTO XX SELECT 26 from dual

INSERT INTO A SELECT 21 from dual

INSERT INTO A SELECT 22 from dual

INSERT INTO A SELECT 23 from dual

INSERT INTO A SELECT 24 from dual

INSERT INTO A SELECT 25 from dual

INSERT INTO A SELECT 26 from dual

Commit

Open another window to insert:

INSERT INTO XX SELECT 31 from dual

INSERT INTO XX SELECT 32 from dual

INSERT INTO XX SELECT 33 from dual

INSERT INTO XX SELECT 34 from dual

INSERT INTO XX SELECT 35 from dual

INSERT INTO XX SELECT 36 from dual

INSERT INTO A SELECT 31 from dual

INSERT INTO A SELECT 32 from dual

INSERT INTO A SELECT 33 from dual

INSERT INTO A SELECT 34 from dual

INSERT INTO A SELECT 35 from dual

INSERT INTO A SELECT 36 from dual

Commit

Query:

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from xx

X FILE# BLOCK#

11 1 94665

12 1 94665

13 1 94665

14 1 94665

15 1 94665

16 1 94665

21 1 94665

22 1 94665

23 1 94665

24 1 94665

25 1 94665

X FILE# BLOCK#

26 1 94665

31 1 94665

32 1 94665

33 1 94665

34 1 94665

35 1 94665

36 1 94665

18 rows selected.

SQL > select x, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from a

X FILE# BLOCK#

11 1 102801

12 1 102801

13 1 102801

14 1 102801

15 1 102801

16 1 102801

21 1 102801

22 1 102801

23 1 102801

24 1 102801

25 1 102801

X FILE# BLOCK#

26 1 102801

31 1 102801

32 1 102801

33 1 102801

34 1 102801

35 1 102801

36 1 102801

18 rows selected.

SQL > select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_NAME IN ('XX','A')

TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE

A VALID 10 40 1 255 65536 1048576 1 2147483645

XX VALID 10 40 2 255 65536 65536 1 2147483645

SQL >

SQL > select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,PCT_FREE,STATUS from dba_indexes where table_owner='SYS' and table_name IN ('XX','A')

OWNER INDEX_NAME TABLE_OWNE TABLE_NAME UNIQUENES INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_FREE STATUS

-- --

SYS IDX_XX SYS XX NONUNIQUE 2 255 65536 1048576 1 2147483645 10 VALID

SYS IDX_A SYS A NONUNIQUE 2 255 65536 1048576 1 2147483645 10 VALID

SQL >

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.