返回小栈
在线重定义造成某核心OLAP系统数据库无规律间歇性重启
orastar2020-03-03 11:49:19

惬意的周末

今天周六,睡到自然醒,西安难得的古都蓝,温暖的阳光,一个惬意的周末,star童鞋正在焦急的等待最喜欢的西安美食之一”肉丸胡辣汤”。。。。突然电话响了,某核心BOSS系统数据库异常,需要紧急支持,star童鞋火速赶往现场ing……….

诡异的回忆

与现场工程师沟通了解现场情况,


场景一:昨天周五15:50,业务监控系统报警,某核心BOSS系统短暂异常,现场工程师查看时系统各项指标均正常(包括主机CPU、内存、数据库集群情况、日志等)。


场景二:昨天周五22:45,业务监控系统报警,某核心BOSS系统短暂异常,敬业高尚的现场工程师于23:50分打车赶到现场,经确认一切,系统各项指标均正常(包括主机CPU、内存、数据库集群情况、日志等)。–泾渭湖边的水,平静的像一面镜子,没有一丝波澜。


场景三:今天周六 6:20,业务监控系统再次报警,某核心BOSS系统短暂异常


是监控系统调皮(误报),还是数据库任性(异常),这是个问题,大家都很迷茫?

清澈的分析

在这个万分紧急的关头,一道白光闪过,star童鞋登场了,大家都看到了希望,下来上干货。。。。


查看所有异常时间点


数据库历史会话


select * from dba_hist_active_sess_history y where y.sample_time >= trunc(sysdate)+13/24

and y.sample_time <= trunc(sysdate)+16/24;


发现15:50数据库存在大量活动重建索引进程



sql_1:

begin

  dbms_redefinition.start_redef_table('ht', 'my_home', 'family');

end;


注:该语句为在线重定义脚本。


sql_2:



通过查看历史会话表中,MACHINE、PROGRAM字段,锁定异常起因。


MACHINE        PROGRAM

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

B工程师的电脑   plsqldev.exe


经与BI工程师沟通其在昨天15点左右有做过在线重定义操作,昨天22点及今天早上6点没有任何操作。


继续查看SQL_3:



注:批量重建索引语句,由于top_level_sql_id相同,所以为sql_1产生的子进程。


WORD天这条重建索引的语句在干嘛!!!!


该语句有以下两个隐患:


  • 正常重建索引语句:alter index “ht”.”PK _ht _LOG”rebuild parallel N(N为需要并行执行的进程数),该语句未设置并行度N,此时的并行度是:服务器CPU数*每个CPU启用的线程数,该服务器210个CPU线程(2结点RAC,210*2个线程)因此以上条语句同时启动420个进程,造成服务器CPU使用率100%,数据库服务异常。

  • 该索引重建完成后,索引并行度为”default”



周五 15点的问题已经分析清楚了,但周五22点和周六6点又是什么原因呢?star童鞋继续分析ing…..

 

查看周五22点和周六6点数据库AWR报告



由上看出,引起并行查询等待的sql为


select /*+ parallel_index(t, "PK_ht_LOG", 999) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad no_expand index_ffs(t, "PK_ht_LOG") */ count(*) as nrw, count(distinct sys_op_lbid(1262391, 'L', t.rowid)) as nlb, null as ndk, sys_op_countchg(substrb(t.rowid, 1, 15), 1) as clf from "ht"." ht_LOG " sample block ( .6116619100, 1) t where "READ_ID" is not null。


该sql使用hint强制并行操作,WORD天999

 



由上可以看出DBMS_SCHECULER中的job(ORA$AT_OS_OPT_SY_2476)占据了97.25%的服务。而该scheculer调用的就是上面的并行查询语句。



原因已经很明显。数据库统计分析任务,周五晚上10点、周六早上6点,该调度程序调用的sql使用并行查询(索引默认并行度999),耗尽了cpu,导致数据库异常。


问题回溯:


场景1: 15:30分业务人员执行在线重定义,ORACLE在线重定义(业务表1亿数据量),重建索引alter index index_name rebuildparallel使用系统默认并行度,并行度较高,造 成数据库异常,此时该索引degree为default。


场景2:周五晚上10点数据库定时统计任务启动,使用索引degree(default)数据库服务异常。


场景3:周六早上6点数据库定时统计任务启动,使用索引degree(default)数据库服务异常。


场景4:star童鞋经过深入分析后,执行一条命令问题解决。

简洁的方案

 Alter index index_name noparallel;


–你要的干货。

忠诚的建议

  • 排查表和索引degree并行度,将并行度不为1的改为并行度为1(noparallel)。

  • 将度行度列入自动化监控列表,及时发现问题。

  • 修改参数parallel_max_servers在可控范围。


惬意的周末已过去大半,我最喜欢的古都美食之一”肉丸胡辣汤”还热气腾腾的等待star同学归来。。。。。。。

6
2