ORACLE11.2.0.3数据库alter table drop column慢原因分析

中午某生产库数据库发布补丁，开发人员反应有语句阻塞，无法执行下面语句

alter table pyramid.pd_loanProduct drop column customerType;

这是一个只有4行的小表

 

我建个测试表尝试了一下，果然很慢

create table pyramid.test_alter2(id number,name varchar2(10))  ;
alter table pyramid.test_alter2 drop column name;

 

我的跟踪步骤如下：

13:44:18 sys@DSEDI>select * from v$mystat where rownum<2 ;

       SID STATISTIC#      VALUE

---------- ---------- ----------

       166          0          0

 

13:44:31 sys@DSEDI>alter table pyramid.test_alter2 drop column name;

这个语句卡死

 

在新的会话查看非空闲等待事件：

select inst_id, sid,event from gv$session_wait where wait_class<>'Idle';

 

发现library cache lock

正常情况下，从v$access视图查询出访问改表的会话，kill掉即可，但是执行select * from v$access的时候同样卡死，也就是说这个方法行不通，那换个方法

 

再次查询，发现library cache lock没了，换成了

 

 在两个节点分别执行

select b.sid,a.user_name,a.kglnaobj

from x$kgllk a , v$session b

where a.kgllkhdl in

(select p1raw from v$session_wait

where wait_time=0 and event = 'library cache lock')

and a.kgllkmod <> 0

and b.saddr=a.kgllkuse

都没结果，确认已经没有library cache lock

 

 

通过两次查询，发现等待事件select * from v$event_name t where t.name='db file sequential read' 的P2是block# 在变化

select objd, file#,block#,class#,ts#,cachehint,status,dirty from v$bh where file#=2 and block#=824009 ;
477 2   824009 1   1   15  xcur   N

select objd, file#,block#,class#,ts#,cachehint,status,dirty from v$bh where file#=2 and block#=739302 ;
477 2   739302 1   1   15  xcur   N

 

核查发现477是一个索引

select * from dba_objects t where t.object_id=477 ;
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST

 

我检查了一下了awr的配置信息，发现那个索引有2.5GB

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE

--------- --------- --------------------------------------------------------------------- ---------------

NON_AWR     2,510.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                    INDEX

NON_AWR     1,860.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY                                      TABLE

 

至此，原因找到了

1、这张表还有其他会话在用，然后就等待library cache lock，无效化相关cursor比较慢。
接下去是db file sequential read，可以看到p1,p2,p3一直在变化，所以会话不是hang，而是在运行。
经过进一步研究发现数据块等待的对象是 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST，会话正在更新AWR报告相关视图的索引信息，所以比较慢。

2、Oracle bug，可能性很低
Bug 6781367 - ALTER TABLE ADD COLUMN or mass UPDATE can be slow in ASSM (文档 ID 6781367.8)

 

 

解决方案：

1. 清理那个表和索引，收集统计信息

analyze table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY compute statistics ;
alter index "sys"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST" rebuild ;

处理后，表和索引都很小了

 

     2.如果还有类似情况，那么重建表，这种情况如果很多，一般不会再重现了，否则要当做BUG处理，要考虑打补丁