[20120601]ITL的問題補充.txt
前一陣子,遇到ITL的問題,寫了一篇BLOG.
http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
最近看一篇blog ,才知道真正的問題在那裡?
%E2%80%93-changes-in-recent-releases-script/
我拿上面的scripts測試一下,我的測試資料庫11.2.0.1.
--補充一點,為了測試的方便,我加入了時間的顯示.為了測試的方便,我僅僅測試大約20分鐘.
--文章提到了ITL wait演算法的改變,摘要如下:
ITL Waits in 11gR1
In 11.1.0.6 and 11.1.0.7 a session waits at most one time on every slot. For all slots but one it waits up to 5 seconds.
For the other one it might wait indefinitely. The following pseudo code illustrates this (you should consider the variable
called "itl" as an array referencing/containing all ITL slots).
The problem of this algorithm is that an "unlucky" session might wait much longer than necessary. In fact, once it enters
the WAIT FOREVER status, it no longer considers the other slots.
ITL Waits in 11gR2
In 11.2.0.1 and 11.2.0.2 a session might wait several times for the same slot. Initially the wait is short. As the time
passes, the wait time increases exponentially based on the formula "wait time = power(2,iteration-1)". For all slots but one
there is a maximum wait time of 5 seconds, though. For the other one, and for the first 10 iterations only, the wait time is
computed with the very same formula. Then, during the 11th iteration, the session waits indefinitely. The following pseudo
code illustrates this.
The advantage of this algorithm is that a session might probe several time all the available slots and, as a result, enters
the WAIT FOREVER status after about 20 minutes only.
ITL Waits in 9i/10g
Up to 10.2.0.4 the behavior. is similar to 11gR1. The only noticeable difference is that the wait time is not always 5 seconds.
Instead, it is either 3 or 6 seconds. I was not able to spot a rule behind the choice between the two durations. So, there might
be some randomness involved.
In 10.2.0.5 the behavior. is similar to 11gR2. Also in this case the only noticeable difference is that the maximum wait time
is not always 5 seconds. Instead, as in releases up to 10.2.0.4, it is either 3 or 6 seconds.
--11GR2版本的改進還好一點.實際上我上次的測試不夠耐心http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
--如果等待也是半分鐘,應該可以提交.
--我上次等待的順序應該如下,按照上面的演算法:
1 1 1
2 2 2
4 4 4
5 5 8
5 5 16
5 5 32
5 5 64
1+1+1+2+2+2+4+4+4+5+5+8+5+5=49秒.也就是出現阻塞49秒,如果前面的itl釋放,last slot不釋放,要等16秒.第4個事務才能提交.
--如果在10.2.0.4,問題會更加嚴重:
10.2.0.4
--如果blocking_session=158不commit或者rollback,也就是itl.last不釋放,也就是經歷6+3+6+6=21秒後(指文章中的例子),阻塞的事務要處於等待的情況.
--這個也是我生產系統遇到的問題.
--這個問題很容易演示.好在一般出現"enq: TX– allocate ITL entry"不多.
--------------------------------測試:
-我建立了一個表，pctfree=0,這樣塊內空餘空間基本沒有，僅僅兩個ITL槽(實際上是3個)，不能在增加空間分配ITL槽。
SQL> select rowid from t where id=1;
ROWID
------------------
AAAUSUAAEAAAAO7AAA
SQL> select min(id),max(id) from t where rowid between 'AAAUSUAAEAAAAO7AAA' and 'AAAUSUAAEAAAAO7DDD';
MIN(ID) MAX(ID)
---------- ----------
1 581
--可以確定id從1到581都在一個塊中。
3.測試資料：
--修改資料，不commit.
開啟會話1：
update t set name='TEST' where id=1;
開啟會話2：
update t set name='TEST' where id=2;
開啟會話3：
update t set name='TEST' where id=3;
開啟會話4：
update t set name='TEST' where id=4;
--出現阻塞情況.
--在會話1執行
SQL> exec dbms_lock.sleep(21);
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 261
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 264
--rollback另外2個會話的事務.
--可以發現即使我釋放了2個itl slot,阻塞的事務依舊等待第3個事務槽的釋放.
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
164 1296
http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
最近看一篇blog ,才知道真正的問題在那裡?
%E2%80%93-changes-in-recent-releases-script/
我拿上面的scripts測試一下,我的測試資料庫11.2.0.1.
$ . itl.sh scott xxxxx $ORACLE_SID 1204
Setup correctly performed: YES
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
09:36:56.639596000 000000 blocking_session=191 sleep=0
09:36:57.649726000 000001 blocking_session=191 sleep=1
09:36:58.659690000 000002 blocking_session=9 sleep=1
09:36:59.669591000 000003 blocking_session=135 sleep=1
09:37:00.679531000 000004 blocking_session=10 sleep=1
09:37:01.689475000 000005 blocking_session=199 sleep=1
09:37:03.711303000 000007 blocking_session=191 sleep=2
09:37:05.644207000 000009 blocking_session=9 sleep=2
09:37:07.664006000 000011 blocking_session=135 sleep=2
09:37:09.684102000 000013 blocking_session=10 sleep=2
09:37:11.703930000 000015 blocking_session=199 sleep=2
09:37:15.657604000 000019 blocking_session=191 sleep=4
09:37:19.698203000 000023 blocking_session=9 sleep=4
09:37:23.737856000 000027 blocking_session=135 sleep=4
09:37:27.676508000 000031 blocking_session=10 sleep=4
09:37:31.735170000 000035 blocking_session=199 sleep=4
09:37:36.683508000 000040 blocking_session=191 sleep=5
09:37:41.733081000 000045 blocking_session=9 sleep=5
09:37:46.681696000 000050 blocking_session=135 sleep=5
09:37:51.755176000 000055 blocking_session=10 sleep=5
09:37:59.733417000 000062 blocking_session=199 sleep=8
09:38:04.683026000 000067 blocking_session=191 sleep=5
09:38:09.748439000 000072 blocking_session=9 sleep=5
09:38:14.696828000 000077 blocking_session=135 sleep=5
09:38:19.746381000 000082 blocking_session=10 sleep=5
09:38:35.715690000 000098 blocking_session=199 sleep=16
09:38:40.765254000 000103 blocking_session=191 sleep=5
09:38:45.728904000 000108 blocking_session=9 sleep=5
09:38:50.678387000 000113 blocking_session=135 sleep=5
09:38:55.727914000 000118 blocking_session=10 sleep=5
09:39:27.776097000 000150 blocking_session=199 sleep=32
09:39:32.725625000 000154 blocking_session=191 sleep=5
09:39:37.687242000 000159 blocking_session=9 sleep=5
09:39:42.736854000 000164 blocking_session=135 sleep=5
09:39:47.698552000 000169 blocking_session=10 sleep=5
09:40:51.774986000 000233 blocking_session=199 sleep=64
09:40:56.728513000 000237 blocking_session=191 sleep=5
09:41:01.786793000 000242 blocking_session=9 sleep=5
09:41:06.742866000 000247 blocking_session=135 sleep=5
09:41:11.807490000 000252 blocking_session=10 sleep=5
09:43:19.787092000 000379 blocking_session=199 sleep=128
09:43:24.740738000 000384 blocking_session=191 sleep=5
09:43:29.794293000 000389 blocking_session=9 sleep=5
09:43:34.742853000 000394 blocking_session=135 sleep=5
09:43:39.800442000 000399 blocking_session=10 sleep=5
09:47:55.746642000 000652 blocking_session=199 sleep=256
09:48:00.796100000 000657 blocking_session=191 sleep=5
09:48:05.744717000 000662 blocking_session=9 sleep=5
09:48:10.811302000 000667 blocking_session=135 sleep=5
09:48:15.760652000 000672 blocking_session=10 sleep=5
09:56:47.804452000 001178 blocking_session=199 sleep=512
09:56:52.775000000 001183 blocking_session=191 sleep=5
09:56:57.824431000 001188 blocking_session=9 sleep=5
09:57:02.773131000 001193 blocking_session=135 sleep=5
09:57:07.835712000 001198 blocking_session=10 sleep=5
09:57:09.047576000 001199 blocking_session=199 sleep=1
--補充一點,為了測試的方便,我加入了時間的顯示.為了測試的方便,我僅僅測試大約20分鐘.
--文章提到了ITL wait演算法的改變,摘要如下:
ITL Waits in 11gR1
In 11.1.0.6 and 11.1.0.7 a session waits at most one time on every slot. For all slots but one it waits up to 5 seconds.
For the other one it might wait indefinitely. The following pseudo code illustrates this (you should consider the variable
called "itl" as an array referencing/containing all ITL slots).
FOR i IN itl.FIRST..itl.LAST
LOOP
EXIT WHEN itl(i) IS FREE
IF i <> itl.LAST
THEN WAIT ON itl(i) FOR 5 SECONDS
ELSE WAIT ON itl(i) FOREVER
END IF
END LOOP
The problem of this algorithm is that an "unlucky" session might wait much longer than necessary. In fact, once it enters
the WAIT FOREVER status, it no longer considers the other slots.
ITL Waits in 11gR2
In 11.2.0.1 and 11.2.0.2 a session might wait several times for the same slot. Initially the wait is short. As the time
passes, the wait time increases exponentially based on the formula "wait time = power(2,iteration-1)". For all slots but one
there is a maximum wait time of 5 seconds, though. For the other one, and for the first 10 iterations only, the wait time is
computed with the very same formula. Then, during the 11th iteration, the session waits indefinitely. The following pseudo
code illustrates this.
iteration = 0
LOOP
iteration++
FOR i IN itl.FIRST..itl.LAST
LOOP
EXIT WHEN itl(i) IS FREE
IF i <> itl.LAST
THEN WAIT ON itl(i) FOR min(power(2,iteration-1),5) SECONDS
ELSIF iteration <= 10
THEN WAIT ON itl(i) FOR power(2,iteration-1) SECONDS
ELSE WAIT ON itl(i) FOREVER
END IF
END LOOP
EXIT WHEN free_itl_found
END LOOP
The advantage of this algorithm is that a session might probe several time all the available slots and, as a result, enters
the WAIT FOREVER status after about 20 minutes only.
ITL Waits in 9i/10g
Up to 10.2.0.4 the behavior. is similar to 11gR1. The only noticeable difference is that the wait time is not always 5 seconds.
Instead, it is either 3 or 6 seconds. I was not able to spot a rule behind the choice between the two durations. So, there might
be some randomness involved.
In 10.2.0.5 the behavior. is similar to 11gR2. Also in this case the only noticeable difference is that the maximum wait time
is not always 5 seconds. Instead, as in releases up to 10.2.0.4, it is either 3 or 6 seconds.
--11GR2版本的改進還好一點.實際上我上次的測試不夠耐心http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
--如果等待也是半分鐘,應該可以提交.
--我上次等待的順序應該如下,按照上面的演算法:
1 1 1
2 2 2
4 4 4
5 5 8
5 5 16
5 5 32
5 5 64
1+1+1+2+2+2+4+4+4+5+5+8+5+5=49秒.也就是出現阻塞49秒,如果前面的itl釋放,last slot不釋放,要等16秒.第4個事務才能提交.
--如果在10.2.0.4,問題會更加嚴重:
10.2.0.4
000000 blocking_session=136 sleep=0
000005 blocking_session=136 sleep=6
000010 blocking_session=140 sleep=3
000015 blocking_session=152 sleep=6
000020 blocking_session=159 sleep=6
029995 blocking_session=158 sleep=29979
--如果blocking_session=158不commit或者rollback,也就是itl.last不釋放,也就是經歷6+3+6+6=21秒後(指文章中的例子),阻塞的事務要處於等待的情況.
--這個也是我生產系統遇到的問題.
--這個問題很容易演示.好在一般出現"enq: TX– allocate ITL entry"不多.
--------------------------------測試:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t pctfree 0 as select rownum id ,'test' name from dual connect by level<=1000;
SQL> create unique index i_t_id on t(id);
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T');
-我建立了一個表，pctfree=0,這樣塊內空餘空間基本沒有，僅僅兩個ITL槽(實際上是3個)，不能在增加空間分配ITL槽。
SQL> select rowid from t where id=1;
ROWID
------------------
AAAUSUAAEAAAAO7AAA
SQL> select min(id),max(id) from t where rowid between 'AAAUSUAAEAAAAO7AAA' and 'AAAUSUAAEAAAAO7DDD';
MIN(ID) MAX(ID)
---------- ----------
1 581
--可以確定id從1到581都在一個塊中。
3.測試資料：
--修改資料，不commit.
開啟會話1：
update t set name='TEST' where id=1;
開啟會話2：
update t set name='TEST' where id=2;
開啟會話3：
update t set name='TEST' where id=3;
開啟會話4：
update t set name='TEST' where id=4;
--出現阻塞情況.
--在會話1執行
SQL> exec dbms_lock.sleep(21);
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 261
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 264
--rollback另外2個會話的事務.
--可以發現即使我釋放了2個itl slot,阻塞的事務依舊等待第3個事務槽的釋放.
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
164 1296
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-731657/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211221]記錄使用sqlplus的小問題補充.txtSQL
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- [20210708]find -mtime +0 0 -0時間問題補充.txt
- [20210626]find -mtime +N N -N時間問題補充.txt
- [20210625]find -mtime +N N -N時間問題補充.txt
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20220815]奇怪的隱式轉換問題(11g測試補充).txt
- [20220811]奇怪的隱式轉換問題(12c補充測試).txt
- [202021127]sql打補丁問題.txtSQL
- [20190312]關於增量檢查點的疑問(補充).txt
- [20220329]windwos下使用seq與xargs建立多個子目錄問題(補充).txt
- [20180928]ora-01426(補充).txt
- 密碼找回功能可能存在的問題（補充）密碼
- [20220223]Index ITL Limit.txtIndexMIT
- [20241016]Oracle C functions annotations補充.txtOracleFunction
- [20220603]測試quiz night(補充).txtUI
- [20211215]提示precompute_subquery補充.txt
- [20231027]Index ITL Limit 3.txtIndexMIT
- [20231031]Index ITL Limit 4.txtIndexMIT
- [20231027]Index ITL Limit 2.txtIndexMIT
- [20210223]bbed itl ktbitflg 2.txt
- [20210929]sql打補丁使用rule提示問題.txtSQL
- [20211116]plsql_code_type=native補充.txtSQL
- [20210604]索引分裂與 itl ktbitflg.txt索引
- [20221014]TNS-12543 TNSdestination host unreachable(補充).txt
- [20210803]使用那個shared pool latch(補充).txt
- [20211013]測試遠端監聽補充.txt
- [20211221]提示precompute_subquery補充2.txt
- BGP基礎（簡述）歡迎有問題補充
- [20190125]MSSM表空間塊ITL的LCK.txtSSM
- [20180327]行遷移與ITL浪費.txt
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20211025]12c sequence nocache測試補充.txt
- [20220120]超長sql語句補充4.txtSQL
- [20220119]超長sql語句補充3.txtSQL
- [20180129]簡單探究cluster table(補充)4.txt
- [20181229]簡單探究cluster table(補充)3.txt
- [20181227]簡單探究cluster table(補充)2.txt