ORA-16957: SQL Analyze time limit interrupt

最新推荐文章于 2024-10-21 16:24:33 发布

转载最新推荐文章于 2024-10-21 16:24:33 发布 · 864 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：http://www.cnblogs.com/ss-33/p/10159350.html

本文档详细介绍了如何解决Oracle数据库中SQL Tuning Advisor运行超时的问题，包括检查和调整时间限制参数，以及如何在出现ORA-16957错误时进行故障排查。

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:

COLUMN parameter_value FORMAT A30
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:

BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'TIME_LIMIT', value => 7200);
END;
/

-- This procedure updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER as used for the reserved auto tuning task, SYS_AUTO_SQL_TUNING_TASK.
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';