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
 |
附异常等待事件监控脚本
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
