昨天遇到一例enq TX - allocate ITL entry等待事件,就是维护人员打开多个会话更新一个表的某个字段,开始以为等待与undo有关,查
看才发现是"enq TX - allocate ITL entry",因为没有业务操作,建议修改为ctas来建立新表,建立相关索引,然后改名完成操作.
实际上,一般正常的业务操作,很少遇到这个等待事件,如果出现一般是pctfree设置太小或者由于记录程度增大,导致pctfree减少,以及块
上事务太多而导致问题.
做一个简单的比喻：1个数据块相当于小区，ITL slot相当于小区的停车位，pctfree的空间相当于公共绿地。每个事务相当于小汽车，当
发生一个事务时，占用一个itl slot，直到commit，其他事务才能使用。而当事务很多的时候，就要向pctfree区借用空间，如果由于
pctfree设置太小或者由于修改记录导致记录长度增加，这样pctfree减少，如果在一个块上有许多事务，又没有及时提交，无法建立
itl slot时，就出现enq TX - allocate ITL entry等待事件。我以前遇到的问题是在10.2.0.4上itl solt的分配算法存在问题,导致
很快出现长时间等待.实际上上面的操作最后变成不可能完成的业务。在10.2.0.4下出现这个问题更快，可以参考我以前写的链接：
http://blog.itpub.net/267265/viewspace-717089/
http://blog.itpub.net/267265/viewspace-731657/
http://antognini.ch/2011/04/itl-waits-changes-in-recent-releases/
http://antognini.ch/2011/06/itl-waits-%E2%80%93-changes-in-recent-releases-script/
--为了加深印象,拿12c重复做一次测试看看.以下脚本来自原作者Christian Antognini，我仅仅加入时间的输出。
#!/bin/sh
user=$1
password=$2
sid=$3
wait=$4
#
# Setup test environment
#
sqlplus -s $user/$password@$sid SET ECHO OFF TERMOUT ON FEEDBACK OFF HEADING OFF
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE t PURGE';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE t (n NUMBER, pad VARCHAR2(50)) PCTFREE 0 INITRANS 5 TABLESPACE users;
INSERT INTO t SELECT rownum, rpad('*',50,'*') FROM dual CONNECT BY level COMMIT;
SELECT 'Setup correctly performed:', decode(count(DISTINCT dbms_rowid.rowid_block_number(rowid)),1,'YES','NO') FROM t;
END
#
# Produce ITL wait
#
for i in 1 2 3 4 5 6
do
echo 'SET ECHO OFF TERMOUT OFF FEEDBACK OFF' > $i.$sid.sql
if [[ $i = 6 ]]
then
# make sure that the other processes have locked one row
echo 'execute dbms_lock.sleep(1)' >> $i.$sid.sql
fi
echo 'UPDATE t SET n = n WHERE n =' $i ';' >> $i.$sid.sql
echo 'SET TERMOUT OFF' >> $i.$sid.sql
if [[ $i then
echo 'execute dbms_lock.sleep(' $wait ')' >> $i.$sid.sql
fi
sqlplus -s $user/$password@$sid @$i.$sid.sql &
done
#
# Monitor ITL wait
#
sqlplus -s $user/$password@$sid SET SERVEROUTPUT ON ECHO OFF TERMOUT ON FEEDBACK OFF HEADING OFF
SELECT * FROM v\$version WHERE rownum = 1;
DECLARE
l_waiter_session v\$session.sid%TYPE := NULL;
l_blocking_session_curr v\$session.blocking_session%TYPE := NULL;
l_blocking_session_prev v\$session.blocking_session%TYPE := NULL;
l_seconds_in_wait_curr v\$session.seconds_in_wait%TYPE := NULL;
l_seconds_in_wait_prev v\$session.seconds_in_wait%TYPE := NULL;
c_sleep CONSTANT NUMBER := 0.1;
c_iterations CONSTANT NUMBER := ceil(($wait-5)/c_sleep);
BEGIN
WHILE l_waiter_session IS NULL
LOOP
BEGIN
SELECT sid INTO l_waiter_session
FROM v\$session
WHERE event = 'enq: TX - allocate ITL entry';
EXCEPTION
WHEN no_data_found THEN NULL;
END;
END LOOP;
FOR i IN 1..c_iterations
LOOP
BEGIN
SELECT blocking_session, seconds_in_wait INTO l_blocking_session_curr, l_seconds_in_wait_curr
FROM v\$session
WHERE sid = l_waiter_session;
EXCEPTION
WHEN no_data_found THEN NULL;
END;
IF l_blocking_session_curr l_blocking_session_prev
OR l_blocking_session_prev IS NULL
OR i = c_iterations
THEN
dbms_output.put_line(to_char(systimestamp,'HH24:MI:SS.FF')||' '||to_char((i-1)*c_sleep,'000000')||
' blocking_session='||nvl(l_blocking_session_prev,l_blocking_session_curr)||
' sleep='||nvl(l_seconds_in_wait_prev,l_seconds_in_wait_curr));
END IF;
l_blocking_session_prev := l_blocking_session_curr;
l_seconds_in_wait_prev := l_seconds_in_wait_curr;
dbms_lock.sleep(c_sleep);
END LOOP;
END;
/
END
#
# Cleanup
#
for i in 1 2 3 4 5 6
do
rm $i.$sid.sql
done
sleep 5
sqlplus -s $user/$password@$sid SET ECHO OFF TERMOUT ON FEEDBACK OFF HEADING OFF
DROP TABLE t PURGE;
END
exit 0
--结果如下：
[oracle11g@hisdg IP=40 ~/test1 18]$ . itl.sh scott tiger ztest 1300
Setup correctly performed: YES
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
09:32:55.610000000 000000 blocking_session=127 sleep=0
09:32:56.611000000 000001 blocking_session=127 sleep=1
09:32:57.611000000 000002 blocking_session=256 sleep=1
09:32:58.611000000 000003 blocking_session=18 sleep=1
09:32:59.611000000 000004 blocking_session=126 sleep=1
09:33:00.611000000 000005 blocking_session=368 sleep=1
09:33:02.611000000 000007 blocking_session=127 sleep=2
09:33:04.611000000 000009 blocking_session=256 sleep=2
09:33:06.611000000 000011 blocking_session=18 sleep=2
09:33:08.612000000 000013 blocking_session=126 sleep=2
09:33:10.612000000 000015 blocking_session=368 sleep=2
09:33:14.612000000 000019 blocking_session=127 sleep=4
09:33:18.612000000 000023 blocking_session=256 sleep=4
09:33:22.612000000 000027 blocking_session=18 sleep=4
09:33:26.613000000 000031 blocking_session=126 sleep=4
09:33:30.613000000 000035 blocking_session=368 sleep=4
09:33:35.613000000 000040 blocking_session=127 sleep=5
09:33:40.613000000 000045 blocking_session=256 sleep=5
09:33:45.615000000 000050 blocking_session=18 sleep=5
09:33:50.615000000 000055 blocking_session=126 sleep=5
09:33:58.615000000 000063 blocking_session=368 sleep=8
09:34:03.616000000 000068 blocking_session=127 sleep=5
09:34:08.616000000 000073 blocking_session=256 sleep=5
09:34:13.616000000 000078 blocking_session=18 sleep=5
09:34:18.617000000 000083 blocking_session=126 sleep=5
09:34:34.620000000 000099 blocking_session=368 sleep=16
09:34:39.621000000 000104 blocking_session=127 sleep=5
09:34:44.621000000 000109 blocking_session=256 sleep=5
09:34:49.623000000 000114 blocking_session=18 sleep=5
09:34:54.625000000 000119 blocking_session=126 sleep=5
09:35:26.627000000 000151 blocking_session=368 sleep=32
09:35:31.628000000 000156 blocking_session=127 sleep=5
09:35:36.628000000 000161 blocking_session=256 sleep=5
09:35:41.628000000 000166 blocking_session=18 sleep=5
09:35:46.629000000 000171 blocking_session=126 sleep=5
09:36:50.632000000 000235 blocking_session=368 sleep=64
09:36:55.633000000 000240 blocking_session=127 sleep=5
09:37:00.634000000 000245 blocking_session=256 sleep=5
09:37:05.634000000 000250 blocking_session=18 sleep=5
09:37:10.634000000 000255 blocking_session=126 sleep=5
09:39:18.644000000 000383 blocking_session=368 sleep=128
09:39:23.644000000 000388 blocking_session=127 sleep=5
09:39:28.645000000 000393 blocking_session=256 sleep=5
09:39:33.647000000 000398 blocking_session=18 sleep=5
09:39:38.651000000 000403 blocking_session=126 sleep=5
09:43:54.671000000 000659 blocking_session=368 sleep=256
09:43:59.672000000 000664 blocking_session=127 sleep=5
09:44:04.672000000 000669 blocking_session=256 sleep=5
09:44:09.672000000 000674 blocking_session=18 sleep=5
09:44:14.673000000 000679 blocking_session=126 sleep=5
09:52:46.709000000 001191 blocking_session=368 sleep=512
09:52:51.710000000 001196 blocking_session=127 sleep=5
09:52:56.710000000 001201 blocking_session=256 sleep=5
09:53:01.712000000 001206 blocking_session=18 sleep=5
09:53:06.712000000 001211 blocking_session=126 sleep=5
09:54:30.618000000 001295 blocking_session=368 sleep=84
--很明显如果我检测时间更长的话（我仅仅检测1300秒），后面不是sleep ＝84。可以发现12c下与11.2.0.3的测试一样。
伪码如下：（来自http://antognini.ch/2011/04/itl-waits-changes-in-recent-releases/）
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 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