【故障處理】佇列等待之TX - allocate ITL entry案例

lhrbest發表於2016-09-08
佇列


【故障處理】佇列等待之TX - allocate ITL entry案例

1  BLOG文件結構圖

 

wps9D6B.tmp 

 

2  前言部分

2.1  導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① enq: TX - allocate ITL entry等待事件的解決

② 一般等待事件的解決辦法

③ 佇列等待的基本知識

Tips：

① 本文在ITpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）有同步更新

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，可以去部落格園地址閱讀

④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

====》2097152*512/1024/1024/1024=1G 

 

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

 

 

3  故障分析及解決過程

3.1  故障環境介紹

 

專案

Source db

db 型別

RAC

db version

11.2.0.3.0

db 儲存

ASM

OS版本及kernel版本

AIX 64位 7.1.0.0

 

3.2  故障發生現象及報錯資訊

最近事情比較多，不過還好，碰到的都是等待事件相關的，同事發了個AWR報告，說是系統響應很慢，我簡單看了下，簡單分析下吧：

wps9D7B.tmp 

20分鐘時間而DB Time為11461分鐘，DB Time太高了，負載很大，很可能有異常的等待事件，系統配置還是比較牛逼的。

wps9D7C.tmp 

事務量很大，其它個別引數有點問題，不一一解說了。Instance Efficiency Percentages也有點問題：

wps9D7D.tmp 

等待事件很明顯了：

wps9D8E.tmp 

AWR的其它部分就不分析了，首先這個等待事件：enq: TX - allocate ITL entry比較少見，查了一下MOS，有點收穫：Troubleshooting waits for 'enq: TX - allocate ITL entry' (文件 ID 1472175.1)

Observe high waits for event enq: TX - allocate ITL entry

Top 5 Timed Foreground Events

Event                           Waits  Time(s)  Avg wait (ms)  % DB time  Wait Class

enq: TX - allocate ITL entry    1,200   3,129           2607       85.22  Configuration

DB CPU                                                   323        8.79

gc buffer busy acquire         17,261      50              3        1.37  Cluster

gc cr block 2-way             143,108      48              0        1.32  Cluster

gc current block busy          10,631      46              4        1.24  Cluster

CAUSE

By default INITRANS value for table is 1 and for index is 2. This defines an internal block structure called the Interested Transaction List (ITL). In order to modify data in a block, a process needs to use an empty ITL slot to record that the transaction is interested in modifying some of the data in the block. If there are insufficient free ITL slots then new ones will be taken in the free space reserved in the block. If this runs out and too many concurrent DML transactions are competing for the same data block we observe contention against the following wait event - "enq: TX - allocate ITL entry".

You can see candidates for re-organisation due to ITL problems in the "Segments by ITL Waits"  section of an Automatic Workload Repository (AWR) report:

Segments by ITL Waits

  * % of Capture shows % of ITL waits for each top segment compared

  * with total ITL waits for all segments captured by the Snapshot

Owner Tablespace Name Object Name Subobject Name Obj. Type       ITL  Waits % of Capture

PIN   BRM_TABLES      SERVICE_T                  TABLE           188               84.30

PIN   BRM_TABLES      BILLINFO_T  P_R_06202012   TABLE PARTITION  35               15.70

SOLUTION

The main solution to this issue is to increase the ITL capability of the table or index by re-creating it and altering the INITRANS or PCTFREE parameter to be able to handle more concurrent transactions. This in turn will help to reduce "enq: TX - allocate ITL entry" wait events.

To reduce enq: TX - allocate ITL entry" wait events, We need to follow the steps below:

1) Set INITRANS to 50 and  pct_free to 40

alter table <table_name> PCTFREE 40  INITRANS 50;

2) Re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of the table as below

alter index <index_name>  rebuild PCTFREE 40 INITRANS 50;

 

總結一下：

