记录一次生产环境enq: TX - allocate ITL entry等待事件
故障现象
昨天中午接到主数据系统运维人员的电话，说系统访问繁忙。前台操作非常卡。
故障排查
远程登录到数据库进行排查，操作系统层面，CPU使用率不高，IO等待也不高。
[oracle@md-db-out ~]$ top top - 16:01:10 up 1435 days, 4:56, 2 users, load average: 5.08, 5.21, 5.10 Tasks: 669 total, 8 running, 661 sleeping, 0 stopped, 0 zombie Cpu(s): 12.9%us, 5.5%sy, 0.0%ni, 74.5%id, 7.1%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 66108672k total, 59016252k used, 7092420k free, 50152k buffers Swap: 33554424k total, 4419336k used, 29135088k free, 53959588k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 23411 oracle 20 0 28.5g 375m 370m S 73.6 0.6 0:40.90 oracle 23437 oracle 20 0 28.5g 374m 369m S 41.9 0.6 0:37.52 oracle 23383 oracle 20 0 28.5g 428m 423m S 34.3 0.7 1:22.12 oracle 23471 oracle 20 0 28.5g 351m 346m R 17.8 0.5 0:31.67 oracle 23413 oracle 20 0 28.5g 379m 374m S 14.9 0.6 0:45.33 oracle 23395 oracle 20 0 28.5g 390m 385m R 12.9 0.6 1:05.33 oracle 7558 oracle 20 0 28.5g 14g 14g R 10.6 22.5 7753:24 oracle 7560 oracle 20 0 28.5g 14g 14g D 10.2 22.6 7817:57 oracle 23561 oracle 20 0 28.5g 325m 320m R 8.6 0.5 0:12.36 oracle 23595 oracle 20 0 28.5g 278m 273m S 7.9 0.4 0:04.89 oracle 23465 oracle 20 0 28.5g 362m 357m R 7.3 0.6 0:34.52 oracle 23457 oracle 20 0 28.5g 362m 357m S 6.9 0.6 0:47.48 oracle 23611 oracle 20 0 28.5g 353m 347m S 6.3 0.5 0:24.75 oracle 23455 oracle 20 0 28.5g 371m 366m S 5.6 0.6 0:31.22 oracle 23781 oracle 20 0 28.5g 245m 242m S 5.0 0.4 0:03.45 oracle 21116 oracle 20 0 28.5g 472m 467m R 4.6 0.7 1:26.28 oracle 23597 oracle 20 0 28.5g 194m 191m S 4.3 0.3 0:01.28 oracle 23052 oracle 20 0 28.5g 64m 58m S 3.6 0.1 0:44.37 oracle 23469 oracle 20 0 28.5g 351m 347m S 3.3 0.5 0:25.80 oracle 23453 oracle 20 0 28.5g 360m 354m S 2.6 0.6 0:32.95 oracle 23555 oracle 20 0 28.5g 306m 300m R 2.6 0.5 0:08.98 oracle 23372 oracle 20 0 28.5g 401m 396m S 2.3 0.6 1:02.56 oracle 7544 oracle -2 0 28.5g 14m 14m S 1.3 0.0 24050:49 oracle 23477 oracle 20 0 28.5g 344m 339m S 1.0 0.5 0:24.53 oracle 106 root 20 0 0 0 0 S 0.7 0.0 207:00.19 kblockd/0 7562 oracle 20 0 28.5g 80m 79m S 0.7 0.1 3635:59 oracle 24621 oracle 20 0 15436 1728 948 R 0.7 0.0 0:00.06 top 24623 oracle 20 0 28.5g 23m 21m S 0.7 0.0 0:00.02 oracle 68 root 20 0 0 0 0 S 0.3 0.0 137:54.58 events/1 79 root 20 0 0 0 0 S 0.3 0.0 147:43.38 events/12 108 root 20 0 0 0 0 S 0.3 0.0 179:19.11 kblockd/2 114 root 20 0 0 0 0 S 0.3 0.0 173:33.90 kblockd/8 1972 root 20 0 243m 1972 1600 S 0.3 0.0 2416:34 vmtoolsd 7554 oracle 20 0 28.5g 386m 381m S 0.3 0.6 4239:00 oracle 10776 root 20 0 450m 5048 4364 S 0.3 0.0 74:24.87 sdmonitor 10812 root 20 0 412m 5928 5424 S 0.3 0.0 216:24.16 sdexam 22462 oracle 20 0 28.5g 404m 399m S 0.3 0.6 0:46.84 oracle 22550 oracle 20 0 28.5g 421m 416m S 0.3 0.7 1:06.66 oracle 23303 oracle 20 0 28.5g 424m 419m S 0.3 0.7 1:32.51 oracle 24605 oracle 20 0 28.5g 16m 14m S 0.3 0.0 0:00.02 oracle 1 root 20 0 19364 1116 892 S 0.0 0.0 35:09.70 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd 3 root RT 0 0 0 0 S 0.0 0.0 6:38.81 migration/0 4 root 20 0 0 0 0 S 0.0 0.0 14:13.53 ksoftirqd/0 5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 6 root RT 0 0 0 0 S 0.0 0.0 29:43.94 watchdog/0 7 root RT 0 0 0 0 S 0.0 0.0 3:13.64 migration/1 8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1 9 root 20 0 0 0 0 S 0.0 0.0 12:04.40 ksoftirqd/1 10 root RT 0 0 0 0 S 0.0 0.0 21:52.21 watchdog/1 11 root RT 0 0 0 0 S 0.0 0.0 7:46.96 migration/2 12 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/2 13 root 20 0 0 0 0 S 0.0 0.0 13:01.99 ksoftirqd/2 14 root RT 0 0 0 0 S 0.0 0.0 20:55.94 watchdog/2 15 root RT 0 0 0 0 S 0.0 0.0 5:34.48 migration/3 16 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/3 17 root 20 0 0 0 0 S 0.0 0.0 10:52.38 ksoftirqd/3 18 root RT 0 0 0 0 S 0.0 0.0 22:09.18 watchdog/3 19 root RT 0 0 0 0 S 0.0 0.0 7:16.79 migration/4 20 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/4 21 root 20 0 0 0 0 S 0.0 0.0 7:31.88 ksoftirqd/4 22 root RT 0 0 0 0 S 0.0 0.0 20:22.17 watchdog/4 23 root RT 0 0 0 0 S 0.0 0.0 2:15.69 migration/5 24 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/5 25 root 20 0 0 0 0 S 0.0 0.0 8:09.24 ksoftirqd/5 26 root RT 0 0 0 0 S 0.0 0.0 21:39.50 watchdog/5 27 root RT 0 0 0 0 S 0.0 0.0 7:03.89 migration/6 28 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/6 29 root 20 0 0 0 0 S 0.0 0.0 6:30.98 ksoftirqd/6 30 root RT 0 0 0 0 S 0.0 0.0 20:10.04 watchdog/6 31 root RT 0 0 0 0 S 0.0 0.0 0:49.83 migration/7 32 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/7 33 root 20 0 0 0 0 S 0.0 0.0 5:21.37 ksoftirqd/7 34 root RT 0 0 0 0 S 0.0 0.0 20:53.45 watchdog/7 35 root RT 0 0 0 0 S 0.0 0.0 9:39.79 migration/8 [oracle@md-db-out ~]$
进入数据库层面继续排查，查看告警文件alter_mddb.log，发现有大量的ORA-00060报错
在登录数据库查询当前等待事件。发现有很多enq: TX - allocate ITL entry，ITL事务槽分配的等待。
[oracle@md-db-out trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 30 16:02:08 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200
col sid for 999999
col event for a40
col p1text for a15
col p1 for 99999999999
col p1raw for a20
select sid,event,p1text,p1,p1raw from v$session_wait
where event not in
('SQL*Net message from client','rdbms ipc message',
'SQL*Net message to client','pmon timer','smon timer',
'wakeup time manager','Streams AQ: qmn slave idle wait',
'jobq slave wait','Streams AQ: qmn coordinator idle wait'
,'gcs remote message','ges remote message','DIAG idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'SQL*Net more data from client',
'SQL*Net more data to client');SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11
SID EVENT P1TEXT P1 P1RAW
------- ---------------------------------------- --------------- ------------ --------------------
9 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
21 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
27 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
88 free buffer waits file# 5 0000000000000005
97 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
99 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
108 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
109 free buffer waits file# 5 0000000000000005
112 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
157 free buffer waits file# 5 0000000000000005
164 free buffer waits file# 5 0000000000000005
SID EVENT P1TEXT P1 P1RAW
------- ---------------------------------------- --------------- ------------ --------------------
167 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
169 db file sequential read file# 5 0000000000000005
181 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
186 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
238 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
251 free buffer waits file# 5 0000000000000005
260 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
309 VKTM Logical Idle Wait 0 00
316 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
322 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
328 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
SID EVENT P1TEXT P1 P1RAW
------- ---------------------------------------- --------------- ------------ --------------------
340 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
347 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
393 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
408 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
425 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
489 free buffer waits file# 5 0000000000000005
491 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
496 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
555 free buffer waits file# 5 0000000000000005
574 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
625 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
SID EVENT P1TEXT P1 P1RAW
------- ---------------------------------------- --------------- ------------ --------------------
632 free buffer waits file# 5 0000000000000005
654 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
718 Space Manager: slave idle wait Slave ID 0 00
724 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
730 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
771 db file async I/O submit requests 96 0000000000000060
774 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
782 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
790 free buffer waits file# 5 0000000000000005
793 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
848 db file async I/O submit requests 107 000000000000006B
SID EVENT P1TEXT P1 P1RAW
------- ---------------------------------------- --------------- ------------ --------------------
852 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
870 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
926 free buffer waits file# 5 0000000000000005
954 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1004 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1009 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1011 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1012 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1019 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1083 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1102 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
SID EVENT P1TEXT P1 P1RAW
------- ---------------------------------------- --------------- ------------ --------------------
1111 free buffer waits file# 5 0000000000000005
1159 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1177 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
1178 enq: TX - allocate ITL entry name|mode 1415053316 0000000054580004
59 rows selected.
SQL>
在查看一下等待事件对应的SQL语句。找到对应的SOA_EX_DRUG表，并查看对应SQL执行计划，在查询一下SOA_EX_DRUG表中对应INITRANS和PCT_FREE
SQL> select /*+ ORDERED */ sql_text from v$sqltext a
where (a.hash_value, a.address) in
(select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode(sql_hash_value, 0, prev_sql_addr, sql_address)
from v$session b
where b.sid = &sid)
order by piece asc;
2 3 4 5 6 7 Enter value for sid: 9
old 6: where b.sid = &sid)
new 6: where b.sid = 9)
SQL_TEXT
----------------------------------------------------------------
update SOA_EX_DRUG set PRDCT_PLT = :1 where ID
= :2
SQL> VARIABLE a1 VARCHAR2(200);
SQL> VARIABLE a2 NUMBER;
SQL> set autotrace trace exp
SQL> update MDMEXTERNAL.SOA_EX_DRUG set PRDCT_PLT = :a1 where ID = :a2;
0 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 99234203
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 40 | 2 (0)| 00:00:01 |
| 1 | UPDATE | SOA_EX_DRUG | | | | |
|* 2 | INDEX UNIQUE SCAN| SOA_EX_DRUG_PK | 1 | 40 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=TO_NUMBER(:A2))
SQL> set autot off
SQL> select owner,table_name,pct_free,ini_trans from dba_tables where table_name='SOA_EX_DRUG';
OWNER TABLE_NAME PCT_FREE INI_TRANS
------------------------------ ------------------------------ ---------- ----------
MDMEXTERNAL SOA_EX_DRUG 10 1
SQL>
获取一份当天业务高峰10点-11点时间段的awr报告，截取报告重要部分。
问题分析
根据上面现象看update SOA_EX_DRUG set PRDCT_PLT = :1 where ID = :2语句非常简单而且是走的是唯一索引，主要问题是在update SOA_EX_DRUG操作过程中initrans不能扩展分配slot导致的itl争用。
解决enq: TX - allocate ITL entry等待事件问题，可能是开发逻辑问题导致的数据库并发行锁，需要开发进行调整优化。这里数据库层面mos 也有一篇
文档介绍如何解决Troubleshooting waits for ‘enq: TX - allocate ITL entry’ (Doc ID 1472175.1)。
此等待事件通常由以下原因引起：
- ITL 数量不足：数据块中初始分配的 ITL 条目数不足
- 高并发事务：同一数据块上有过多并发事务
- 长时间运行的事务：事务持有 ITL 条目时间过长
- 频繁的 DML 操作：对同一数据块的频繁修改
这里我们先选择数据库层面对表进行的改造。
问题解决
通知业务晚上21点停机进行维护，重启数据库，关闭监听，备份原表SOA_EX_DRUG
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@md-db-out trace]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-JUN-2025 21:20:00
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=md-db-out)(PORT=1521)))
The command completed successfully
[oracle@md-db-out trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 30 21:20:14 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 3.0198E+10 bytes
Fixed Size 2267952 bytes
Variable Size 3556771024 bytes
Database Buffers 2.6575E+10 bytes
Redo Buffers 63631360 bytes
Database mounted.
Database opened.
SQL> create directory dir as '/home/oracle';
Directory created.
SQL> grant read,write on directory dir to public;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@md-db-out trace]$ expdp \'/ as sysdba\' directory=dir dumpfile=SOA_EX_DRUG_%U.dmp logfile=expdp.log tables=MDMEXTERNAL.SOA_EX_DRUG
Export: Release 11.2.0.4.0 - Production on Mon Jun 30 21:26:25 2025
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=dir dumpfile=SOA_EX_DRUG_%U.dmp logfile=expdp.log tables=MDMEXTERNAL.SOA_EX_DRUG
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 749 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MDMEXTERNAL"."SOA_EX_DRUG" 674.9 MB 2473456 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/SOA_EX_DRUG_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 30 21:26:55 2025 elapsed 0 00:00:27
[oracle@md-db-out trace]$
将原表SOA_EX_DRUG的PCTFREE调整到20，INITRANS调整到50。
[oracle@md-db-out trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 30 21:27:33 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter table MDMEXTERNAL.SOA_EX_DRUG PCTFREE 20 INITRANS 50;
Table altered.
SQL>
对原表SOA_EX_DRUG进行重组
SQL> alter table MDMEXTERNAL.SOA_EX_DRUG move;
Table altered.
SQL>
对原表SOA_EX_DRUG所有索引进行重建并调整PCTFREE调整到20，INITRANS调整到50。
SQL> drop index MDMEXTERNAL.INDEX_CHANGE_LOG;
Index dropped.
SQL> create index MDMEXTERNAL.INDEX_CHANGE_LOG on MDMEXTERNAL.SOA_EX_DRUG (ID, DRUG_ID, CMM_NAME, APRV_NO)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.INDEX_MEDCTYPE;
Index dropped.
SQL> create index MDMEXTERNAL.INDEX_MEDCTYPE on MDMEXTERNAL.SOA_EX_DRUG (MED_CTGY_TYPE)
tablespace TBS_EXTERNAL_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.INDEX_MEDCTYPE;
Index dropped.
SQL> create index MDMEXTERNAL.INDEX_MEDCTYPE on MDMEXTERNAL.SOA_EX_DRUG (MED_CTGY_TYPE)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.INDEX_MNFCT_ID;
Index dropped.
SQL> create index MDMEXTERNAL.INDEX_MNFCT_ID on MDMEXTERNAL.SOA_EX_DRUG (PRDCT_ID)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.INDEX_PR_CN_PK;
Index dropped.
SQL> create index MDMEXTERNAL.INDEX_PR_CN_PK on MDMEXTERNAL.SOA_EX_DRUG (PRDCT_PLT, CNTRY_NAME)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.SOA_EX_DRUG_ACTUAL_PRDCT_PLT;
Index dropped.
SQL> create index MDMEXTERNAL.SOA_EX_DRUG_ACTUAL_PRDCT_PLT on MDMEXTERNAL.SOA_EX_DRUG (ACTUAL_PRDCT_PLT)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.SOA_EX_DRUG_APPO;
Index dropped.
SQL> create index MDMEXTERNAL.SOA_EX_DRUG_APPO on MDMEXTERNAL.SOA_EX_DRUG (APRV_NO)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.SOA_EX_DRUG_CMM_NAME_INDEX;
Index dropped.
SQL> create index MDMEXTERNAL.SOA_EX_DRUG_CMM_NAME_INDEX on MDMEXTERNAL.SOA_EX_DRUG (CMM_NAME)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.SOA_EX_DRUG_PK;
Index dropped.
SQL> create unique index MDMEXTERNAL.SOA_EX_DRUG_PK on MDMEXTERNAL.SOA_EX_DRUG (ID)
tablespace IDX_EXTERNAL_INDEX_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 160K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> drop index MDMEXTERNAL.SOA_EX_DRUG_PRDCT_PLT;
Index dropped.
SQL> create index MDMEXTERNAL.SOA_EX_DRUG_PRDCT_PLT on MDMEXTERNAL.SOA_EX_DRUG (PRDCT_PLT)
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> alter table MDMEXTERNAL.SOA_EX_DRUG drop constraint DRUG_ID_ONLY;
Table altered.
SQL> alter table MDMEXTERNAL.SOA_EX_DRUG
add constraint DRUG_ID_ONLY unique (DRUG_ID)
using index
tablespace TBS_EXTERNAL_DATA
pctfree 20
initrans 50
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12 13 14
Table altered.
SQL>
检查一下SOA_EX_DRUG表和索引参数是否已调整
SQL> set linesize 300
SQL> select owner,table_name,pct_free,ini_trans from dba_tables where table_name='SOA_EX_DRUG';
OWNER TABLE_NAME PCT_FREE INI_TRANS
------------------------------ ------------------------------ ---------- ----------
MDMEXTERNAL SOA_EX_DRUG 20 50
SQL> select index_name,pct_free,ini_trans from dba_indexes where table_name='SOA_EX_DRUG';
INDEX_NAME PCT_FREE INI_TRANS
------------------------------ ---------- ----------
INDEX_MNFCT_ID 20 50
INDEX_CHANGE_LOG 20 50
SOA_EX_DRUG_CMM_NAME_INDEX 20 50
SOA_EX_DRUG_PRDCT_PLT 20 50
SOA_EX_DRUG_PK 20 50
SOA_EX_DRUG_APPO 20 50
INDEX_MEDCTYPE 20 50
INDEX_PR_CN_PK 20 50
SOA_EX_DRUG_ACTUAL_PRDCT_PLT 20 50
DRUG_ID_ONLY 20 50
10 rows selected.
SQL>
检查SOA_EX_DRUG表记录，编译失效对象，开启监听，正式恢复系统运行。
SQL> select count(1) from MDMEXTERNAL.SOA_EX_DRUG;
COUNT(1)
----------
2473533
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-06-30 21:45:51
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2025-06-30 21:45:57
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@md-db-out trace]$ lsrnctl start
-bash: lsrnctl: command not found
[oracle@md-db-out trace]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-JUN-2025 21:46:23
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/md-db-out/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=md-db-out)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=md-db-out)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 30-JUN-2025 21:46:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/md-db-out/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=md-db-out)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@md-db-out trace]$
至此我们已经在数据库层面对SOA_EX_DRUG表进行了处理
调整后第二天再观察一下系统使用情况
数据库告警日志中也没有ORA-00060报错
[root@md-db-out trace]# cd /u01/app/oracle/diag/rdbms/mddb/mddb/trace/
[root@md-db-out trace]# tail -60 alert_mddb.log
Thread 1 advanced to log sequence 1409133 (LGWR switch)
Current log# 3 seq# 1409133 mem# 0: /u01/app/oracle/oradata/mddb/redo03.log
Tue Jul 01 12:04:07 2025
Archived Log entry 1398382 added for thread 1 sequence 1409132 ID 0x2764065 dest 1:
Tue Jul 01 12:12:18 2025
Thread 1 advanced to log sequence 1409134 (LGWR switch)
Current log# 1 seq# 1409134 mem# 0: /u01/app/oracle/oradata/mddb/redo01.log
Tue Jul 01 12:12:18 2025
Archived Log entry 1398383 added for thread 1 sequence 1409133 ID 0x2764065 dest 1:
Tue Jul 01 12:22:21 2025
Thread 1 advanced to log sequence 1409135 (LGWR switch)
Current log# 2 seq# 1409135 mem# 0: /u01/app/oracle/oradata/mddb/redo02.log
Tue Jul 01 12:22:21 2025
Archived Log entry 1398384 added for thread 1 sequence 1409134 ID 0x2764065 dest 1:
Tue Jul 01 12:31:35 2025
Thread 1 advanced to log sequence 1409136 (LGWR switch)
Current log# 3 seq# 1409136 mem# 0: /u01/app/oracle/oradata/mddb/redo03.log
Tue Jul 01 12:31:35 2025
Archived Log entry 1398385 added for thread 1 sequence 1409135 ID 0x2764065 dest 1:
Thread 1 advanced to log sequence 1409137 (LGWR switch)
Current log# 1 seq# 1409137 mem# 0: /u01/app/oracle/oradata/mddb/redo01.log
Tue Jul 01 12:31:36 2025
Archived Log entry 1398386 added for thread 1 sequence 1409136 ID 0x2764065 dest 1:
Thread 1 cannot allocate new log, sequence 1409138
Checkpoint not complete
Current log# 1 seq# 1409137 mem# 0: /u01/app/oracle/oradata/mddb/redo01.log
Thread 1 advanced to log sequence 1409138 (LGWR switch)
Current log# 2 seq# 1409138 mem# 0: /u01/app/oracle/oradata/mddb/redo02.log
Tue Jul 01 12:31:37 2025
Archived Log entry 1398387 added for thread 1 sequence 1409137 ID 0x2764065 dest 1:
Tue Jul 01 12:36:03 2025
Thread 1 cannot allocate new log, sequence 1409139
Checkpoint not complete
Current log# 2 seq# 1409138 mem# 0: /u01/app/oracle/oradata/mddb/redo02.log
Thread 1 advanced to log sequence 1409139 (LGWR switch)
Current log# 3 seq# 1409139 mem# 0: /u01/app/oracle/oradata/mddb/redo03.log
Tue Jul 01 12:36:05 2025
Archived Log entry 1398388 added for thread 1 sequence 1409138 ID 0x2764065 dest 1:
Tue Jul 01 12:47:29 2025
Thread 1 advanced to log sequence 1409140 (LGWR switch)
Current log# 1 seq# 1409140 mem# 0: /u01/app/oracle/oradata/mddb/redo01.log
Tue Jul 01 12:47:29 2025
Archived Log entry 1398389 added for thread 1 sequence 1409139 ID 0x2764065 dest 1:
Tue Jul 01 12:56:58 2025
Thread 1 advanced to log sequence 1409141 (LGWR switch)
Current log# 2 seq# 1409141 mem# 0: /u01/app/oracle/oradata/mddb/redo02.log
Tue Jul 01 12:56:58 2025
Archived Log entry 1398390 added for thread 1 sequence 1409140 ID 0x2764065 dest 1:
Tue Jul 01 13:01:37 2025
Thread 1 advanced to log sequence 1409142 (LGWR switch)
Current log# 3 seq# 1409142 mem# 0: /u01/app/oracle/oradata/mddb/redo03.log
Tue Jul 01 13:01:37 2025
Archived Log entry 1398391 added for thread 1 sequence 1409141 ID 0x2764065 dest 1:
Thread 1 cannot allocate new log, sequence 1409143
Checkpoint not complete
Current log# 3 seq# 1409142 mem# 0: /u01/app/oracle/oradata/mddb/redo03.log
Thread 1 advanced to log sequence 1409143 (LGWR switch)
Current log# 1 seq# 1409143 mem# 0: /u01/app/oracle/oradata/mddb/redo01.log
Tue Jul 01 13:01:38 2025
Archived Log entry 1398392 added for thread 1 sequence 1409142 ID 0x2764065 dest 1:
[root@md-db-out trace]#
也没有看到enq: TX - allocate ITL entry等待事件了
[oracle@md-db-out ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 13:01:54 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200
col sid for 999999
col event for a40
col p1text for a15
col p1 for 99999999999
col p1raw for a20
select sid,event,p1text,p1,p1raw from v$session_wait
where event not in
('SQL*Net message from client','rdbms ipc message',
'SQL*Net message to client','pmon timer','smon timer',
'wakeup time manager','Streams AQ: qmn slave idle wait',
'jobq slave wait','Streams AQ: qmn coordinator idle wait'
,'gcs remote message','ges remote message','DIAG idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'SQL*Net more data from client',
'SQL*Net more data to client');SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11
SID EVENT P1TEXT P1 P1RAW
------- ---------------------------------------- --------------- ------------ --------------------
16 Space Manager: slave idle wait Slave ID 0 00
309 VKTM Logical Idle Wait 0 00
628 db file parallel read files 1 0000000000000001
713 Space Manager: slave idle wait Slave ID 1 0000000000000001
935 db file scattered read file# 5 0000000000000005
SQL>
再截取调整后的10点-11点的AWR报告，发现DB Time比原来提升了27倍。AWR报告里也没有看到enq: TX - allocate ITL entry等待事件了，Segments by ITL Waits里也没有SOA_EX_DRUG表了。
总结
当一个事务需要修改一个数据块时，需要在数据块头部获取一个可用的ITL槽，用于记录事务的ID、使用Undo数据块地址以及SCN等信息。如果事务申请不到新的可用ITL槽时，就会产生enq: TX - allocate ITL entry等待。我们可以通过调整表的INITRANS：增加初始 ITL 条目数和PCTFREE：增加保留空间，将行分散到更多的块中，来解决问题。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
