记录一起索引rebuild与收集统计信息的事故

一、背景：

业务核心数据库每月定期做数据裁剪（保留一年数据），表中的索引存在大量的碎片。最近业务低迷，因此想为索引rebuild整理碎片。
  因涉及的表都是业务订单大表（分区表），涉及分区索引，每个分区索引操作均为20-30分钟不等。为了不影响生产DML操作，采用rebuild online 方案操作。
  自己写了一个脚本，将拼接好的rebuild online SQL文本放在sh 脚本执行，使其24小时执行。当时考虑业务本身是平时的1/10，又加了online 操作，因此不会影响生产。
  没想到在脚本在正常执行一周后，在周六上午8：50左右系统组收到应用连接异常增长的报警，在9：20时应用日志及我的rebuild SQL脚本同时报错：

ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

二、数据库现象

  收集8:50至9：20的ash报告，显示存在大量的由业务SQL引起的cursor: pin S wait on X 及 library cache lock等待事件

   看到这个ash报告后当时想的应该是应用之间产生的锁之间的挣用导致了应用和我执行rebuild脚本同时出现了ORA-04021报错。
  后从来dba_hist_active_sess_history视图里查询8：00至9：20的等待事件中发现571与643两个会话有相关阻塞的现象，并且643第一次产生library cache lock等待事件

– 查询视图SQL

select instance_num,sample_time,session_id,h.sql_id,h.sql_exec_start,event,a.SQL_TEXT,h.blocking_session,h.blocking_session_status
  from dba_hist_active_sess_history h
  left join v$sqlarea a on a.sql_id=h.sql_id
 where
  sample_time > to_date('****-**-** 08:00', 'yyyy-mm-dd hh24:mi')
   and sample_time < to_date('****-**-** 09:20', 'yyyy-mm-dd hh24:mi')
   and h.sql_id is not null
  order by 2;

下图为后期测试截图：当时571执行的SQL_TEXT为：dbms_stats.gather_database_stats_job_proc()

 

三、分析结论

  回想当天数据库操作：
  1、数据库每月的数据裁剪刚好是今天凌晨执行05点左右执行完成。
  2、数据库的自动维护任务为周六日从晚上10点开始执行20小时。
  3、索引rebuild online 脚本24小时执行。
  因此每月的数据裁剪操作为truncate 分区，触发了数据库的自动维护任务的统计收集该表的信息，然后表的统计收集信息可能导致原统计信息失效 （此为猜测，欢迎大神指正），使用rebuild 语句产生library cache lock事件与收集信息的会话产生相互阻塞的锁等待。同时使用业务应用设计该表的查询也产生了大量的library cache lock 事件。

  最终结论：rebuild online 索引需要避开数据库的自动维护任务或收集统计信息操作。

Oracle 自动收集统计信息机制：https://www.modb.pro/db/403670

Oracle_索引重建—优化索引碎片：https://www.modb.pro/db/399543

              欢迎点赞支持&或留言指正错误