【案例分享】 – 延迟段引起的性能问题

[TOC]

前言

本文主要分享一个由 deferred_segment_creation 段延迟特性引起的性能问题。该问题持续了几个月，一个非常头疼的问题。

段延迟介绍：当创建一个新表或索引时，Oracle不会立即为其分配空间（即创建段）。这一特性默认开启。适用于空表或者未插入数据的索引。

一、环境描述

客户存在2套MES 环境。一套是11.2.0.4 RAC ，另外一套是 19.21.0.0 RAC 。

二、问题描述

根据客户反馈，2套MES每个月1号 0点，产线机台都会抛出应用超时错误，持续1分钟后恢复正常。

三、问题分析

1） 查看数据库监控信息：可以看到，数据库在23:59 - 00:01 分钟之间，确实存在大量的并发类等待。

2）查看ASH 报告：可以看到，异常时间段主要都是library cache 相关等待。并且从v$active_sess_history 视图中，也找不到blocking_session 较多的session，看起来并不像某个session导致的。

因为持续时间非常短，能看到的信息比较有限，且每次都每个月 1号 0点，跨月时发生，平时没发生过，较为规律。所以，怀疑是跨月时候，分区表导致的。

怀疑的方向：

1. 每个月1号是否存在什么特殊操作，比如job 之类的定义 --> 和客户沟通，数据库、应用层面都没有部署这类job。
2. 跨月时，收集新的分区统计信息期间，导致SQL执行异常 --> 关闭自动收集统计信息任务，问题依旧发生。
3. 是否存在按月自动分区表，跨月时候自动分区导致 --> 数据库中不存在自动分区表。
4. 是否存在shared pool 动态调整导致 --> 经查询，数据库中不存在内存动态调整。
5. 是否存在人为 DDL 操作 --> 经查询，0点期间不存在的DDL操作，而且故障规律，不像是人为操作导致。

以上怀疑的方向，都不是问题导致的原因，我们提交support case ，请Oracle协助分析。

四、support 分析

1. 开case 后，根据support 要求，通过oradebug 收集更多的信息。

-- 问题规律，我们选择用crontab 自动收集问题发生的时候的hanganalyze 信息
oracle@mes-xxxx-db01:/home/oracle>crontab -l
0 0 1 * * /home/oracle/ocs/oracle_debug_script.sh

oracle@mes-xxxx-db01:/home/oracle>cat /home/oracle/ocs/oracle_debug_script.sh
#!/bin/bash
if [ -f ~/.bash_profile ];
then
  . ~/.bash_profile
fi
sqlplus -S "/ as sysdba" <<EOF
spool /tmp/oradebug.log
oradebug setmypid
oradebug unlimit

oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258

host sleep 5
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258

host sleep 5
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258

oradebug tracefile_name

EOF
复制

Support 第一次 回复

原因分析：

解决办法：

结论：根据support 提供的方法，修改隐含参数，并重启数据库后，问题依旧发生，未能解决。 

Support 第二次 回复

问题分析：

解决办法：

结论：在跨月之前，提前对未来分区预分配extent后，问题未发生，终于得到解决。 

通过数据库监控，可以看到，0点左右数据库运行正常，不存在并发类等待了。