上线过程中由于应用的需要，将大量分区非常多的分区表分成单表，于是需要批量create table，起了100个进程同时并发create table，过程中出现了大量的

enq: TX - row lock contention

row cache lock

enq: TX - allocate ITL entry

等待事件，由于当时也没有做trace，后来只好做了个awr和v$active_session_history进行查看了，

发现出现了类似如下的语句：

insert into ccol$(con#, obj#, intcol#, pos#, col#, spare1) values(:1, :2, :3, decode(:4, 0, null, :4), :5, :6)

update tsq$ set blocks=:3, maxblocks=:4, grantor#=:5, priv1=:6, priv2=:7, priv3=:8 where ts#=:1 and user#=:2

insert into tab$(obj#, ts#, file#, block#, bobj#, tab#, intcols, kernelcols, clucols, audit$, flags, pctfree$, pctused$, initrans, maxtrans, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, analyzetime, samplesize, cols, property, degree, instances, dataobj#, avgspc_flb, flbcnt, trigflag, spare1, spare6)values(:1, :2, :3, :4, decode(:5, 0, null, :5), decode(:6, 0, null, :6), :7, :8, decode(:9, 0, null, :9), :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, decode(:26, 1, null, :26), decode(:27, 1, null, :27), :28, :29, :30, :31, :32, :33)

的语句，ccol$将obj$和con$进行关联，再询问了应用的脚本是否存在约束，一问，每个column上都有not null约束，看来等待事件的出现并非凭空，

大量并发的insert导致enq: TX - allocate ITL entry的出现，

大量update tsq$ 会导致enq: TX - row lock contention的出现

ITPub博客

批量create table出现的等待

最新文章