解决enq: TX - allocate ITL entry等待导致的性能问题 - 编程

某个系统dbtime过高，经分析发现大量的ITL等待，确认这条SQL为insert操作，并发用户大概100多个，最终通过修改ITL相关参数解决，
下面时处理过程
查询等待事件。
SQL> select sql_id,event,count(*) from v$active_session_history where sample_time > to_date('202106070800','yyyymmddhh24mi')
and event like '%TX%' group by sql_id,event having count(*)>10 order by count(*)

SQL_ID EVENT COUNT(*)
------------- ------------------------------ ----------
24hyhfzgy4gqv enq: TX - row lock contention 497
3mfn9a6br4xxp enq: TX - row lock contention 994
4ukb8t5z1pu20 enq: TX - allocate ITL entry 37921

查段对应类型
SQL>select segment_name,bytes/1024/1024 ,owner,segment_type from dba_segments where segment_name='resource_TB_INVOICE_MAILING'

SEGMENT_NAME BYTES/1024/1024 OWNER SEGMENT_TYPE
------------------------------ --------------- ------------------------------ ------------------
resource_TB_INVOICE_MAILING 248 resource TABLE
resource_TB_INVOICE_MAILING 30 resource INDEX

确定SQL语句
SQL_ID 4ukb8t5z1pu20, child number 0
-------------------------------------
INSERT INTO resource_TB_TRACKING_DATA ( ID, OPERATIONADDRESS,
OPERATIONDES, COURIERMOBILE, TXLOGISTICID, OPERATIONMAN,
OPERATIONTIME, STATUS ) VALUES ( :1 , :2 , :3 , :4 , :5 ,
:6 , :7 , :8 )

------------------------------------------------------------------
| Id | Operation | Name | Cost |
------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | resource_TB_TRACKING_DATA | |
------------------------------------------------------------------

查该SQL对应的等待事件和用户数
SQL> select session_id,event,count(*) from v$active_session_history where sample_time > to_date('202106070800','yyyymmddhh24mi')
2* and event like '%TX%' and sql_id='4ukb8t5z1pu20' group by session_id,event having count(*)>10 order by count(*)
SQL> /

SESSION_ID EVENT COUNT(*)
---------- ---------------------------------------------------------------- ----------
2230 enq: TX - allocate ITL entry 16
3078 enq: TX - allocate ITL entry 46
993 enq: TX - allocate ITL entry 50
3524 enq: TX - allocate ITL entry 85
1088 enq: TX - allocate ITL entry 120
2895 enq: TX - allocate ITL entry 138
422 enq: TX - allocate ITL entry 155
3936 enq: TX - allocate ITL entry 155
2513 enq: TX - allocate ITL entry 190
3746 enq: TX - allocate ITL entry 194
1186 enq: TX - allocate ITL entry 211
3371 enq: TX - allocate ITL entry 225
1846 enq: TX - allocate ITL entry 229
2324 enq: TX - allocate ITL entry 260
1372 enq: TX - allocate ITL entry 294
3937 enq: TX - allocate ITL entry 312
1656 enq: TX - allocate ITL entry 329
325 enq: TX - allocate ITL entry 364
226 enq: TX - allocate ITL entry 372
600 enq: TX - allocate ITL entry 372
......
3823 enq: TX - allocate ITL entry 372
4120 enq: TX - allocate ITL entry 372

分析：并发会话的insert操作造成等待enq: TX - allocate ITL entry
解决方法：修改表对应块的INITRANS 参数，提高应对并发事务的能力
SQL> alter table resource.resource_TB_TRACKING_DATA INITRANS 100; <<<<修改初始事务槽数量

Table altered.

SQL> alter table resource.resource_TB_TRACKING_DATA move ; <<<<二手域名买卖重建表再次初始化数据块，否则只对新块有效

Table altered.

SQL> alter index resource.GTTD_LOGISTISCID_INDEX rebuild INITRANS 100;
alter index resource.GTTD_LOGISTICSID_STATUS_INDEX rebuild INITRANS 100;
alter index resource.resource_TB_TRACKING_DATA_PK rebuild INITRANS 100;

Index altered.
Index altered.
Index altered.

SQL> select owner,ini_trans ,max_trans from dba_tables where table_name='resource_TB_TRACKING_DATA'

OWNER INI_TRANS MAX_TRANS
-------------------- ---------- ----------
resource 100 255

业务重启，再次检查等待事件

SQL> select sql_id,event,count(*) from v$active_session_history where sample_time > to_date('202106071020','yyyymmddhh24mi')
and event like '%TX%' group by sql_id,event having count(*)>10 order by count(*)

no rows selected

select * from table(dbms_xplan.display_cursor('&sql_id',null,'allstats last'));

备注：
这里只修改了inittrans参数，也可以同时减少数据块中有效数据的空间大小，也就是增加pctfree的比例
alter table resource.resource_TB_TRACKING_DATA PCTFREE 20;
重建对应索引，重新组织数据块
alter index index_name rebuild online PCTFREE 20;

两个方案都有效，可以使用一个方案，也可以两个一起使用，看实际需求吧。

解决enq: TX - allocate ITL entry等待导致的性能问题

更多相关文章

随机推荐