在线重定义造成某核心OLAP系统数据库无规律间歇性重启

惬意的周末

今天周六，睡到自然醒，西安难得的古都蓝，温暖的阳光，一个惬意的周末，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同学归来。。。。。。。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31442014/viewspace-2137456/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31442014/viewspace-2137456/