oracle 大表新增列 慢_大表删除字段为何慢？

预计阅读时间：5分钟

某个系统上线，SQL脚本中有个变更，删除某张表的一个字段，线上执行，等了1个小时，还没有完成，担心是因为正常业务的影响，于是，尝试停了服务，可这条SQL，仍处于等待状态，此时，上游系统出现积Q，面临两个选择：

1. 继续等待SQL完成，但无法知晓，还要等待多久。

2. kill这条SQL，回滚DDL，同样需要些时间。

经过权衡，选择了方案2，大约2分钟左右，回滚完成，启动服务，恢复正常。虽然系统恢复，但是这个字段，没有被删除。

其实这张待删除字段的表，有5000万条记录，而且不是分区表，我们需要看下，是因为存在其他的事务，阻碍了删除操作，还是删除字段的操作中，有什么等待事件，导致如此之慢。

由于当时的环境，已经无法使用，所以只能用实验，模拟这个操作，首先，创建测试表，包含三个字段，插入测试数据，总计5000万，SQL>create table test(id number, a varchar2(5), b varchar2(5));

Table created.

SQL>select count(*) from test;

COUNT(*)

----------

50000000

第一次执行，删除的过程中，提示了ORA-30036错误，这说明了什么？说明了alter table ... drop column ...操作，会产生回滚数据，需要占据回滚表空间，SQL> alter table test drop column a;

alter table test drop column a

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

增加回滚空间，执行删除操作，此时这个会话hang，我们看下锁信息，发现当前drop column的会话，对TEST表，持有的锁模式，是6，即exclusive独占锁，SQL> SELECT l.session_id sid,

2         s.serial#,

3         l.locked_mode,

4         l.oracle_username,

5         l.os_user_name,

6         s.machine,

7         s.terminal,

8         o.object_name,

9         s.logon_time

10  FROM v$locked_object l, all_objects o, v$session s

11  WHERE l.object_id = o.object_id

12     AND l.session_id = s.sid

13  ORDER BY sid, s.serial#;

SID      SERIAL#   LOCKED_MODE ORACLE_USERNAME OS_USER_NAME    MACHINE     TERMINAL   OBJECT_NAME                    LOGON_TIM

------- ---------- ----------- --------------- --------------- ---------- ---------- ------------------------------  ---------

199      52583         3         BISAL           oracle         DBBAK1       pts/0    WRI$_OPTSTAT_HISTGRM_HISTORY   21-DEC-18

199      52583         6         BISAL           oracle         DBBAK1       pts/0    TEST                           21-DEC-18

199      52583         3         BISAL           oracle         DBBAK1       pts/0    WRI$_OPTSTAT_HISTHEAD_HISTORY  21-DEC-18

我们对这个操作，执行10046，得到的trace文件，有661K，打开trace，第一步操作，就是NOWAIT对表设置EXCLUSIVE锁，接下来，是一系列DML，LOCK TABLE 'TEST' IN EXCLUSIVE MODE NOWAIT

delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history

where :1 <= savtime and obj# = :2 and intcol# = nvl(:3, intcol#)

and rownum <= NVL(:4, rownum)

delete from sys.wri$_optstat_histhead_history h where h.obj#

