预计阅读时间: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#-:1
where (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小时未执行完成,有些不相符,从感性上看,删除一张表字段,不仅仅要删除数据字典,而且要删除实际表上的数据,因此这和表的数据量,很是有关。
如果有机会,在线上再做一次,或许能得到更多的信息。针对这个问题,朋友们要是有什么猜测,欢迎提出来。
其实针对大表的结构变更,还是要非常小心,一方面测试环境很重要,但往往可能不具备这种环境,或是因为数据量,或是因为并发量,但另一方面,要是了解原理,可能不用测试,就知道这个操作的影响,例如新增字段,可以参考 《alter table新增字段操作究竟有何影响?(上篇)》 《alter table新增字段操作究竟有何影响?(下篇)》
|