ORACLE 11G Disable SQL TUNING ADVISOR

Production on a set of 11g database Alert.log error ora-16957:sql Analyze time limit interrupt.

Querying MOS-related documents troubleshooting:ora-16957: "SQL Analyze time limit Interrupt" Errors (document ID 1275248.1)

The ORA-16957 error is a internal error code used to indicate that SQL Tuning Task have reached the time limit for Tuning A specific SQL.

The default time limit is 3600 seconds.

1. Check The current timing:

COLUMN parameter_value FORMAT A30 SELECT parameter_name, parameter_value from dba_advisor_parameters WHERE task_name = ' SY S_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 means that the background auto-parse SQL takes longer than the default time limit of 3600s and requires the use of dbms_sqltune. Set_tuning_task_parameter packet growth time limit.

Generally in production, the SQL TUNING ADVISOR is not turned on by default. You can use the following code to turn off automatic SQL TUNING ADVISOR.

--Query the current task status [email protected]> select client_name,status from dba_autotask_client; client_name                                                         status---------------------------------------------------------------- --------auto  optimizer stats collection                                    ENABLEDauto space advisor                                                 enabledsql  tuning advisor                                                 enabled--disabling sql tuning  advisor[email protected]> begin  2  dbms_auto_task_admin.disable (   3      client_name =>  ' Sql tuning advisor ',   4      operation   => NULL,  5       window_name => null);   6  end;  7   /pl/sql procedure successfully completed.--querying the status again [email protected]>  Select client_name,status from dba_autotask_client; client_name                                                         status---------------------------------------------------------------- --------auto  optimizer stats collection                                    ENABLEDauto space advisor                                                 enabledsql tuning advisor                                                  disabled--Enable Sql tuning advisorbegindbms_auto_task_admin.enable (     client_name =>  ' Sql tuning advisor ',    operation    => null,    window_name => null); END;

Reference Document: Http://blog.chinaunix.net/uid-25528717-id-3172008.html

Reference Document: Http://www.cnblogs.com/suredandan/p/3200157.html

Reference Document: http://blog.itpub.net/235507/viewspace-1137629/

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN12332

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1870577

ORACLE 11G Disable SQL TUNING ADVISOR