Skip to content

performance tuning - 2. page

skip_unusable_indexes参数使用建议

​SKIP_UNUSABLE_INDEXES的使用与索引失效是相关的,该参数10g开始引入,11g默认为TRUE.
当为TRUE时候,如果数据库中存在usable状态的索引,则会自动忽略该索引生成新的执行计划(不走该索引,也不提示该索引的异常);当为False时候,则会报错.我所运维的数据库在一些关键系统中,会将此参数设成False,让系统及时发现索引的异常以便及时去介入修复.
环境各有所异,设置值也可依据实际情况设置.如果sql使用了hint或者涉及到唯一索引的对应DML,该参数会失效.

该参数的一些使用场景可以参考如下的测试:

创建测试表和索引

SQL> conn test/test
已连接。
SQL> drop table a;
表已删除。
SQL> create table a(id number);
表已创建。
SQL> create unique index idx_a_id on a(id);
索引已创建。
SQL> declare
  2  begin
  3  for a in 1..1000 loop
  4  insert into a(id) values(a);
  5  end loop;
  6  end;
  7  /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> show parameter SKIP_UNUSABLE_INDEXES;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
skip_unusable_indexes                boolean     TRUE
SQL> select * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IDX_A_ID |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=1)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
        124  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

修改skip_unusable_indexes为false

SQL> alter system set skip_unusable_indexes=false scope=memory;
系统已更改。
将索引修改为不可用
SQL> alter index idx_a_id unusable;
索引已更改。
出现错误提示索引不可用
SQL> select * from a where id=1;
select * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态

将skip_unusable_indexes修改为true

SQL> alter system set skip_unusable_indexes=true scope=memory;
系统已更改。

对于查询操作此时该sql能够正常运行，但是此时进行的是全表扫描

SQL> select * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    52 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| A    |     4 |    52 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用hint强制使用索引，此时会提示索引无效

SQL> select /*+index(a)*/ * from a where id=1;
select /*+index(a)*/ * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态
--插入操作会出错
SQL> insert into a values(1002);
insert into a values(1002)
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态
SQL> delete from a where id=1;
delete from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态

SQL>

解决方法，重建索引

SQL> alter index test.idx_a_id rebuild;
索引已更改。
SQL> select /*+index(a)*/ * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IDX_A_ID |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=1)

统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> drop index test.idx_a_id;
索引已删除。
SQL> create index test.idx_a_id on a(id);
索引已创建。
SQL> alter index test.idx_a_id unusable;
索引已更改。
SQL> insert into a values(1002);
已创建 1 行。
SQL> commit;

测试证明SKIP_UNUSABLE_INDEXES对于使用hint强制使用索引的语句和唯一索引的插入、删除语句却不能生效。

该测试摘自互联网,同时做了一些修改.

Data access tuning Index Maintenance Oracle performance tuning 数据技术 | Guang Cai Li | |   skip_unusable_indexes

Oracle full table scan 的跟踪监视

最近一个客户的数据库反应不定时的性能问题,经过跟踪发现是一系列的全表扫相关SQL,其中还碰到了比较诡异的全表扫都是顺序读的情况.和周亮以前也讨论过此类情况只是当时这方面的事情并没有太多的眉目,这次跟踪发现了是和undo一致性读有关,还存在大量的row chains.当然这里不讨论这个case,这个case可能会在新书中描述.这个文章不描述具体的原理,只是作为一个对数据库全表扫相关信息收集诊断建议.

在跟踪某一特定的事件时候,有时候没有监控系统或者其他相关捕获信息的手段,则诊断起来比较耗时间,比如跟踪系统中全表扫的情况,默认情况下是只能获取当时时刻具体的情况,这里介绍通过AWR记录中来获取相关的信息,由于AWR从v$sql里面获取的sql是有条件限制的,因此该方法并不能补全数据库中所有的全表扫相关sql(其他情况也是),因为awr收集sql的情况是以statistics_level 的设置为依赖,typical(top 30)或者all(top 100)收集的top sql信息不同.

 

