As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks.It is also indirectly disabled by setting the-- Enable BEGIN DBMS_AUTO_TASK_ADMIN.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; / -- Disable BEGIN DBMS_AUTO_TASK_ADMIN.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; / STATISTICS_LEVEL parameter to BASIC , as this stops automatic statistics gathering by the AWR.The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package controls the behavior of the SQL tuning advisor. The parameters specifically for the automatic runs include:
%_ADVISOR_PARAMETERS views.The following code shows how theCOLUMN 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 ('ACCEPT_SQL_PROFILES', 'MAX_SQL_PROFILES_PER_EXEC', 'MAX_AUTO_SQL_PROFILES'); PARAMETER_NAME PARAMETER_VALUE ------------------------------ ------------------------------ ACCEPT_SQL_PROFILES FALSE MAX_SQL_PROFILES_PER_EXEC 20 MAX_AUTO_SQL_PROFILES 10000 3 rows selected. SQL> SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.TheBEGIN DBMS_SQLTUNE.set_tuning_task_parameter( task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE'); END; / REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.As you can see from the above report, this was run against a very quiet system, so there were no candidate SQL statements to process. If this were run against a more active system, you might expect the report to contain the following sections:VARIABLE l_report CLOB; BEGIN :l_report := DBMS_SQLTUNE.report_auto_tuning_task( begin_exec => NULL, end_exec => NULL, type => DBMS_SQLTUNE.type_text, -- 'TEXT' level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL' section => DBMS_SQLTUNE.section_all, -- 'ALL' object_id => NULL, result_limit => NULL); END; / SET LONG 1000000 PRINT :l_report L_REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK Tuning Task Owner : SYS Workload Type : Automatic High-Load SQL Workload Execution Count : 31 Current Execution : EXEC_1_25 Execution Type : TUNE SQL Scope : COMPREHENSIVE Global Time Limit(seconds) : 3600 Per-SQL Time Limit(seconds) : 1200 Completion Status : COMPLETED Started at : 01/16/2008 22:00:06 Completed at : 01/16/2008 22:00:46 Number of Candidate SQLs : 0 Cumulative Elapsed Time of SQL (s) : 0 ------------------------------------------------------------------------------- SQL>
|
DBA's Home Page - A website for Oracle, Greenplum and PostgreSQL DBA's > DBA's Forum, Routine Tasks and Activities >