oracle rac 表 删 列 慢_探寻大表删除字段慢的原因

这是杂货铺的第403篇文章

《大表删除字段为何慢？》的案例中，提到删除一张大表的字段，产生了很多等待，但是测试环境模拟的现象，看起来和生产，略有区别。

由于生产环境，用的是DG架构，因此在数据库同事的协助下，利用DG备库snapshot的功能，在生产环境，真实操作了一次，虽然和主库操作，唯一不同的是，没有请求的压力，相当于停了应用，但还是能说明些问题。

整个删除操作，执行时间，大约是30分钟，通过10046的trace，看见等待事件，主要是下面这种，

从这段内容中，有几点值得说明，

1. db file scattered read

Oracle在执行全表扫描(Full Table Scan，FTS)或全索引扫描(Index Full San)时，为保障性能，尽量一次读取多个块，这称为Multi Block I/O，即多块读。每次执行Multi Block I/O，都会等待物理I/O结束，此时等待的，就是db file scattered read事件。这个等待事件包含三个参数：
file#：要读取的数据块所在数据文件的文件号。
block#：读取的起始数据块号。
blocks：需要读取的数据块数目。

2. obj#=11111

obj#对应的是dba_objects视图中的字段object_id，所以，根据object_id，可以检索出object_name，就知道正是删除字段的表名，说明这些等待，产生在删除字段的表上。

3. file#=10

file#对应的是dba_data_files视图中的字段file_id，根据10046这个trace文件中，“db file scattered read”出现的上下文，可以看出不同的file_id，通过检索dba_data_files，可以得出结论，会遍历所有数据表空间对应的数据文件，

众所周知，alter table语句是条DDL，在Oracle中其实会被转成DML语句来执行，但DDL和DML不同之处，就在于这种DDL会有隐式提交，因此执行完成，会自动commit，无法回滚，既然是条DML，就会产生redo，就会占用undo空间，这就能解释，为何执行过程中，出现大量“db file scattered read”，这种等待事件，原因就是要将表中所有数据，注意是所有数据，从本地磁盘文件，读到buffer cache，需要真实删除每行的这列值。还有个知识点，就是在删除的过程中，是可以select每条记录，原因就是undo提供的一致性读，Oracle体系结构的基础理论，在此刻，发挥作用，这个问题，要是开始没明白，说明还是对体系结构，理解不深入，没有条件反射。这些问题，还要感谢dbsnake的指教，他还指出，像PG，和Oracle不同的是，他的DDL，是需要显示commit，因此就能回滚DDL。

关于大表删字段，有些老师朋友，提供了他们碰见的问题，以及建议，

1. kill删除字段的会话，再次查询表会报ORA-12986，需要truncate表才能继续，此时要是没备份，就凉凉了。

这篇文章提到了这个错误，

https://blog.csdn.net/u011116642/article/details/51540314

有人还做了测试，

1. 将列置为unused

ALTER TABLE test1 SET UNUSED COLUMN Tablespace_name;

2. 删除unused列

ALTER TABLE test1 DROP UNUSED COLUMN CHECKPOINT 5;

在未执行完毕前，用shutdown abort强制关闭数据库(如果用shutdown immediate就看不到效果，它会等到执行完毕才会关闭数据库)

3. 重新启动数据库，查看test1表，报错，

4. 继续删除未删完的列

ALTER TABLE test1 DROP COLUMNS continue

5. 执行完毕后再次查询test1表，就OK了

2.可以尝试逻辑删除，然后再物理删除，即线上置为unused，等维护窗口，再删除这个字段，如下面这篇文章，

https://blog.csdn.net/caimaohua/article/details/4264040

3. 使用在线重定义，删除字段，如下文章所介绍，

http://m.blog.itpub.net/17203031/viewspace-772500/

https://blog.csdn.net/qq_33879355/article/details/78578175

4. 如果有停机时间，可以采用CTAS重建表，间接删除字段。

针对这个问题，我们采用的，算是第五种方法，即不动这字段，作为备份字段，未来新需求要增加字段，就直接改这字段，当然这是有些前提的，

1. 应用中对该字段的引用，需要删除，例如insert操作，需要删除这个字段名称，否则就会出问题。

2. 新增字段的类型，要和这个字段类型兼容，比如这字段是VARCHAR2，新增字段需要DATE，自然不能直接改。

对这个问题，要是有什么新的见解、看法，欢迎提出来，一起讨论。