1.周期的全表扫信息

col c1 heading "Day|Hour" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
COUNT(1) c2
FROM dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
WHERE p.object_owner <> 'SYS'
AND p.object_owner = o.owner
AND p.object_name = o.segment_name
AND o.blocks > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Full-table scans Per Snapshot Period
Begin
 Interval FTS
 time Count
 -------------------- --------
 04-10-18 11 4
 04-10-21 17 1
 04-10-21 23 2
 04-10-22 15 2
 04-10-22 16 2
 04-10-22 23 2
 04-10-24 00 2

2.每小时的全表扫信息

col c1 heading "Day|Hour" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Table Full-table scans
Averages per Hour

Day                       FTS
Hour                    Count
-------------------- --------
00                          4
10                          2
11                          4
12                         23
13                         16
14                          6
15                         17
16                         10
17                         17
18                         21
19                          1
23                          6

3.基于星期的全表扫信息

col c1 heading "Week|Day" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'day') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'day')
ORDER BY 1;

查询结果类似如下:

Week                      FTS
Day                     Count
-------------------- --------
sunday                      2
monday                     19
tuesday                    31
wednesday                  34
thursday                   27
friday                     15
Saturday                    2

以上脚本作者为Donald K. Burleson.
我对此脚本做了些变更,除了生成html格式外额外加入索引类的信息,变更的脚本未在blog上发布.

Data access tuning Oracle performance tuning 数据技术 | Guang Cai Li | |   full table scan

索引块上递归事务专用的itl slot争用的识别判断

承接《Oracle10g版本后enq: TX – allocate ITL entry等待事件的根源以及解决思路》

由于itl争用主要是initrans不足（10g后今本不受此影响maxtrans失效），块空间不足原因引起，但是特殊的情况下的索引递归事务引起的itl争用的识别也是需要掌握的技术，虽然大量的递归情况较少见，但如何区分相比前面的2种情况就相对复杂点。主要的思路是根据索引块的itl信息来识别，因为上一篇中讲到在索引的枝节点上，有且只有一个ITL slot，它是用于当发生节点分裂的递归事务(Recursive Transaction)。在叶子节点上，第一条ITL Slot也是用于分裂的递归事务的。只要根据相关索引块的负责递归事务的itl事务槽的使用情况就可以判断争用的情况。

思路如下：
1.找出被阻塞的事务
2.根据阻塞的事务找到相关的回滚块以及相关事务起始回滚编号
3.根据回滚块的内容识别相关的索引块
4.dump出相关的索引块识别对应的itl争用情况

例子：

1.测试阻塞

session 1 更新61080块的100行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=100;

1 row updated.

commit;

session 2 更新61080块的200行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=200;

1 row updated.

commit;

session 3 更新61080块的300行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=300;

1 row updated.

session 4 更新61080块的400行

SQL> update luda set a=a
  2  where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61080
  3  and dbms_rowid.ROWID_ROW_NUMBER(rowid)=400;

1 row updated.

session 5 更新61080块的500行hang住

2.找出被阻塞的事务以及相关回滚段信息

确认itl阻塞：

SQL> select s.sid, s.event, s.row_wait_obj#
  2  from v$session s where s.sid=151;

       SID EVENT                          ROW_WAIT_OBJ#
---------- ------------------------------ -------------
       148 enq: TX - allocate ITL entry              -1

确认对应的阻塞事务的回滚相关信息，可以发现阻塞事务对应的回滚在2号数据文件的2109数据块中，起始的回滚记录是0x2e（46的16进制）

SQL> select l.sid req_session, s.sid lock_session, l.lmode, l.request, t.xidusn, t.xidslot, t.start_ubafil, t.start_ubablk, t.start_ubarec
  2  from v$lock l, v$transaction t, v$session s
  3  where l.type = 'TX'
  4  and trunc(id1/power(2,16)) = t.xidusn
  5  and l.id2 = t.xidsqn
  6  and id1 - power(2,16)*trunc(id1/power(2,16)) = t.xidslot
  7  and t.addr = s.taddr
  8  and l.request = 4;

