中午某生产数据库发布补丁,开发人员反应有语句阻塞,无法执行下面语句
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’; alter_table_drop_column_1
发现library cache lock

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

再次查询,发现library cache lock没了,换成了
alter_table_drop_column_2
在两个节点分别执行
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
alter_table_drop_column_3
alter_table_drop_column_4
通过两次查询,发现等待事件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处理,要考虑打补丁