标题: ORA-16957: SQL Analyze time limit interrupt [打印本页] 作者: jiawang 时间: 2020-10-21 15:29 标题: ORA-16957: SQL Analyze time limit interrupt 报错现象：
Fri 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:
文档 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;
/
Or
-- 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.
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';