Process XXX appears to be hung in Auto SQL Tuning task

1 问题描述

1.1 邮件收到告警：

主机： xxx

目标名：xxx －xx，目标类型：Database Instance

事件信息：在以下时间/行号处的 /u01/app/oracle/diag/rdbms/xxx/xxxx/alert/log.xml 中检测到操作错误 (Process 0x0x18a...): Wed Jun 21 23:10:09 2017/150484。 ，联系人：

时间：2017-6-21 23:11:25 CST 通知发出：规则名 DEFAULT_RULESET_FOR_ALL_TARGETS,METRIC_ALERT_INCIDENT_CREATION，规则所有者 。

1.2 EM监控：

1.3 alert日志：

Wed Jun 21 23:10:09 2017
Process 0x0x18a1124b40 appears to be hung in Auto SQL Tuning task
Current time = 1498057808, process death time = 1498057801
Attempting to kill process 0x0x18a1124b40 with OS pid = 89919
OSD kill succeeded for process 0x18a1124b40

1.4 log.xml日志

<msg time='2017-06-21T23:10:09.303+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2489:3170456095' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='erp-sxjzt'
 host_addr='10.106.26.70' module='' pid='107802'>
 <txt>Process 0x0x18a1124b40 appears to be hung in Auto SQL Tuning task
 </txt>
</msg>
<msg time='2017-06-21T23:10:09.303+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2493:1670650406' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='erp-sxjzt'
 host_addr='10.106.26.70' module='' pid='107802'>
 <txt>Current time = 1498057808, process death time = 1498057801
 </txt>
</msg>
<msg time='2017-06-21T23:10:09.303+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2504:2480300341' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='erp-sxjzt'
 host_addr='10.106.26.70' module='' pid='107802'>
 <txt>Attempting to kill process 0x0x18a1124b40 with OS pid = 89919
 </txt>
</msg>
<msg time='2017-06-21T23:10:09.303+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2518:234340873' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='erp-sxjzt'
 host_addr='10.106.26.70' module='' pid='107802'>
 <txt>OSD kill succeeded for process 0x18a1124b40
 </txt>
</msg>

2 问题分析

不难看出该报错是因为Auto SQL Tuning task任务运行时间超长，超过时间限制而被中断。

2.1 查看是哪个job造成的：

SELECT client_name,
       task_name,
       task_target_type,
       last_try_result,
       operation_name,
       status,
       current_job_name,
       job_scheduler_status,
       retry_count,
       last_good_date,
       last_good_duration,
       last_try_date,
       last_try_duration
  FROM dba_autotask_task;

 

2.2 查看时间限制

SELECT task_name, parameter_name, parameter_value, description
  FROM dba_advisor_parameters
 WHERE parameter_name LIKE '%TIME_LIMIT'
   AND task_name = 'SYS_AUTO_SQL_TUNING_TASK';

3 解决方法：

自动sql优化，根据AWR的统计信息，针对产生较大性能影响的SQl运行SQl优化顾问（SQL Tuning Advisor）。AWR的统计信息用来生成一个SQL语句列表并根据它们过去一周对系统性能的影响进行排序，这个SQL列表会自动排除所有被断定为不太容易优化的SQL语句，比如并行查询、DML、DDL和任何由并发问题引起的性能问题。SQL优化顾问生成优化SQL的建议，建议中可能生成SQL配置文件（profile）。当建议使用SQL配置文件的时候，这些配置文件会进行性能测试，如果测试结果显示至少有三倍的改进，并且SQL优化任务参数ACCEPT_SQL_PROFILES设置为true，这个建议就会被接受；如果ACCEPT_SQL_PROFILES被设置为false，这个建议会被报告。

默认的ACCEPT_SQL_PROFILES设置为false，而且可以根据任何查询，单独执行SQl优化顾问。建议关闭这个性能。

关闭自动sql调优：

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
END;
/----------------------------以下为附加信息，建议关闭自动sql调优，而不是修改时间----------------
开启如下：
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
END;
/
调整时间语句：
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','TIME_LIMIT',7200);