记一次oracle数据库瞬时卡顿排查过程

---

欢迎关注作者 分享更多数据库安装配置，troubleshooting，调优，备份恢复知识和案例
CSDN：潇湘秦-CSDN博客
公众号：潇湘秦

symptoms
昨天晚上10点钟收到邮件报警，提示有超量的异常等待事件

异常的等待事件为 library cache lock
我这里监控的异常等待事件标准为，只要出现超过阈值的等待一定会影响数据库的性能，避免频繁的报警
##监控异常等待的脚本放在本文的最后

不久用户端开始在微信群中反馈，系统出现卡顿和部分超时问题

过了十分钟后用户端反应已经恢复，报警也没有继续出现，则可以判定已经恢复，继续睡觉第二天再继续追查

问题排查过程

追查过往的数据库异常状态常见的办法有OEM或者AWR报表
在OEM选择 问题数据库-性能主页-性能概览-历史，可以看到相关异常波峰

OEM的详细搭建可以参考我的这篇博文

https://www.modb.pro/db/647677

拉取问题时段的AWR 也可以看到top1 foreground wait event 为library cache lock

熟悉的library cache lock的DBA应该可以 很容易判断出library cache相关的等待是和share pool的数据结构相关，

可能发生library cache pin和library cache lock的情况：

1、在存储过程或者函数正在运行时被编译。
2、在存储过程或者函数正在运行时被对它们进行授权、或者移除权限等操作。
3、对某个表执行DDL期间，有另外的会话对该表执行DML或者DDL。
4、PL/SQL对象之间存在复杂的依赖性

Library cache lock/pin详解 参看我CSDN文章
https://blog.csdn.net/xiaofan23z/article/details/7699778

首先想到的应该就是 是否有DDL相关的操作

造成了大量的library cache的等待，这时就需要查一下问题时段的DDL记录 ；oracle本身是不会记录DDL的log的，这需要DBA提前对数据库做好DDL的log，

这样遇到问题时才方便排查，具体的DDL log记录脚本请参考如下博文

https://www.modb.pro/db/1746798614500429824

查询最近一天的DDL记录可以看到出问题时段有一些CMP开头的表被创建和删除， 还有部分temp表被创建和删除

module都是DBMS_SCHEDULER

再查询一下schedule job

select * from DBA_SCHEDULER_JOB_RUN_DETAILS；
找到执行时间符合的job

从JOB name和执行时间 就可以判定为autotask相关的任务，alert log中也有记录

11g的自动维护任务

oracle 11g中默认的自动维护任务分三类:
Automatic Optimizer Statistics Collection (自动优化器统计信息收集)**收集数库中所有无统计信息或仅有过时统计信息的 schema 对象的 Optmzer(优化)统计信息，SQL query optimizer(SQL 查询优化器)使用此任务收集的统言息提高 SQL 执行的性能。
Automatic Segment Advisor (自动段指导)*识别有可用回收空间的段，并提出如何消除这当段中的碎片的建议，也可以手动运行 Segment Advisor 获取更多最新建议，或获取 Automatic segment Advisor 没有检查到的那些有可能做空间回收的段的建议。
Automatic SOL Tuning Advisor (自动 SOL优化指导)
检查高负载 SQL语句的性能，并提出如何优化这些语句的建议。您可以配置此指导，自动应用建议的SQL profile.

Solution

这里造成影响的主要是 ‘auto space advisor’ 和’sql tuning advisor’ 处理办法比较简单就是把这两个定时任务关闭

这不是第一次遇到autotask对生产造成影响，生产环境建议可以关闭。

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL, window_name => NULL);
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL, window_name => NULL);

确认修改结果

SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME in ( 'auto space advisor','sql tuning advisor');

后记：

想迅速的定位问题依赖于数据支撑，数据依赖于监控或者统计信息，有些是系统自带的，有些需要运维自行添加

1.OEM监控平台对于监控oracle 还是非常好用的

2.记录DDL log一是可以方便审计追溯，二是方便调优排障

3.wait event 监控可以及时发现问题，及时处理

CMP$表和压缩相关属于auto space advisor范畴 参考如下官方文档Doc ID 1606356.1

Is Table SCHEMA.CMP4$xxxxxx Or Similar Related To Compression Advisor? (Doc ID 1606356.1)

附异常等待事件监控脚本

1.shell脚本 check_wait_event.sh
#!/bin/bash
#
#name: check_wait_event.sh
#purpose: check wait event
#2022-08-10 add by norton

  rm -f /tmp/check_wait_event.html

  ORACLE_SID=orcl  #修改sid
  export ORACLE_SID
  ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1  ##修改oracle home
  export ORACLE_HOME
  HOST_NAME=`uname -n`
  export HOST_NAME
  JOB_PWD=$HOME/jobs
  export JOB_PWD

  $ORACLE_HOME/bin/sqlplus -s "/ as sysdba"<<!
    @$JOB_PWD/check_wait_event 
    exit
!

  var=`cat /tmp/check_wait_event.html|wc -l`

  #echo $var
  if [[ $var -gt 11 ]];
  then
    # echo "**********************************************************************"
    # echo "There are alerts"
    echo "please contact DBA  xxxx"|mailx -s "  Check Wait Event Alert"  -a  /tmp/check_wait_event.html `cat $JOB_PWD/base_mail.addr|grep -v \#`   ###base_mail.addr 存放收件人邮箱
    # echo "**********************************************************************"
    # exit
  fi
  shift
done

-----------------------------------------------------------
2.sql脚本
/*
    Program:  check_wait_event.sql
    Purpose:  notice no idle wait event
    History:
        Rev     Date        Author           Description
        -----   ----------  -------------    -----------------  
        1.0     10-aug-2022   norton       Create the script 
*/

SET markup html ON spool ON entmap OFF


SET echo OFF
SET verify OFF
SET feedback OFF
SET termout OFF
SET pagesize 25
SET appinfo 'check_wait_event.sql' 


COLUMN event  format a35   heading "EVENT"
COLUMN wait_num        format 99,999 heading  "WAIT_NUM"

spool /tmp/check_wait_event.html


ttitle center "List NO IDLE wait time"  skip 2
 

select event,count(1) as wait_num  from gv$session_wait
where event in ('enq: TX - row lock contention',
'library cache lock','library cache pin','cursor: pin S wait on X')   /*可以根据需要添加需要监控的wait event*/
group by event
having count(1)>5;


SET markup html OFF
spool OFF
SET pagesize 14
ttitle OFF
SET feedback ON
SET termout ON
SET appinfo OFF
SET echo ON

EXIT
复制

---

欢迎关注作者
公众号：潇湘秦的DBA之路
墨天伦：潇湘秦的个人主页 - 墨天轮