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.