關於enq: TX - allocate ITL entry等待事件
SQL> create table trans_test(id number,name varchar2(100)) initrans 1 maxtrans 1;
Table created
SQL> select ini_trans,max_trans,table_name from user_tables a where a.table_name='TRANS_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.
對於ini_trans, max_trans的預設值,表級為1,索引級為2. 一般來說不需要做特別的設定。可以根據業務的需要來配置。
以下設定可供參考:
對於大表,資料千萬級以上的表,initrans建議設定為8~16
對於中級表,資料量在百萬到千萬級,initrans建議設定為4~8
對於普通的表,initrans建議設定為1~4
對於此等待事件,解決思路有三種:
1) Depending on the number of transactions in the table we need to alter the value of INITRANS.
If the issue is not resolved by increasing INITRANS then try increasing PCTFREE. Increasing PCTFREE holds more
1) Spreading rows into more number of blocks will also helps to reduce this wait event.
2) Then re-organize the table using move (alter table service_T move;)
3) Rebuild index
Increase INITRANS
here it has been changed to 50:
2) Then re-organize the table using move (alter table move;)
3) Then rebuild all the indexes of this table as below
Increase PCTFREE
space back and so spreads the same number of rows over more blocks. This means that there are more ITL slots
available overall :
A Combination of increasing both INITRANS and PCTFREE
1) Set INITRANS to 50 pct_free to 40
2) Re-organize the table using move (alter table move;)
3) Then rebuild all the indexes of the table as below
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21374452/viewspace-2157353/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- enq: TX - allocate ITL entry等待事件分析ENQ事件
- enq: TX - allocate ITL entryENQ
- 關於enq: TX - allocate ITL entry的問題分析ENQ
- enq: TX - allocate ITL entry等待過多導致全域性死鎖ENQ
- [20140130]關於enq TX-allocate ITL entryENQ
- [20150721]enq TX - allocate ITL entryENQ
- 【MOS】 Troubleshooting waits for enq: TX - allocate ITL entry(1472175.1)AIENQ
- 【故障處理】佇列等待之TX - allocate ITL entry案例佇列
- 【故障處理】佇列等待之TX - allocate ITL entry引起的死鎖處理佇列
- [20231026]enq TX - allocate ITL entry的測試4.txtENQ
- 等待事件enq: TX - row lock contention事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- 故障排除 | enq:TX - index contention等待事件ENQIndex事件
- 等待事件enq TX row lock contention分析事件ENQ
- enq: TX - row lock contention等待事件處理ENQ事件
- zt_Oracle enq: TX contention 和 enq: TM contention 等待事件OracleENQ事件
- enq: TX - index contention等待ENQIndex
- 關於enq: TX - index contention 等待的探討與測試ENQIndex
- enq: WF - contention等待事件ENQ事件
- enq: CF - contention 等待事件ENQ事件
- enq: TS - contention 等待事件ENQ事件
- 等待事件之enq: HW - contention事件ENQ
- enq:TM-contention事件等待ENQ事件
- 消除 enq: DX - contention 等待事件ENQ事件
- 關於enq: TX - row lock contention行鎖的總結ENQ
- 關於ITL以及UNDO SEGMENT HEADER 事物表(tx table)闡述Header
- Oracle等待事件之enq: TM – contentionOracle事件ENQ
- oracle等待事件之enq: CF – contentionOracle事件ENQ
- enq:Library cache lock/pin等待事件ENQ事件
- 如何診斷等待事件 enq: HW - contention事件ENQ
- 【效能調整】等待事件 enq: SQ - contention事件ENQ
- enq:TX - index contentionENQIndex
- enq: TX - index contentionENQIndex
- 關於DFS lock handle等待事件事件
- TX:ITL LOCK(INITRANS,MAXINTRANS)
- 事務上的等待事件 —— enq: UL - contention事件ENQ
- enq: SQ - contention 等待事件處理辦法ENQ事件
- enq: TX - row lock contentionENQ