TX:ITL LOCK(INITRANS,MAXINTRANS)

今天和老周老肖吃飯之於談論了一個問題，就是INITRANS,MAXINTRANS對高併發量的資料塊的影響。
大家都知道在進行大量DML對同一個塊的時候（不同行），不會出現TX:ROW LOCK，但是由於ITL的限制
這樣的操作可能出現TX:ITL LOCK(MODE=4),以前我遇到過各種TX TM，但是ITL確實沒有遇到過，我記得
10G的不管ASSM還是MSSM都是預設的最打MAXTRANS為255，所以沒怎麼關注，因為如果要達到這個值需要
255個TRANSACTION對同一個塊進行修改，基本不可能。
但是我卻忽略了一點：
DIS402 3-19
Before the block reaches  PCTFREE, the free space is
used both for insertion of new rows and by the growth of the data block header.
可以發現PCTFREE除了儲存可能的UPDATE資料還儲存擴張的BLOCK header。

測試：
SQL>  create tablespace testo1
  2   datafile '/oradata/xuexi/XUEXI/datafile/testo1.dbf' size 50m segment space management AUTO;
SQL>  create tablespace testo2
  2   datafile '/oradata/xuexi/XUEXI/datafile/testo2.dbf' size 50m segment space management manual;
SQL>   create table test2
  2    initrans 100 maxtrans 200
  3    tablespace testo1
  4    as
  5    select * from dba_users;
 
Table created
 
SQL>
SQL>  create table test3
  2    initrans 100 maxtrans 200
  3    tablespace testo2
  4    as
  5    select * from dba_users;
  SQL> select TABLE_NAME,FREELISTS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS from user_tables where table_name in('TEST2','TEST3');
 
TABLE_NAME                      FREELISTS   PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS
------------------------------ ---------- ---------- ---------- ---------- ----------
TEST2                                             10                   100        255
TEST3                                   1         10         40        100        255
可以看到我們只能指定INI_TRANS，MAX_TRANS預設就是255，同時可以看到MSSM的PCT_FREE和PCT_USED都生效了因為他是FREELIST管理的(freelists=1)，
而ASSM卻只有PCT_FREE，而PCT_USED,freelists均為空，所以他是點陣圖進行管理空塊的，而PCT_FREE是必須因為剩餘多少空間給UPDATE和擴張的ITL還是
它來指定，而為了避免FREELIST的爭用，大家要儘量使用ASSM，預設的10G就是。
預設的INDEX的INI_TRANS=2，表的INI_TRANS=1，MAX_TRANS都是255,這樣就出現一個問題，如果沒有足夠的PCTFREE來進行擴張ITL，即便MAX_TRANS為65555也沒用，
所以如果出現ITL LOCK(MODE=4)，大家應該考慮是：
1、增加PCTFREE
2、增加初始的塊的ITL及提高INI_TRANS。
其實這樣的情況很少，但是遇到還是要注意。
及
SQL> alter table test2 pctfree 40 initrans 150;
 
Table altered
 
SQL> select TABLE_NAME,FREELISTS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS from user_tables where table_name in('TEST2');
 
TABLE_NAME                      FREELISTS   PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS
------------------------------ ---------- ---------- ---------- ---------- ----------
TEST2                                             40                   150        255

但是這個操作只對後來分配的BLOCK生效，如果對現有的表，只有MOVE了，根據需求看看是否NOLOGGING
SQL> alter table test2 move pctfree 30 initrans 170;
 
Table altered
當然索引也可以一樣處理

SQL> select TABLE_NAME,index_name,FREELISTS,PCT_FREE,INI_TRANS,MAX_TRANS from user_indexes ;
 
TABLE_NAME                     INDEX_NAME                      FREELISTS   PCT_FREE  INI_TRANS  MAX_TRANS
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
TEST3                          TEST_IN                                           10          2        255

注意索引沒有PCT_USED,是否進行插入資料不是PCT_USED控制的，是根據ROWID確定的。同時對於索引來說，其pctfree僅僅是在create或rebuild時生效，對與後續的插入、修改之類的操作來說是無效的，pctfree的設定也僅僅是為了延緩由於insert等操作而導致的的索引塊分裂。

SQL> alter index test_in  pctfree 30;
 
alter index test_in  pctfree 30
 
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
所以索引只能修改INITRANS
 
SQL>
SQL>
SQL> alter index test_in  initrans 170;
 
Index altered