[20120224]itl的問題.txt

昨天看awr報表，發現ITL waits,很少在前5位看到這個等待事件，當然解決很簡單，是一個很小的表，僅僅佔用1塊，開發人員把它當作seq使用，真不知道程式設計師怎麼想的？？？解決方法很簡單，加大pctfree，然後move，在rebuild索引就ok了。

我仔細看了裡面相關的語句，僅僅有一個事務有點慢，其它都很快，正常ITL waits不應該排這麼前，感覺有點奇怪。作了一個測試：


1.建立測試環境:

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2.建立測試資料:
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槽，不能在增加空間分配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;


也許還有些空間，能容納3個ITL槽，在第4個會話的時候掛起。

在開啟一個會話5執行,可以發現出現enq: TX - allocate ITL entry等待事件:
SQL> SELECT SID,SEQ#,EVENT FROM V$SESSION_WAIT WHERE event NOT IN (SELECT NAME FROM v$event_name WHERE wait_class = 'Idle');

       SID       SEQ# EVENT
---------- ---------- ----------------------------------------
       138        148 enq: TX - allocate ITL entry
        71        124 asynch descriptor resize

使用strace監測掛起程式：

$ strace -ttT -p 14378
Process 14378 attached - interrupt to quit
14:53:50.649628 gettimeofday({1330066430, 649778}, NULL) = 0 <0.000055>
14:53:50.649912 gettimeofday({1330066430, 649983}, NULL) = 0 <0.000056>
14:53:50.650084 gettimeofday({1330066430, 650115}, NULL) = 0 <0.000023>
14:53:50.650185 semtimedop(5210112, 0x7fbfff5748, 548682028960, NULL) = -1 EAGAIN (Resource temporarily unavailable) <3.001094>
14:53:53.651455 gettimeofday({1330066433, 651528}, NULL) = 0 <0.000054>
14:53:53.651624 gettimeofday({1330066433, 651702}, NULL) = 0 <0.000067>
14:53:53.651784 getrusage(RUSAGE_SELF, {ru_utime={0, 25996}, ru_stime={0, 22996}, ...}) = 0 <0.000052>
14:53:53.651972 gettimeofday({1330066433, 652035}, NULL) = 0 <0.000072>
14:53:53.652109 getrusage(RUSAGE_SELF, {ru_utime={0, 25996}, ru_stime={0, 22996}, ...}) = 0 <0.000018>
14:53:53.652259 gettimeofday({1330066433, 652300}, NULL) = 0 <0.000027>
14:53:53.652363 semtimedop(5210112, 0x7fbfff5748, 548682028960, NULL) = -1 EAGAIN (Resource temporarily unavailable) <3.001427>
14:53:56.653983 gettimeofday({1330066436, 654065}, NULL) = 0 <0.000062>
14:53:56.654174 gettimeofday({1330066436, 654235}, NULL) = 0 <0.000049>
14:53:56.654341 getrusage(RUSAGE_SELF, {ru_utime={0, 25996}, ru_stime={0, 22996}, ...}) = 0 <0.000064>
14:53:56.654522 gettimeofday({1330066436, 654590}, NULL) = 0 <0.000054>
14:53:56.654678 getrusage(RUSAGE_SELF, {ru_utime={0, 25996}, ru_stime={0, 22996}, ...}) = 0 <0.000055>
14:53:56.654873 gettimeofday({1330066436, 654951}, NULL) = 0 <0.000061>
14:53:56.655037 semtimedop(5210112, 0x7fbfff5748, 548682028960, NULL) = -1 EAGAIN (Resource temporarily unavailable) <3.001270>
14:53:59.656491 gettimeofday({1330066439, 656568}, NULL) = 0 <0.000053>
14:53:59.656674 gettimeofday({1330066439, 656735}, NULL) = 0 <0.000049>
14:53:59.656839 getrusage(RUSAGE_SELF, {ru_utime={0, 25996}, ru_stime={0, 22996}, ...}) = 0 <0.000060>
14:53:59.657013 gettimeofday({1330066439, 657090}, NULL) = 0 <0.000067>
14:53:59.657178 getrusage(RUSAGE_SELF, {ru_utime={0, 25996}, ru_stime={0, 22996}, ...}) = 0 <0.000052>
14:53:59.657372 gettimeofday({1330066439, 657450}, NULL) = 0 <0.000068>
14:53:59.657538 semtimedop(5210112, 0x7fbfff5748, 548682028960, NULL
Process 14378 detached

--可以發現系統呼叫semtimedop，這是一個linux的sleep訊號，等待3秒再檢測。

4.開始rollback操作:

開啟會話1：
rollback ;

--檢查會話4,會話4依舊掛起！

開啟會話2：
rollback ;

--檢查會話4,會話4依舊掛起！

開啟會話3：
rollback ;

--檢查會話4,會話4執行！
--可以發現，會話4必須要等待會話3結束事務，才能繼續操作。

5.測試有5個會話的情況。

--修改資料，不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;

開啟會話5：
update t set name='TEST' where id=5;

在第4，5個會話的時候掛起。

開啟會話1：
rollback ;

--檢查會話4,5會話4依舊掛起，會話5執行！

開啟會話2：
rollback ;

--檢查會話4,會話4居然執行了！
--反覆測試，僅僅在出現多個ITL等待時，前面的情況才不會出現！