REQ_SESSION LOCK_SESSION      LMODE    REQUEST     XIDUSN    XIDSLOT START_UBAFIL START_UBABLK START_UBAREC
----------- ------------ ---------- ---------- ---------- ---------- ------------ ------------ ------------
        148          159          0          4          5         26            2         2109           46

dump出2号数据文件的2109号数据块

SQL> alter system dump datafile 2 block 2109;

System altered.

确认相关对象的object_id

SQL> select object_name,object_id,data_object_id from dba_objects where object_name in ('LUDA','IDX_TEST') and owner='SYS';

OBJECT_NAME                               OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ---------- --------------
IDX_TEST                                      51980          51980
LUDA                                          51978          51978

分析2号数据文件的2109号数据块dump文件，从Rec #0x2e部分开始到结束只有0x2e此条与对象号51980，51978相关回滚记录

*-----------------------------
* Rec #0x2e  slt: 0x1a  objn: 51978(0x0000cb0a)  objd: 51978  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x0080083d.014b.2d ctl max scn: 0x0000.000bb362 prv tx scn: 0x0000.000bb37b
txn start scn: scn: 0x0000.000bcdc1 logon user: 0
 prev brb: 8388917 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0002.010.0000013b uba: 0x00800051.0100.3c
                      flg: C---    lkc:  0     scn: 0x0000.000bcd90
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0040ee98  hdba: 0x0040ee91
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 400(0x190) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
Vector content:
col  0: [ 3]  c2 31 06

End dump data blocks tsn: 1 file#: 2 minblk 2109 maxblk 2109

从bdba地址分析可以获得当前更新对象对luda，前面更新提交的1条语句的部分就是luda的61080号块。

SQL> select dbms_utility.data_block_address_file(TO_NUMBER('0040ee98', 'XXXXXXXX')) file_id,
  2  dbms_utility.data_block_address_block(TO_NUMBER('0040ee98', 'XXXXXXXX')) block_id from dual;

   FILE_ID   BLOCK_ID
---------- ----------
         1      61080

在回滚信息中没有发现索引块类型，事务对象只有objno为51978的luda表，类似的索引对象也是用此方法分析。

Data access tuning Index Maintenance Oracle performance tuning 数据技术 | Guang Cai Li |

Oracle10g版本后enq: TX – allocate ITL entry等待事件的根源以及解决思路

客户昨日一套核心系统有进程挂住，top等待事件为enq: TX – allocate ITL entry，判断为itl争用的原因，这方面以前做了不少测试并没有整理，这里顺便做个整理作为诊断手段用。该内容主要分为主要的三个部分：

一.itl的解释以及原理
二.数据块上的initrans不能扩展分配slot导致的itl争用测试
三.索引块上递归事务专用的itl slot争用的识别判断

 

一.ITL原理解释

ITL(Interested Transaction List)是Oracle数据块内部的一个组成部分，用来记录该块所有发生的事务，一个itl可以看作是一个记录，在一个时间，可以记录一个事务（包括提交或者未提交事务）。当然，如果这个事务已经提交，那么这个itl的位置就可以被反复使用了，因为itl类似记录，所以，有的时候也叫itl槽位。Oracle的每个数据块中都有一个或者多个事务槽，每一个对数据块的并发访问事务都会占用一个事务槽。 表和索引的事务槽ini_trans是1、max_trans是255，在oracle10g中，不能修改max_trans这个参数，因为oracle10g忽略了这个参数。如果一个事务一直没有提交，那么，这个事务将一直占用一个itl槽位，itl里面记录了事务信息，回滚段的嵌入口，事务类型等等。如果这个事务已经提交，那么，itl槽位中还保存的有这个事务提交时候的SCN号。如dump一个块，就可以看到itl信息：

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.002.0000158e  0x0080104d.00a1.6e  --U-  734  fsc 0x0000.6c9deff0
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

