Resource Manager导致的定时JOB无法执行完成的排查

最新推荐文章于 2024-07-29 10:24:22 发布

Yushan Bai

最新推荐文章于 2024-07-29 10:24:22 发布

阅读量557

点赞数

分类专栏： ORACLE 故障排查

本文链接：https://blog.csdn.net/haibusuanyun/article/details/104087694

版权

ORACLE 故障排查专栏收录该内容

195 篇文章 15 订阅

订阅专栏

难得早睡一次，将近23点接到某用户电话，一个每半小时执行一次的定时JOB执行报错无法完成；用户DBA已经通过监控软件找到了JOB中的执行慢的一条DELETE的SQL，电话中告知我执行计划没有变化，系统CPU使用率不太高；

对于这一类SQL执行慢的问题，通常我们的思路是从几个大的方面入手判断一下系统整体资源情况，SQL是突然变慢还是一直比较慢；SQL的执行计划是怎么样的、对应的SQL执行时有哪些等待事件等，有了这些基础判断之后，再进行一下步的分析；在了解到目前JOB是每半小时启动一次，目前还有正在运行中的，立刻协调进行远程；使用了如下的处理流程：登陆系统查看这是一套hpux+11.2.0.3版本的两节点RAC。

1.命令行登陆后立刻开了一个窗口使用glance命令刷着系统资源情况，CPU使用率整体不到10%，有一个进程的CPU使用率100%，

2.通过ospid找到数据库中session信息，查看执行的SQL及event,确认就是JOB中的异常SQL。数据库整体的active session只有几个，看起来只有这个异常SQL的session等待事件是Resmgr:Cpu Quantum

3.Resource Manager相关的有几个问题会导致数据库会话hang等，由于此次是周日晚上出现的问题，oracle 11g自动收集统计信息策略在周末是从早上6点开始持续20小时的，此时还处于此窗口期。因此计划关闭Resource Manager，参考Resource Manager and SQL Tuning Advisory DEFAULT_MAINTENANCE_PLAN (文档 ID 786346.1)上的关闭步骤

4.关闭Resource Manager之后，session信息的event已经变为db file sequential read，不再出现Resmgr:Cpu Quantum等待事件；

5.此时SQL的执行计划效率较低，需要读取近500W数据块；此时通过JOB执行情况历史表，判断此SQL执行计划是近期变差(之前没有JOB超时报错当天突然出现大量)；通过AWR基表查看此SQL在之前的执行情况，可以发现异常当天17点前AWR基表中没有此SQL的信息；

6.对于突然变差的SQL，常见原因可能是绑定变量窥视、统计信息变化等；通过查看内存中此SQL的执行计划信息，没有人为进行SQL绑定；结合相关表的统计信息收集时间，可以发现绑定变量值是查询17点-17:30数据，统计信息收集完成时间为16:23; 初步判断统计信息收集后，SQL重新硬解析时，可能由于绑定变量窥视或者统计信息异常引起SQL使用了错误的执行计划；

7.接下来通过并行收集100%比例的统计信息，下一次JOB执行时此SQL已经使用了新的执行计划，读取数据块只需要几十个，ms级即可完成；接下来就使用了绑定SQL执行计划的方式，又观察了下一个半小时的JOB执行情况，一切正常。处理前后的SQL执行效率：

SQL> select sql_id,PLAN_HASH_VALUE,CHILD_number,EXECUTIONS,BUFFER_GETS/EXECUTIONS,ELAPSED_TIME/EXECUTIONS ,sql_profile from v$sql where sql_id='ahnbsn2fzh9jq';
SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS BUFFER_GETS/EXECUTIONS ELAPSED_TIME/EXECUTIONS
------------- --------------- ------------ ---------- ---------------------- -----------------------
ahnbsn2fzh9jq      3801442187            0         39             4862751.49               313439769

SQL>  select sql_id,CHILD_address,PLAN_HASH_VALUE,CHILD_number,EXECUTIONS,BUFFER_GETS/EXECUTIONS,ELAPSED_TIME/EXECUTIONS,sql_plan_baseline from v$sql where sql_id='ahnbsn2fzh9jq';

SQL_ID        CHILD_ADDRESS    PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS BUFFER_GETS/EXECUTIONS ELAPSED_TIME/EXECUTIONS SQL_PLAN_BASELINE
------------- ---------------- --------------- ------------ ---------- ---------------------- ----------------------- ------------------------------
ahnbsn2fzh9jq C00000156E8D0B60      1240941567            0          8                 29.625               10653.625 SQL_PLAN_3srj4nhp31ppf65417d0e