ITPub博客

首页 > 数据库 > Oracle > 探寻大表删除字段慢的原因

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

原创 Oracle 作者:bisal 时间:2019-01-25 00:19:37 0 删除 编辑

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


由于生产环境,用的是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,自然不能直接改。

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7192724/viewspace-2564711/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 小时制式问题
请登录后发表评论 登录
全部评论
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2623856