如果在并发量特别大的系统中，最好分配足够的itl个数（10g之前的版本），其实它并浪费不了太多的空间，或者，设置足够的pctfree，保证itl能扩展，但是pctfree有可能是被行数据给消耗掉的，如update可能一下占满块空间，所以，也有可能导致块内部的空间不够而导致itl等待，所以在通常情况下，10g版本后引起itl等待的原因往往是因为块的空间不足导致，并不是tran事务槽数量不足，在正常情况下2k的数据块最多可以拥有41个itl，4k数据块最多拥有83，8k最多用友169个itl（以itl 24byte为单位）。INITRANS不足的问题不会出现在索引数据块上，当发现没有足够空间分配ITL slot时，无论是枝点块还是叶子块，数据块会发生分裂(Index Block Split)。

有一种特殊情况也会引起ITL的等待，就是在索引上的递归事务itl争用，这种情况比较特殊。在索引的枝节点上，有且只有一个ITL slot，它是用于当发生节点分裂的递归事务(Recursive Transaction)。在叶子节点上，第一条ITL Slot也是用于分裂的递归事务的。在一个用户事务中，如果发生多次分裂，每一次分裂都是由一个单独的递归事务控制的，如果下层节点分裂导致其父节点分裂，它们的分裂则由同一个递归事务控制。当2个事务同时需要分裂一个枝节点或者叶子节点时，或者枝节点下的2个子节点分别被2个事务分裂，就会造成这种ITL等待。

 

2.数据块上的initrans不能扩展分配slot导致的itl争用测试

我们做个测试关于如果数据块没有空间留给itl slot扩展时候的测试，创建表luda，指定pctfree为0，同时指定initrans为1然后填满相关数据块，再对块满的数据进行更新模拟出itl的等待。
创建表luda，并指定pctfree为0，initrans为1

SQL> create table luda(a int) pctfree 0 initrans 1;

Table created.

 

插入大量数据

