如何解決enq: TX- index contention
1,enq: TX- index contention等待事件的概念
2,如何產生此等待事件
3,如何解決等待事件
4,小結
.
1,enq: TX- index contention等待事件的概念
1，當在基於索引的表中插入一條記錄時，此時正好另一會話事務正在操作這個索引塊正處於分裂狀態（即索引塊分裂）
2,如何產生此等待事件
--構造等待事件 enq: TX- index contention,如高併發引發此事件
--併發job
declare
v_job pls_integer;
begin
for i in 1..100 loop
dbms_job.submit(v_job,what => 'proc_arg;',next_date => sysdate,interval=>'sysdate+1');
commit;
end loop;
end;
/
--併發job呼叫的儲存過程
create or replace procedure proc_arg
as
begin
insert into t_detail values(1);
--dbms_lock.sleep(120);
--commit;
end;
--查詢是否發生了enq: TX- index contention事件
--先溫習下v$session_wait檢視幾個重要列的含義：
1，WAIT_TIME_MICRO,
1,單位為毫秒
2,如當前會話處於等待，則為已等待佔用的時間
3,如當前會話已停止等待,則為最近一次等待佔用的時間
2，TIME_REMAINING_MICRO
1,本引數有幾類值
2,若值>0，則表示還要等待多久時間
若值=0,則表示當前會話已超時
若值=-1,則表示不知還要等待多久
若值=null,表示當前會話未處於等待狀態
--由下可知,不知還要等多久
SQL> select event,WAIT_TIME_MICRO,TIME_REMAINING_MICRO from v$session_wait where event like '%enq: TX - index contention%';
EVENT WAIT_TIME_MICRO TIME_REMAINING_MICRO
---------------------------------------------------------------- --------------- --------------------
enq: TX - index contention 804411 -1
enq: TX - index contention 804144 -1
enq: TX - index contention 821468 -1
enq: TX - index contention 759771 -1
enq: TX - index contention 766228 -1
--中間略
enq: TX - index contention 784128 -1
--判斷上述的enq: TX - index contention是否和索引塊分裂有關
SQL> select * from v$event_name where name like '%split%';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------
169 1457266432 gc current split 3871361733 11 Cluster
769 67546339 index block split rootdba level childdba 1893977003 0 Other
794 695862004 kksscl hash split 1893977003 0 Other
994 1569316226 enq: AM - PST split check name|mode id1 id2 1893977003 0 Other
SQL> select sid,event,total_waits,average_wait from v$session_event where event in (select name from v$event_name where name like '%split%' and event#=769);
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
--未看到索引塊分裂
SQL> select sid,event,total_waits,average_wait from v$session_event where event like '%index%'
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
10 enq: TX - index contention 34 74.76
14 enq: TX - index contention 101 112.85
15 enq: TX - index contention 90 74.66
---中間內容略
17 enq: TX - index contention 17 63.72
20 enq: TX - index contention 101 64.37
170 enq: TX - index contention 107 74.87
171 enq: TX - index contention 107 66.2
172 enq: TX - index contention 3 63.76
59 rows selected
SQL> select * from v$event_name where name like '%enq%index%';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
enq: TX - index contention name|mode usn<<16 | slot sequence Concurrency
--獲取與索引塊分裂的統計
SQL> select * from v$sesstat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
1 0 0
SQL> select * from v$statname where name like '%split%';
STATISTIC# NAME CLASS STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
382 leaf node splits 128 1417124053
383 leaf node 90-10 splits 128 1285138251
384 branch node splits 128 399027615
385 root node splits 128 708722985
400 queue splits 128 1168925905
--葉子塊分裂的統計資訊
SQL> select sum(ss.VALUE) from v$sesstat ss,v$statname st where ss.STATISTIC#=st.statistic# and st.statistic#=382;
SUM(SS.VALUE)
-------------
3
--執行併發會話後葉子塊分裂的情況，確實發生了葉子塊分裂
SQL> select sum(ss.VALUE) from v$sesstat ss,v$statname st where ss.STATISTIC#=st.statistic# and st.statistic#=382;
SUM(SS.VALUE)
-------------
437
SQL> /
SUM(SS.VALUE)
-------------
591
3,如何解決等待事件
1,重構索引，建立反向鍵索引
2,增大索引的pctfree
--檢視index的pctfree
SQL> select pct_free from user_indexes where index_name='IDX_T_DETAIL';
PCT_FREE
----------
10
--可刪除索引建立反向鍵索引
SQL> create index idx_t_detail on t_detail(a) reverse;
Index created
--反向鍵索引有了，是否會降低enq: TX - index contention競爭呢
--enq: TX - index contention等待事件還有;total_waits表示等待次數;average_wait表示平均每次等待多久時間,單位為in hundredths of a second
--但這個v$session_event不會儲存下來，等待事件一結束就看不到了，主要想看平均等待事件，在dba_hist也沒找到相應的表
select sid,event,total_waits,average_wait from v$session_event where event like 'enq: TX - index contention'；
---------- ---------------------------------------------------------------- ----------- ------------
8 enq: TX - index contention 58 62.29
18 enq: TX - index contention 86 82.81
19 enq: TX - index contention 89 69.25
29 enq: TX - index contention 112 72.57
38 enq: TX - index contention 114 72.14
55 enq: TX - index contention 107 69.71
61 enq: TX - index contention 111 66.13
--如下檢視可以查詢上述等待事件的變動資訊,根據事件分類，並根據等待事件長短分為不同的桶，桶的標準為：
--from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 221 ms, < 222 ms, and >= 222 ms.
select event ,
wait_time_milli,
wait_count,
last_update_time
from V$EVENT_HISTOGRAM
where event like 'enq: TX - index contention';
--此為上述的歷史資訊
SQL> select * from dba_hist_event_histogram where event_name like 'enq: TX - index contention';
--我的思路透過執行b-tree index與反向鍵index，比對V$EVENT_HISTOGRAM上述的結果，有何區別
--v$event_histogram不會自動清除結果，須重啟庫方可（在此為了對比）
--為了對比，先刪除之前的索引
SQL> drop index idx_t_detail;
Index dropped
--建立b-tree index
SQL> create index idx_t_detail on t_detail(a);
Index created
--執行文章前面的併發plsql指令碼
--交替執行下面2個指令碼，此指令碼確保上述的併發plsql指令碼執行完畢
SQL> select count(1) from v$session_event where event like 'enq: TX - index contention';
COUNT(1)
----------
1
--檢視等待事件的統計資訊
SQL> select event ,
2 wait_time_milli,
3 wait_count,
4 last_update_time
5 from V$EVENT_HISTOGRAM
6 where event like 'enq: TX - index contention';
EVENT WAIT_TIME_MILLI WAIT_COUNT LAST_UPDATE_TIME
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 159 18-MAR-13 04.41.39.178979 PM +08:00
enq: TX - index contention 2 45 18-MAR-13 04.41.41.478856 PM +08:00
enq: TX - index contention 4 58 18-MAR-13 04.41.38.528987 PM +08:00
enq: TX - index contention 8 56 18-MAR-13 04.41.41.183837 PM +08:00
enq: TX - index contention 16 97 18-MAR-13 04.41.37.391778 PM +08:00
enq: TX - index contention 32 163 18-MAR-13 04.41.41.606186 PM +08:00
enq: TX - index contention 64 289 18-MAR-13 04.41.41.372071 PM +08:00
enq: TX - index contention 128 485 18-MAR-13 04.41.40.770750 PM +08:00
enq: TX - index contention 256 654 18-MAR-13 04.41.39.239939 PM +08:00
enq: TX - index contention 512 1185 18-MAR-13 04.41.40.940983 PM +08:00
enq: TX - index contention 1024 1864 18-MAR-13 04.41.38.830764 PM +08:00
enq: TX - index contention 2048 876 18-MAR-13 04.41.40.670356 PM +08:00
enq: TX - index contention 4096 152 18-MAR-13 04.41.09.104519 PM +08:00
enq: TX - index contention 8192 23 18-MAR-13 04.39.27.721987 PM +08:00
enq: TX - index contention 16384 12 18-MAR-13 04.39.20.452465 PM +08:00
enq: TX - index contention 32768 14 18-MAR-13 04.39.47.426106 PM +08:00
--重啟庫為了清除v$event_histogram的統計資訊，再看看反向鍵索引的競爭情況
EVENT WAIT_TIME_MILLI WAIT_COUNT LAST_UPDATE_TIME
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 136 18-MAR-13 05.11.10.278219 PM +08:00
enq: TX - index contention 2 35 18-MAR-13 05.11.10.243922 PM +08:00
enq: TX - index contention 4 43 18-MAR-13 05.11.09.834354 PM +08:00
enq: TX - index contention 8 60 18-MAR-13 05.11.07.219909 PM +08:00
enq: TX - index contention 16 74 18-MAR-13 05.11.08.621526 PM +08:00
enq: TX - index contention 32 135 18-MAR-13 05.11.10.028352 PM +08:00
enq: TX - index contention 64 311 18-MAR-13 05.11.09.444585 PM +08:00
enq: TX - index contention 128 485 18-MAR-13 05.11.09.972510 PM +08:00
enq: TX - index contention 256 669 18-MAR-13 05.11.09.793868 PM +08:00
enq: TX - index contention 512 1130 18-MAR-13 05.11.06.987832 PM +08:00
enq: TX - index contention 1024 1932 18-MAR-13 05.11.09.289674 PM +08:00
enq: TX - index contention 2048 1128 18-MAR-13 05.11.05.031096 PM +08:00
enq: TX - index contention 4096 166 18-MAR-13 05.10.37.715998 PM +08:00
enq: TX - index contention 8192 8 18-MAR-13 05.08.37.833097 PM +08:00
enq: TX - index contention 16384 5 18-MAR-13 05.08.37.820380 PM +08:00
結論：反向鍵索引的競爭反而更多了，我是算的上面的累計值
--看來還有問題，要麼不能用反向鍵，要麼我測試的方法不對：
--既然與索引塊分裂有關，如果加大索引所在資料塊大小，會不會減少競爭次數呢
--使用大資料塊必備配置
SQL> alter system set db_16k_cache_size=20m scope=spfile;
System altered
--建立大資料表空間
SQL> create tablespace tbs_16k datafile
2 'c:\tbs16k_1.dbf' size 2g blocksize 16k;
Tablespace created
--切換索引到大資料塊表空間
SQL> select index_name,tablespace_name from user_indexes where index_name='IDX_T_DETAIL';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IDX_T_DETAIL TBS_HANG
SQL> alter index idx_t_detail rebuild tablespace tbs_16k;
Index altered
SQL> select index_name,tablespace_name from user_indexes where index_name='IDX_T_DETAIL';
INDEX_NAME TABLESPACE_NAME
------------------------------ ---------
IDX_T_DETAIL TBS_16K
--大資料塊測試的競爭如下
EVENT WAIT_TIME_MILLI WAIT_COUNT LAST_UPDATE_TIME
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 106 18-MAR-13 08.01.30.221162 PM +08:00
enq: TX - index contention 2 30 18-MAR-13 08.01.25.504420 PM +08:00
enq: TX - index contention 4 25 18-MAR-13 08.01.25.506781 PM +08:00
enq: TX - index contention 8 39 18-MAR-13 08.01.29.690659 PM +08:00
enq: TX - index contention 16 77 18-MAR-13 08.01.29.691672 PM +08:00
enq: TX - index contention 32 100 18-MAR-13 08.01.10.861044 PM +08:00
enq: TX - index contention 64 236 18-MAR-13 08.01.19.586879 PM +08:00
enq: TX - index contention 128 293 18-MAR-13 08.01.30.129016 PM +08:00
enq: TX - index contention 256 423 18-MAR-13 08.01.29.552920 PM +08:00
enq: TX - index contention 512 477 18-MAR-13 08.01.30.955651 PM +08:00
enq: TX - index contention 1024 681 18-MAR-13 08.01.24.291290 PM +08:00
enq: TX - index contention 2048 628 18-MAR-13 08.01.27.361559 PM +08:00
enq: TX - index contention 4096 252 18-MAR-13 08.01.27.762065 PM +08:00
enq: TX - index contention 8192 48 18-MAR-13 08.01.05.489611 PM +08:00
enq: TX - index contention 16384 10 18-MAR-13 07.59.39.765070 PM +08:00
15 rows selected
--加大資料塊尺寸後，index contention大大減少
--哪麼修改index pctfree效果表現如何呢
----修改前的pctfree
SQL> select pct_free from user_indexes where index_name='IDX_T_DETAIL';
PCT_FREE
----------
10
--有個問題要搞清楚，oracle在插入索引塊時，是先在每個已有資料的索引塊先查詢是否有符合條件的空閒資料塊，還是直接查詢一個新的索引塊
pctfree--為每個資料保留一個用於後來update的比例；減小此引數會不會減少index contention
SQL> drop index idx_t_detail;
Index dropped
SQL> create index idx_t_detail on t_detail(a) tablespace tbs_16k pctfree 0;
Index created
--貼上pctfree為0的index contention，可知競爭大為減少；
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 63 18-MAR-13 08.54.42.786397 PM +08:00
enq: TX - index contention 2 9 18-MAR-13 08.54.37.646487 PM +08:00
enq: TX - index contention 4 12 18-MAR-13 08.54.37.053155 PM +08:00
enq: TX - index contention 8 7 18-MAR-13 08.54.40.311190 PM +08:00
enq: TX - index contention 16 26 18-MAR-13 08.54.42.766359 PM +08:00
enq: TX - index contention 32 30 18-MAR-13 08.54.42.667444 PM +08:00
enq: TX - index contention 64 47 18-MAR-13 08.54.42.755426 PM +08:00
enq: TX - index contention 128 63 18-MAR-13 08.54.37.625092 PM +08:00
enq: TX - index contention 256 98 18-MAR-13 08.54.40.562709 PM +08:00
enq: TX - index contention 512 109 18-MAR-13 08.54.40.364472 PM +08:00
enq: TX - index contention 1024 138 18-MAR-13 08.54.42.479061 PM +08:00
enq: TX - index contention 2048 186 18-MAR-13 08.54.42.478623 PM +08:00
enq: TX - index contention 4096 109 18-MAR-13 08.54.27.103869 PM +08:00
enq: TX - index contention 8192 41 18-MAR-13 08.54.27.108269 PM +08:00
enq: TX - index contention 16384 12 18-MAR-13 08.54.01.466532 PM +08:00
4，小結
1,如出現enq: TX - index contention,說明此時正有會話正在操作分裂的索引塊，而你所屬會話要更新此資料塊則發生此事件
2,高併發會產生此事件
3,把索引和表儲存在獨立的表空間，加大索引的block size(注意：其它附加副作用，暫未測試)
4,重構索引為反向鍵索引或全域性hash index,說白了就是把索引塊打散分佈到多個資料塊，不要集中儲存；這樣每個塊競爭的機會就減少
5,修正index pctfree為0,大大減少index contention, 但要注意表是否只能insert，如還伴隨大量的update（此塊下文研究）
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-756476/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- enq:TX - index contentionENQIndex
- enq: TX - index contentionENQIndex
- enq: TX - index contention等待ENQIndex
- enq: TX - index contention基礎理論ENQIndex
- 故障排除 | enq：TX - index contention等待事件ENQIndex事件
- Troubleshooting 'enq: TX - index contention' WaitsENQIndexAI
- 【故障解決】enq: PS - contentionENQ
- enq: TX - index contention故障修復一例ENQIndex
- 'enq: TX - index contention' Waits in a RAC Environment. [ID 873243.1]ENQIndexAI
- enq: US - contentionENQ
- enq: HW - contentionENQ
- enq: TM - contentionENQ
- enq:TM contentionENQ
- enq: DX - contentionENQ
- enq: TS - contentionENQ
- 【MOS】Troubleshooting 'enq: TX - index contention' Waits (文件 ID 873243.1)ENQIndexAI
- zt_Oracle enq: TX contention 和 enq: TM contention 等待事件OracleENQ事件
- 關於enq: TX - index contention 等待的探討與測試ENQIndex
- enq: TX - row lock contentionENQ
- 關於enq: US – contentionENQ
- enq: WF - contention等待事件ENQ事件
- enq: CF - contention 等待事件ENQ事件
- enq: TS - contention 等待事件ENQ事件
- enq: HW - contention診斷及解決過程ENQ
- 等待事件之enq: HW - contention事件ENQ
- enq: SQ - contention" waits in RACENQAI
- enq:TM-contention事件等待ENQ事件
- 消除 enq: DX - contention 等待事件ENQ事件
- Oracle等待事件之enq: TM – contentionOracle事件ENQ
- 等待事件enq: TX - row lock contention事件ENQ
- oracle等待事件之enq: CF – contentionOracle事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- Metlink:Performance issues with enq: US - contentionORMENQ
- 奇異的enq: TX - row lock contentionENQ
- 等待事件enq TX row lock contention分析事件ENQ
- 如何診斷等待事件 enq: HW - contention事件ENQ
- enq: HW - contention 問題的處理ENQ
- 【MW】Drop Materialized View Hangs with 'Enq: JI - Contention'ZedViewENQ