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);