in (select o2.obj# from sys.obj$ o1, sys.obj$ o2 where o1.obj# = :1

and o1.type# = 2  and o1.owner# = o2.owner#

delete from sys.wri$_optstat_histgrm_history h where h.obj#

in (select o2.obj# from sys.obj$ o1, sys.obj$ o2 where o1.obj# = :1

and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name)

and h.intcol# = :2

update sys.wri$_optstat_histhead_history h

set intcol# = decode(intcol#, :2, 0, intcol# -1)

where h.intcol# >= :2 and h.obj#

in (select o2.obj# from sys.obj$ o1, sys.obj$ o2 where o1.obj# = :1

and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name)

update sys.wri$_optstat_histgrm_history h

set intcol# = decode(intcol#, :2, 0, intcol# -1)

where h.intcol# >= :2 and h.obj#

in (select o2.obj# from sys.obj$ o1, sys.obj$ o2

where o1.obj# = :1 and o1.type# = 2 and o1.owner# = o2.owner#

and o2.name = o1.name)

此时出现了很多等待事件，从记录的时间看，占据了drop column这个操作大部分的用时，db file scattered read

db file sequential read

log buffer space

log file switch completion

然后，就是对很多数据字典表，进行各种DML操作，此处忽略了select操作，delete from sys.col_usage$ where obj#= :1 and intcol#= :2

delete from objauth$ where obj#=:1 and col#=:2

delete from col$ where obj#=:1 and intcol#=:2

update triggercol$ set col#=col#-:1

where (obj#=:2 or obj#=:3) and col#>:4

update col$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update icol$ set col#=col#-:1 where (bo#=:2 or bo#=:3) and col#>:4

update ccol$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update partcol$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update partcol$ set col#=col#-:1 where obj#

in (select obj# from ind$ where (bo#=:2 or bo#=:3)) and col#>:4

update histgrm$ set col#=col#-:1 where obj#

in (select o2.obj# from obj$ o1, obj$ o2

where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2

and o1.owner# = o2.owner# and o2.name = o1.name) and col#>:4

update hist_head$ set col#=col#-:1 where obj#

in (select o2.obj# from obj$ o1, obj$ o2

where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2

and o1.owner# = o2.owner# and o2.name = o1.name) and col#>:4

update coltype$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update lob$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update ntab$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update refcon$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update objauth$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update subpartcol$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update ind$ set trunccnt=trunccnt-:1 where (bo#=:2 or bo#=:3)

and trunccnt>:4

update partobj$ set definclcol=definclcol-:1

where (obj#=:2 or obj#=:3) and definclcol>:4

update indpart$ set inclcol=inclcol-:1 where (bo#=:2 or bo#=:3)

and inclcol>:4

update col$ set segcol#=segcol#-:1 where (obj#=:2 or obj#=:3)

and segcol#>:4

update icol$ set segcol#=segcol#-:1

where (bo#=:2 or bo#=:3) and segcol#>:4

update partcol$ set segcol#=segcol#-:1

where (obj#=:2 or obj#=:3) and segcol#>:4

update partcol$ set segcol#=segcol#-:1 where obj#

in (select obj# from ind$ where (bo#=:2 or bo#=:3)) and segcol#>:4

update subpartcol$ set segcol#=segcol#-:1

where (obj#=:2 or obj#=:3) and segcol#>:4

update triggercol$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update col$ set intcol#=intcol#-:1 where (obj#=:2 or obj#=:3) and intcol#>:4

update icol$ set spare2=spare2-:1 where (bo#=:2 or bo#=:3) and spare2>:4

update (select intcol#, ind$.bo# bo# from ind$,                                       icol$ where ind$.obj# = icol$.obj#) set intcol#=intcol#-:1 where (bo#=:2 or bo#=:3) and intcol#>:4

update ccol$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update partcol$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update partcol$ set intcol#=intcol#-:1 where obj#

in (select obj# from ind$ where (bo#=:2 or bo#=:3)) and intcol#>:4

update histgrm$ set intcol#=intcol#-:1 where obj#

in (select o2.obj# from obj$ o1, obj$ o2

where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2

and o1.owner# = o2.owner# and o2.name = o1.name) and intcol#>:4

update hist_head$ set intcol#=intcol#-:1 where obj#

in (select o2.obj# from obj$ o1, obj$ o2

where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2

and o1.owner# = o2.owner# and o2.name = o1.name) and intcol#>:4

update coltype$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update lob$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update ntab$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update refcon$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update rls_sc$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update fgacol$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update attrcol$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update com$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4

update subpartcol$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update icoldep$ set intcol#=intcol#-:1

where obj# in (select obj# from ind$

where (bo#=:2 or bo#=:3)) and intcol#>:4

update association$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update ustats$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update partlob$ set intcol#=intcol#-:1

where (tabobj#=:2 or tabobj#=:3) and intcol#>:4

update trigger$ set nttrigcol=nttrigcol-:1

where (baseobject=:2 or baseobject=:3) and nttrigcol>:4

update viewtrcol$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update jijoin$ set tab1col#=tab1col#-:1

where (tab1obj#=:2 or tab1obj#=:3) and tab1col#>:4

update subcoltype$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update opqtype$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update opqtype$ set lobcol=lobcol-:1

where (obj#=:2 or obj#=:3) and lobcol>:4

update col_usage$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

update sumpred$ set lcolid=lcolid-:1

where (ldobj#=:2 or ldobj#=:3) and lcolid>:4

update coltype$ set typidcol#=typidcol#-:1

where (obj#=:2 or obj#=:3) and typidcol#>:4

update defsubpartlob$ set intcol#=intcol#-:1where (bo#=:2 or bo#=:3) and intcol#>:4

update ecol$ set colnum=colnum-:1

where (tabobj#=:2 or tabobj#=:3) and colnum>:4

update radm_mc$ set intcol#=intcol#-:1

where (obj#=:2 or obj#=:3) and intcol#>:4

delete from compression$ where obj#=:1

delete from idl_ub1$ where obj#=:1

delete from idl_char$ where obj#=:1

delete from idl_ub2$ where obj#=:1

delete from idl_sb4$ where obj#=:1

delete from error$ where obj#=:1

update dependency$ set d_attrs = :1 where d_obj# = :2 and p_obj# = :3

delete from superobj$ where subobj# = :1

delete from tab_stats$ where obj#=:1

update tab$

set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),

tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),

audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,

rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,

samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),

dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,

spare6=:35 where obj#=:1

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,

dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1

and name=:2 and namespace=:3 and remoteowner is null

and linkname is null and subname is null

insert into sys.aud$( sessionid,entryid,statement,ntimestamp#,

userid,userhost,terminal,action#,returncode, logoff$lread,logoff$pread,

logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,

sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)

values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),:4,:5,:6,:7,:8,:9,:10,

:11,:12,cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,

:16,:17,:18,:19,:20,:21,:22)

可以看出，一个drop column，在Oracle后台，就要做如此多的工作。尽管表结构、累积的统计信息、测试和生产环境，确实有些不同，但是实验中drop column操作，用时大约7分钟，和实际上线中，1小时未执行完成，有些不相符，从感性上看，删除一张表字段，不仅仅要删除数据字典，而且要删除实际表上的数据，因此这和表的数据量，很是有关。

如果有机会，在线上再做一次，或许能得到更多的信息。针对这个问题，朋友们要是有什么猜测，欢迎提出来。

其实针对大表的结构变更，还是要非常小心，一方面测试环境很重要，但往往可能不具备这种环境，或是因为数据量，或是因为并发量，但另一方面，要是了解原理，可能不用测试，就知道这个操作的影响，例如新增字段，可以参考