ORA-16957: SQL Analyze time limit interrupt

time_expired

First thing this morning, I was greeted with an email from my alert log monitor containing the error in the title.  This is one of the less cryptic error messages from an Oracle database, but what does it mean?

If you are an Oracle DBA and have been dealing with performance issues for any length of time, you are familiar with the SQL Tuning Advisor.  This tool is part of the Oracle Tuning Pack .  It takes one or more SQL statements as input, analyzes them, and displays recommendations to tune the statements with rationale and expected benefits.  These recommendations can include collecting object statistics, creating indexes, creating SQL profiles or SQL plan baselines, and even recommendations to rewrite.

The SQL Tuning Advisor has two modes – automatic and manual.  The automatic mode is referred to as the Automatic SQL Tuning Task or SQL Analyze for short.  This task when enabled runs nightly as part of the default maintenance plan.  Usually it runs happily in the background and goes mostly unnoticed.  Other times it generates an ORA-16957 error. This error means that it has not completed within its limit settings. You can view these settings like this:

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',
'SQL_LIMIT','LOCAL_TIME_LIMIT');

PARAMETER_NAME                 PARAMETER_VALUE  

------------------------------ ------------------------------  

LOCAL_TIME_LIMIT               1200  
TIME_LIMIT                     7200  
DEFAULT_EXECUTION_TYPE         TUNE SQL
SQL_LIMIT                      -1

The time_limit value is the total time in seconds that the SQL Analyze job is allowed per execution within the maintenance window.  The local_time_limit is the value in seconds that the SQL Analyze job is allowed to spend analyzing each individual SQL statement.  The sql_limit parameter is the number of SQL statements to examine per execution of the job.  The -1 number indicates no limit to the number of SQL statements the job can review within its time limit.

How does it determine which statements need to be analyzed?  From the Oracle documentation, the following quote is taken:

The automated SQL tuning task does not process the following types of SQL:

  • Ad hoc SQL statements or SQL statements that do not repeat within a week
  • Parallel queries
  • Queries that take too long to run after being SQL profiled, so that it is not practical for SQL Tuning Advisor to test execution
  • Recursive SQL

 You can run SQL Tuning Advisor on demand to tune the preceding types of SQL statements.

 

Can we find out what SQL statement caused the ORA-16957 error?

Get the execution_name for the long run day:

set lines 200 pages 100 
col error_message for a60 
col execution_name for a15 

select execution_name, advisor_name,to_char(execution_start,'dd-mon-yy hh:mi:ss'), to_char(execution_end,'dd-mon-yy hh:mi:ss'), status,error_message 
from dba_advisor_executions 
where task_name = 'SYS_AUTO_SQL_TUNING_TASK' 
order by execution_start desc;

Take the name of the execution you are interested in and place it into the query below to identify the ID of the SQL causing the overrun.  If a sufficient amount of time has passed, you may not get any data from this query.

SELECT sql_id, sql_text FROM dba_hist_sqltext 
WHERE sql_id IN (SELECT attr1 FROM dba_advisor_objects 
WHERE execution_name = '&execution_name' 
AND task_name = 'SYS_AUTO_SQL_TUNING_TASK' 
AND type = 'SQL' AND bitand(attr7,64) <> 0 );


 Then you can take the SQL_ID from the query above and run it through the manual mode of the SQL Tuning Advisor:

Create the tuning task:

DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sql_id => '&SQL_ID', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 99999,   --I use a large time out value 
task_name => 'my_tuning_task', 
description => 'Tuning task for statement blahblah.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/

 

Execute the tuning task:

BEGIN 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_tuning_task'); 
end; 
/

When the task is complete, get the report:

SET LONG 5000 
SET LONGCHUNKSIZE 5000 
SET LINESIZE 200 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tuning_task') from DUAL;

Be sure to drop the tuning task before executing another.  NOTE – If the tuning task results recommend using a SQL Profile, then you MUST accept the profile before dropping the tuning task.  Otherwise, the recommended profile will be lost and you will need to run the tuning task again.

BEGIN
  dbms_sqltune.drop_tuning_task (task_name => 'my_tuning_task');
END;
/

 

In the event that the ORA-16957 error is seen on a regular basis, you may need to adjust the SQL Analyze task limits or limit the number of SQL statements analyzed. Use the DBMS_SQL_AUTOTUNE.SET_TUNING_TASK_PARAMETER procedure to change these values.  Here is an example of changing the time limit to 7200 seconds (2 hours):

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

 

If you need to determine if the SQL Analyze job is running against your database, use this query:

SELECT CLIENT_NAME, STATUS
 FROM   DBA_AUTOTASK_CLIENT
 WHERE  CLIENT_NAME = 'sql tuning advisor';
  
 CLIENT_NAME          STATUS
 -------------------- --------
 sql tuning advisor   ENABLED

 

You can disable or enable the SQL Analyze job with these commands:

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE (
     client_name => 'sql tuning advisor'
 ,   operation   => NULL
 ,   window_name => NULL
 );
 END;
 /


 BEGIN
   DBMS_AUTO_TASK_ADMIN.DISABLE (
     client_name => 'sql tuning advisor'
 ,   operation   => NULL
 ,   window_name => NULL
 );
 END;
 /

If you are interested in viewing the latest SQL Analyze report:

VARIABLE l_report CLOB;

BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text,
level => DBMS_SQLTUNE.level_typical,
section => DBMS_SQLTUNE.section_all,
object_id => NULL,
result_limit => NULL
);
END;
/

SET LONG 1000000
PRINT :l_report

If this task is enabled in your database, then take advantage of its analysis.  Regular viewing of its output may help identify ‘usual suspects’ that continue to run poorly.

 

Advertisements