ORA-16957: "SQL Analyze time limit interrupt" Errors

刘泽宇

SYMPTOMS

The following error is reported in the alert.log:

ORA-16957: SQL Analyze time limit interrupt

  

CAUSE

The ORA-16957 error is an internal error code used to indicate that SQL Tuning Task has reached the time limit for tuning a specific sql.
The default time limit is 3600 seconds.

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.
  
• 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 ERRORSTACK off';