原因：表和索引的預設INITRANS值不合適，引起的事務槽分配等待。當一個事務需要修改一個資料塊時，需要在資料塊頭部獲取一個可用的ITL槽，用於記錄事務的id,使用undo資料塊地址,scn等資訊。如果事務申請不到新的可用ITL槽時，就會產生enq: TX - allocate ITL entry等待。 發生這個等待時，要麼是塊上的已分配ITL個數(透過ini_trans引數控制)達到了上限255(10g以後沒有了max_trans限制引數，無法指定小於255的值)，要麼是這個塊中沒有更多的空閒空間來容納一個ITL了(每個ITL佔用24bytes)。 預設情況下建立的表ITL槽數最小為1+1,pctfree為10，那麼如果是這樣一種情況，如果表中經常執行update語句，然後塊中剩餘的10%空間所剩無幾，而且業務的併發量還很大，此時就很容易遇到enq: TX - allocate ITL entry等待。

解決：解決方式就是調整表和索引的INITRANS,有必要還需要調整pcfree值。

1) Set INITRANS to 50 and  pct_free to 40

alter table <table_name> PCTFREE 40  INITRANS 50;

2) Re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of the table as below

alter index <index_name>  rebuild PCTFREE 40 INITRANS 50;

 

3.3  故障分析及解決

有了以上的知識，我們知道，目前首先需要找到產生等待事件的表，然後修改INITRANS和PCTFREE來重構表就可以了。

我們檢視AWR中的Segments by ITL Waits部分：

wps9D8F.tmp 

wps9D90.tmp 

 

wps9D91.tmp 

 

 SELECT D.SQL_ID,

        CHR(BITAND(P1, -16777216) / 16777215) ||

        CHR(BITAND(P1, 16711680) / 65535) "Lock",

        BITAND(P1, 65535) "Mode",

        D.CURRENT_OBJ#,

        COUNT(1),

        COUNT(DISTINCT D.SESSION_ID)

   FROM DBA_HIST_ACTIVE_SESS_HISTORY D

  WHERE D.SAMPLE_TIME BETWEEN

        TO_DATE('2016-09-05 16:55:00', 'YYYY-MM-DD HH24:MI:SS') AND

        TO_DATE('2016-09-05 17:15:00', 'YYYY-MM-DD HH24:MI:SS')

    AND D.EVENT = 'enq: TX - allocate ITL entry'

  GROUP BY D.SQL_ID,

           (CHR(BITAND(P1, -16777216) / 16777215) ||

           CHR(BITAND(P1, 16711680) / 65535)),

           (BITAND(P1, 65535)),

           D.CURRENT_OBJ#;

wps9DA2.tmp 

 

SELECT * FROM v$sql a WHERE a.SQL_ID='1cmnjddakrqbv'; 

wps9DA3.tmp 

SELECT * FROM Dba_Objects d WHERE d.object_id=87620;

wps9DA4.tmp 

好吧，知道了表名，我們檢視一下表的屬性：

SELECT * FROM Dba_Tables d WHERE d.table_name='ORGANIZATION'; 

wps9DA5.tmp 

pct_free為10，ini_trans為1，我們根據MOS應該修改這2個值，SQL如下：

ALTER TABLE ORGANIZATION PCTFREE 20  INITRANS 16;

ALTER TABLE ORGANIZATION MOVE;

ALTER INDEX PK_ORGANIZATION  REBUILD PCTFREE 20 INITRANS 16 NOLOGGING;

 

這裡需要注意：該表大約2000條記錄，很小，所以MOVE的時候可以不用並行，也不用NOLOGGING，若是表很大的時候可以考慮並行+NOLOGGING屬性，另外，還需要REBUILD索引才可以。

修改完成後，開發人員經過測試後終於可以了，給我簡單回覆了一下。

 

wps9DA6.tmp 

 

  About Me

..........................................................................................................................................................................................................                        

● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新，推薦pdf檔案閱讀

● QQ群：230161599 微信群：私聊

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2124735/  部落格園地址：http://www.cnblogs.com/lhrbest/articles/5854407.html

● 本文pdf版： （提取碼：ed9b）

● 小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

● 聯絡我請加QQ好友(642808185)，註明新增緣由

● 於 2016-09-06 09:00~2016-09-06 19:00 在中行完成

● 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

..........................................................................................................................................................................................................

長按識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,學習最實用的資料庫技術。

wps9DB6.tmp

 



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2124735/，如需轉載，請註明出處，否則將追究法律責任。

相關文章