SQL> begin
2 for i in 1..20000 loop
3 insert into luda values(i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit ;

Commit complete.

 

SQL> select f,b,count(*) from (select dbms_rowid.rowid_relative_fno(rowid) f,dbms_rowid.rowid_block_number(rowid) b
2 from luda) group by f,b order by 3;

F B COUNT(*)
---------- ---------- ----------
1 61101 200
1 61093 734
1 61089 734
1 61095 734
1 61085 734
1 61099 734
1 61074 734
1 61077 734
1 61080 734
1 61092 734
1 61100 734
1 61083 734
1 61091 734
1 61097 734
1 61098 734
1 61075 734
1 61076 734
1 61078 734
1 61081 734
1 61084 734
1 61087 734
1 61096 734
1 61079 734
1 61094 734
1 61088 734
1 61090 734
1 61082 734
1 61086 734

 

插入20018条数据后可以发现该表有26个数据块，填满了除了61101块意外的其他数据块。
接着导出已经填满的数据块61074.

SQL> alter system dump datafile 1 block 61074;

System altered.

 

Block header dump: 0x0040ee92
Object id on Block? Y
seg/obj: 0xcb0a csc: 0x00.bb6a1 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.020.0000013b 0x0080078c.013c.3a --U- 734 fsc 0x0000.000bb765
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--可以发现initrans为1的情况下默认是有2个事务槽，itc=2
data_block_dump,data header at 0xd7fe45c
===============
tsiz: 0x1fa0
hsiz: 0x5ce
pbl: 0x0d7fe45c
bdba: 0x0040ee92
76543210
flag=--------
ntab=1
nrow=734
frre=-1
fsbo=0x5ce
fseo=0xbf8

avsp=0x4
tosp=0x4
0xe:pti[0] nrow=734 offs=0

 

块满的情况测试slot的分配，根据前面的查询结果我们知道单个块的存储行数为734行，也可以通过dump中的nrow=734得知，所以我们在这部测试中依次更新第100，200，300行的数据。

session 1 更新第100行的数据：

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61074
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=100;

1 row updated.

 

session 2更新第200行的数据：

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61074
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=200;

1 row updated.

 

session 3更新第300行的数据，session3的sid为158，并且在执行过程中session 3 hang住：

SQL> select sid from v$mystat where rownum=1;

SID
----------
158

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61074
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=300;
--此时进程hang住
 

alter system dump datafile 1 block 61074；

 

Block header dump: 0x0040ee92
Object id on Block? Y
seg/obj: 0xcb0a csc: 0x00.bb97e itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.014.00000159 0x008006bb.01f1.12 ---- 1 fsc 0x0000.00000000
0x02 0x0005.00e.0000013c 0x0080083c.014b.20 ---- 1 fsc 0x0000.00000000
--通过此时的dump我们也可以发现原先为被占用的2个事务槽已经被占用而且事务未提交。
data_block_dump,data header at 0xd77645c
===============
tsiz: 0x1fa0
hsiz: 0x5ce
pbl: 0x0d77645c
bdba: 0x0040ee92
76543210
flag=--------
ntab=1
nrow=734
frre=-1
fsbo=0x5ce
fseo=0xbf8
avsp=0x4
tosp=0x4
0xe:pti[0] nrow=734 offs=0

 

 
--查询158进程的等待事件为itl的相关等待事件enq: TX - allocate ITL entry
SQL> select sid,event from v$session where sid=158;

SID EVENT
---------- ----------------------------------------------------------------
158 enq: TX - allocate ITL entry

从以上验证了空间不足的情况下会导致itl无法分配引起enq: TX – allocate ITL entry等待事件的产生。

接下来测试块不满的情况，在表luda中目前有数据的块为26个，其中块号为61101的块只有200条数据，只占用该块30%的空间，为了测试需要对61101号块采用4个session分别对第10，20，30，40行进行更新：

session 1：

 

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=10;

1 row updated.

 

 

session2：

 

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=20;

1 row updated.

 

session3：

 

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=30;

1 row updated.

 

session4：

SQL> update luda set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61101
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=40;

1 row updated.

 

以上4个session都没有遇到阻塞，导出61101号块可以发现该块有4个itl slot，自动扩展了2个slot，验证了在空间足够的情况下itl slot会自动扩展。

SQL> alter system dump datafile 1 block 61101;

System altered.

 

--

Block header dump: 0x0040eead
Object id on Block? Y
seg/obj: 0xcb0a csc: 0x00.bc003 itc: 4 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.014.00000159 0x008006bb.01f1.13 ---- 1 fsc 0x0000.00000000
0x02 0x0009.016.0000013b 0x0080075c.0143.37 ---- 1 fsc 0x0000.00000000
0x03 0x0005.00e.0000013c 0x0080083c.014b.21 ---- 1 fsc 0x0000.00000000
0x04 0x0002.026.00000139 0x00800130.00fc.09 ---- 1 fsc 0x0000.00000000
--itc=4，在61101块上存在4条未提交的事务，分别是我们刚才执行的sesson1-4.

data_block_dump,data header at 0xe17048c
===============
tsiz: 0x1f70
hsiz: 0x1a2
pbl: 0x0e17048c
bdba: 0x0040eead
76543210
flag=--------
ntab=1
nrow=200
frre=-1
fsbo=0x1a2
fseo=0x1957
avsp=0x16c6
tosp=0x16c6
0xe:pti[0] nrow=200 offs=0

 

以上测试说明了在Oracle10g以来，itl的争用事件产生在数据块上主要是受块空间的影响，如果大量事务在相关block上执行操作但是当块无法满足分配事务槽，则会产生itl相关争用。
下一节主要是区分是索引块还是表数据块的争用. http://www.ludatou.com/?p=1920

Buffer cache Data access tuning Database Instance tuning Oracle Oracle 排障配置与调整 performance tuning 浅谈oracle技术系列 等待事件 | Guang Cai Li |