一则job 执行变慢简单分析

一、问题描述

客户反馈job 之前执行都正常， 08月11日 3点开始，突然变慢，执行时间较长。
 

二、处理过程

1. 分析异常时间段AWR报告

分析：数据库整体负载正常，无异常等待事件，各项等待事件平均等待时间非常小，DB整体负载正常；根据客户反馈其他应用都正常，只有这一个job执行变慢，因此基本排除是因为整个DB负载较重导致的性能问题。

2. 查看SQL语句部分

分析：查看SQL语句，排名第一的确实是JOB，执行时间相比平时确实变长很多，整个JOB单次执行需60min，主要时间都耗在第二个SQL语句gz9y6v9r32df1上面，该SQL语句单次执行0.04s，看起来执执行速度还可以，但是执行次数较多，7万多次，所以整体时间消耗较大，初步判断为该SQL执行时间过长导致了JOB执行时间较长。

3. 查看SQL语句执行计划

分析：我们选取一个时间段较长的awrsqrpt报告，包含正常时间段和异常时间段，可以看到该SQL语句存在2个执行计划，且效率相差很多，单次执行时间差了200多倍，这么看来，0.04s 就显的非常慢了。

对比2个执行计划效率：

3858585690 执行计划，单次执行时间：0.043s

66230310 执行计划，单次执行时间：0.00016s

具体执行计划如下：3858585690 为全表扫描，66230310 为索引范围扫描。

4. 查看SQL语句异常时间段执行计划

分析：可以看到，异常时间段期间，该SQL语句确实走了不好的执行计划，导致了执行时间长，到这里，我们可以判断是该SQL在异常时间段执行计划发生了变化，走了不好的执行计划，执行时间变长，导致整个JOB 执行时间变长。

三、解决办法

针对执行计划改变的问题，我们可以采取绑定执行计划或者使用SPM 方式来解决。这里，我们选择使用绑定执行计划的方式。

1. 采用sql profile 方式，绑定执行计划

-- 我们使用coe_load_sql_profile.sql 脚本，绑定执行计划，不要使用sys用户

SQL> conn system/oracle
SQL> @coe_load_sql_profile.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: gz9y6v9r32df1

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: gz9y6v9r32df1

 PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
  3858585690                .043
  66230310                 .000

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 66230310
....


过程略。。
复制

2. 确认SQL语句已经被绑定

SQL> select * from table(dbms_xplan.display_cursor('gz9y6v9r32df1',null,'last typical'));

...
Plan hash value: 66230310
..
Note
-----
   - SQL profile "xxxx" used for this statement
复制

通过上面的SQL语句，查看新的执行计划，我们可以看到 - SQL profile “xxxx” used for this statement字样，可以确认该SQL语句已经绑定了SQL pofile。

通过绑定执行计划后，SQL语句执行效率恢复到正常水平，JOB 执行时间也恢复正常。