探寻大表删除字段慢的原因

古城微笑少年丶 2022-04-23 07:26 281阅读 0赞

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

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

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

![20190125001722148.png][]

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

**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，可以得出结论，会遍历所有数据表空间对应的数据文件，

![20190125001741471.png][]

众所周知，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表才能继续，此时要是没备份，就凉凉了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jpc2Fs_size_16_color_FFFFFF_t_70][]

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

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表，报错，
> 
> ![20190125001823412.png][]
> 
> 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，自然不能直接改。

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

我的博客即将同步至腾讯云+社区，邀请大家一同入驻：https://cloud.tencent.com/developer/support-plan?invite\_code=3rikbhkqktmok

[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247487758&idx=1&sn=4e6696b62b6d19c4faf36a04266521e9&chksm=ea8154fdddf6ddeb66c11764b10710c6f86434f96219f82d6637eb88597dd3f0b89333db8eda&scene=21#wechat_redirect
[20190125001722148.png]: /images/20220323/e9ba8e3e93d44e1fbefffeba9114d4a9.png
[20190125001741471.png]: /images/20220323/8c811b5beb0f4b8fb4a28a07605368e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jpc2Fs_size_16_color_FFFFFF_t_70]: /images/20220323/1c83e81fd612450690cf7ed6a4ebf4cf.png
[20190125001823412.png]: /images/20220323/bc0eac4e36a44299bfdba3532a46429d.png