记一次Oracle大表添加带默认值列带来的灾难性性能故障

故障排查

---

业务人员反映数据库无法使用，故障现象及排查过程如下：

• 登陆进数据库服务器执行uptime发现负载高达400
• select * from v$session_longops where sofar<>totalwork;无记录，排除大表全表扫描或hash关联和RMAN备份等原因
• select * from v$lock where type like 'TX';发现数据库大量会话锁定时间长达4000s
• 分析AWR报告
  TOP等待事件
  顶级等待事件为cursor:pin S wait on X和library cache lock，library cache是共享池（shared pool）中主要负责缓存SQL语句及其被解析生成的执行计划，解析分硬解析和软解析，硬解析是新生成执行计划然后存入library cache,软解析是library cache中已缓存该SQL执行计划（SQL文本相同，已经解析过，缓存未被清空）。

实例效率百分比统计

SQL解析大多数在等待，实际解析时间仅5%，SQL解析出现严重等待的原因往往和DDL语句有关，DDL语句在更改某个对象（通常是表）的结构的过程中，这个对象是无法使用的，如果是表，则无法查询，实际上就是无法解析SQL。

TOP SQL 异常SQL定位
最终发现是一条来自于PLSQL的alter table MR_ORDERS add execute_count NUMBER default 0;添加表同时赋默认值。

检查表大小发现表大约14G

select bytes/1024/1024,segment_name from dba_segments 
where segment_name like 'MR_ORDERS' and owner like 'BV';
BYTES/1024/1024 SEGMENT_NAME                                                                     
--------------- ---------------------------------------------------------------------------------
          14372 MR_ORDERS                                                                        

稍微有经验的DBA都知道，加列的同时给表列赋默认值实际上就是一个全表的UPDATE，只不过会给表加DDL锁（SQL解析需等待锁释放，从而无法查询）。对于大表来说，加列的同时赋默认值，相当于给一个庞大的DML事务加了个DDL锁，对业务繁忙的生产系统来说，无疑是毁灭性的性能灾难。

解决方案：

---

先杀掉DDL语句
select 'alter system kill session '''||sid||','||serial#||'''; ' from v$session where sql_id like 'fqhxcjtkbhvrn';

再杀掉其他大量等待的SQL(这里方便起见，按照SQL数据量进行了排序，先批量删最多的)
select count(*),sql_id from v$session group by sql_id order by 1 desc;
select 'alter system kill session '''||sid||','||serial#||'''; ' from v$session where sql_id like 'XXX';

温馨提示：
删之前最好检查一下SQL，以免误删
select * from v$sql where sql_id like 'XXX';