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 /*+ d