ITL与pctfree

某些生产系统ITL 的等待比较多。

dba_tables 中INI_TRANS参数默认值为1,这个参数是不生效的，实际默认情况每个ini_trans为2

就是每个块中默认有两个事物槽，如果该块有update或者delete等操作，通过ITL，去更新undo相关块的内容。
2个事物槽，只能支持一个块上的2个事物。

 

如果该块有空闲，则可以自动创建新的ITL。

没有空闲空间的情况就会发生ITL等待，前两个事物的ITL必须释放后，第三个事物才能获得ITL。

我试验的情况为pctfree 为0 ,即该块完全被使用
创建一个表

0924:15i:19 SQL> create table test(id int) pctfree 0;
 
Table created
 
Executed in 0.031 seconds
 

插入200条数据
0924:15i:30 SQL> begin
              2  for i in 1..200 loop
              3  insert into test values(i);
              4  end loop;
              5  end;
              6  /
 
PL/SQL procedure successfully completed
 
Executed in 0.172 seconds
 
0924:15i:58 SQL> exec dbms_stats.gather_table_stats('JIEBIN','TEST');
 
PL/SQL procedure successfully completed
 
Executed in 0.141 seconds
 

表test占用3个块。INI_TRNAS为1（实际不生效，值为2），表示该块如果被填满，只能有两个事物。

0924:16i:21 SQL> select * from dba_tables where wner='JIEBIN';
 
OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING BACKED_UP   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE                                   INSTANCES                                CACHE                TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE     TEMPORARY SECONDARY NESTED BUFFER_POOL ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION        SKIP_CORRUPT MONITORING CLUSTER_OWNER                  DEPENDENCIES COMPRESSION DROPPED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------- --------- ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ---------------------------------------- ---------------------------------------- -------------------- ---------- ----------- ------------- ----------- ------------ --------- --------- ------ ----------- ------------ ------------ ---------- --------------- ------------ ---------- ------------------------------ ------------ ----------- -------
JIEBIN                         JIEBIN2                        SYSTEM                                                                                       VALID            10         40          1        255          65536                       1  2147483645                       1               1 YES     N                                                                                                                                     1                                        1                                   N                ENABLED                              NO                       N         N         NO     DEFAULT     DISABLED     NO           NO                         DISABLED     YES                                       DISABLED     DISABLED    NO
JIEBIN                         P_TEST                                                                                                                      VALID                                                                                                                                                   N                                                                                                                                     1                                        1                                   N                ENABLED                              YES                      N         N         NO                 DISABLED     NO           NO                         DISABLED     YES                                       DISABLED                 NO
JIEBIN                         TEST                           SYSTEM                                                                                       VALID             0         40          1        255          65536                       1  2147483645                       1               1 YES     N               2000          3            0          0          0           3                         0                   0          1                                        1                                   N                ENABLED           2000 2010-07-28 17 NO                       N         N         NO     DEFAULT     DISABLED     YES          NO                         DISABLED     YES                                       DISABLED     DISABLED    NO
 
3 rows selected
 
Executed in 0.594 seconds
select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) from test group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid);
 
1024:29i:27 SQL>
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(   COUNT(*)
------------------------------ ----------
                        192843        734
                        192844        532
                        192842        734
 
3 rows selected
 
Executed in 0.453 seconds
 
表test占用了3个数据块，192842，192843数据块是满的，如果itl用完以后，无法创建新的itl

第一个session
update test set id=2 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)=192842 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=1  ;
第二个session
update test set id=2 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)=192842 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=2  ;

第三个session
update test set id=2 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)=192842 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3  ;
第四个session
update test set id=2 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)=192842 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=4  ;
.
.
.
.
.

第三个session以后都会hang住，直到上面的ITL使用完释放。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8135069/viewspace-669670/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8135069/viewspace-669670/