怎么理解Oracle中INITRANS和MAXTRANS參數

怎么理解Oracle中INITRANS和MAXTRANS參數，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

INITRANS：
INITRANS 指的是一個 BLOCK 上初始預分配給并行交易控制的空間 (ITLs)
( 當 BLOCK 上某筆 ROW 被交易更新鎖定時，會在 BLOCK header ITL allocate 一個鎖，當下一個交易要更新同一筆 row 時，就會發現他已經被先前的交易持有鎖了，會先去檢查該交易是否 active? 如果是，后來的該筆交易就會被 blocking ，等待 ) 如果一個表格需要同時有大量交易存取，你應該設定 INITRANS 大一點，可以減少 ITL 還要動態擴充的 Overhead 。

For tables INITRANS defaults to 1 for indexes 2

MAXTRANS：
MAXTRANS 指的是如果 INITRANS 空間不夠用了，就會自動擴展 ITL ，直到最大值也就是 MAXTRANS 值為止，預設是 255 。但是，如果 BLOCK 空間已經不足，也有可能無法持續擴充到 255 個 ITS 空間喔。

每個塊都有一個塊首部。這個塊首部中有一個事務表。事務表中會建立一些條目來描述哪些事務將塊上的哪些行/元素鎖定。這個事務表的初始大小由對象的INITRANS 設置指定。對于表，這個值默認為1（索引的INITRANS 默認為2）。事務表會根據需要動態擴展，最大達到MAXTRANS 個條目（假設塊上有足夠的自由空間）。所分配的每個事務條目需要占用塊首部中的23～24 字節的存儲空間。注意，對于Oracle 10g，MAXTRANS 則會忽略，所有段的MAXTRANS 都是255。

也就是說，如果某個事物鎖定了這個塊的數據，則會在這個地方記錄事務的標識，當然那個事務要先看一下這個地方是不是已經有人占用了，如果有，則去看看那個事務是否為活動狀態。如果不活動，比如已經提交或者回滾，則可以覆蓋這個地方。如果活動，則需要等待(閂的作用)

所以，如果有大量的并發訪問使用的這個塊，則參數不能太小，否則資源競爭將導致系統并發性能下降。

測試了一下ORACLE 并發事務的時候的塊分配和ITL 管理,
略去大部分的測試過程,大概的結果小結如下:
1. INITRANS =1 時 并發多個INSERT 事務(本次測試最多5個)的時候并不會由于ITL的爭用而等待組塞,ORACLE 采取的策略是每個INSERT事物已經操作完成，屬于不活動事物，只等待commit或者rollback,這樣各個會話之間就不會產生沖突，除非段沒有多余的塊(次種情況與本次的主題無關).

2.INITRANS =1 時 并發多個UPDATE事務(本次測試最多7個)的時候也不會由于ITL的爭用而導致等待產生,此時ORACLE除了使用默認的ITL之外，另外動態擴展所需要的ITL，緊緊在非常極端的情況下才會出現等待，(當然應用層面的死鎖或等待與本主題無關)。
1) 該BLOCK沒有FREE空間了，注意FREE參數的設置不能太小。
2) 該塊使用的ITL總數，超過該塊允許的ITL的最大值min(round(block_size*0.5/24) - 2 ,255) 。
   要達到這樣的極端情況實際的生產情況是很難的，應該比業務SQL的死鎖出現的概率更小。

小結：創建表的時候除非已經清楚，大部分的情況下沒有必要調整INITRANS參數，通常1-4以下足夠用了，INITRANS 設置非常大的時候ORACLE 有出現壞塊的BUG，另外FREE 參數倒是要注意不能隨意改小,除非你已經很清楚更改的后果．


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

Table created.

SQL>
SQL> create table a 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.


另開一個窗口插入：
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;

再開一個窗口插入：
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;

查詢：
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>    

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。