ORA-16957: SQL Analyze time limit interrupt
ri Dec 21 22:00:00 2018
Setting Resource Manager plan SCHEDULER[0x32DD]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Fri Dec 21 22:00:00 2018
Starting background process VKRM
Fri Dec 21 22:00:00 2018
VKRM started with pid=52, OS id=163996
Fri Dec 21 22:00:02 2018
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Fri Dec 21 22:01:12 2018
Thread 1 advanced to log sequence 3392 (LGWR switch)
Current log# 3 seq# 3392 mem# 0: /u01/app/oracle/oradata/jrdw/redo03.log
Fri Dec 21 22:01:12 2018
LNS: Standby redo logfile selected for thread 1 sequence 3392 for destination LOG_ARCHIVE_DEST_2
Fri Dec 21 22:01:14 2018
Archived Log entry 6736 added for thread 1 sequence 3391 ID 0x55c5148c dest 1:
Fri Dec 21 22:02:05 2018
Errors in file /u01/app/oracle/diag/rdbms/jrdw/jrdw/trace/jrdw_j003_164087.trc:
ORA-16957: SQL Analyze time limit interrupt
Fri Dec 21 22:02:21 2018
Errors in file /u01/app/oracle/diag/rdbms/jrdw/jrdw/trace/jrdw_j003_164087.trc:
ORA-16957: SQL Analyze time limit interrupt
Fri Dec 21 22:06:47 2018
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
---文档 ID 1275248.1
SOLUTION
1. Check the current values for the time limit related parameters:
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('TIME_LIMIT',
'DEFAULT_EXECUTION_TYPE',
'LOCAL_TIME_LIMIT');
Then, increase the time:
Using:
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'TIME_LIMIT', value => 7200);
END;
/
Or
BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(parameter => 'TIME_LIMIT', value => 7200);
END;
/
2. ORA-16957 may appear in the alert.log together with other error messages or alone:
- Together with other errors:
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x2B3533343949] [PC:0x22F0025, kxsffir()+337] [flags: 0x0, count: 1]
Errors in file /diag/rdbms/orcl/trace/ORCL_j000_32621.trc (incident=353044):
ORA-07445: exception encountered: core dump [kxsffir()+337] [SIGSEGV] [ADDR:0x2B3533343949] [PC:0x22F0025] [Address not mapped to object] []
ORA-16957: SQL Analyze time limit interrupt
Incident details in: /diag/rdbms/orcl/ORCL/incident/incdir_353044/ORCL_j000_32621_i353044. -
ORA-00600: internal error code, arguments: [17183]
ORA-16957: SQL Analyze time limit interrupt
In these cases, investigate the underlying error that resulted in the Tuning Task timing out - The error appears without reference to other errors:
When the error appears in the alert.log alone, you need to investigate the SQL that is hitting the time limit in order to understand whether the timeout is legitmate or not: Is the SQL indeed large and complex and should it take 30 minutes to analyze?
In order to obtain more information on the relevant SQL, check the trace file indicated with the ORA-16957 error in the alert.log
Setting the following event may provide more information in the trace file:alter system set events '16957 trace name ERRORSTACK level 3';
Once the error occurs and a trace file is created disable the event as follows:alter system set events '16957 trace name context off';
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了