11g new Features-automatic SQL tuning (Automatic SQL Tuning)

11g new Features-automatic SQL tuning (Automatic SQL Tuning)

In Oracle 10g, an automatic SQL Tuning feature was introduced. In addition, ADDM monitors SQL statements that capture high loads.

In Oracle 11g, the automatic SQL Tuning feature is enhanced by running SQL Tuning Advisor. By default, automatic SQL tuning is run every night through automated tasks.

The process of automating SQL tuning:

1. Identify the SQL statement that needs tuning to identify the CPU and I/O load in the AWR

2. Tuning to generate a new SQL profile

When the automatic SQL Tuning task runs in the Maintenance window (Maintenance windows), the SQL statement identified in the previous step is tuned by the SQL Tuning Advisor. and generate the corresponding SQL profiles. If a statistic is found to be missing or out of date, Gather_stats_job will be notified

3. Test the new SQL Profile benefit%= (time_old–time_new)/(Time_old)

4. Execute a new SQL profile that meets the criteria

You can view dba_sql_profiles, which SQL profiles is automatically executed by the database (Type column)

5.DBA can generate reports for automatic SQL tuning activities. You can verify that the resulting SQL profile is valid, delete the newly generated SQL profile, and so on.

SQL tuning is not tuned for automatic sql:

-ad hoc statements and seldom-repeated SQL

-Parallel query

-long-running queries. For long-running SQL, this SQL is ignored if the resulting SQL profile is used for testing, or if it will take a long time to run. If you use the newly generated SQL profile to significantly reduce the time, this type of long-running SQL is not ignored.

-Recursive SQL

-DDL statements

-Using the statement above the INSERT, delete DML statement, you can use the SQL Tuning Advisor for tuning manually.

The above statement can be tuned manually using the SQL Tuning Advisor.

Configure automatic SQL tuning

Configure using the Dbms_sqltune package. In Maintenance window, the automatic SQL Tuning feature initiates a job (Sys_auto_sql_tuning_task). Generate a list of SQL that needs to be tuned and then tune it once for importance. Typically this job will run for an hour and you can set the job setting: Dbms_sqltune.set_tuning_task_parameter (' Sys_auto_sql_tuning_task ', ' time_limit ', 7200);

Begin Dbms_sqltune.set_tuning_task_parameter ('Sys_auto_sql_tuning_task','Local_time_limit',1400); Dbms_sqltune.set_tuning_task_parameter ('Sys_auto_sql_tuning_task','Accept_sql_profiles','TRUE'); Dbms_sqltune.set_tuning_task_parameter ('Sys_auto_sql_tuning_task','max_sql_profiles_per_exec', -); Dbms_sqltune.set_tuning_task_parameter ('Ssy_auto_sql_tuning_task','Max_auto_sql_profiles',10002); end;

managing automatic SQL Tuning Tasks

1. Turn on automatic SQL tuning

begin     Dbms_auto_task_admin.enable (          'SQL Tuning Advisor',           'null', #在所有维护窗口都开启           'null'  ); end;

2. Turn off automatic SQL tuning

begin     Dbms_auto_task_admin.disable (          'SQL Tuning Advisor',          ' null',          'null'); end;

Generate automatic SQL Tuning recommendations for text Format report:

Sql>variable my_rept CLOB; SQL>begin:my_rept:=Dbms_sqltune.report_auto_tuning_task (begin_exec=NULL, End_exec=NULL, type='TEXT', level='Typical', section=' All', object_id=NULL, Result_limit=NULL); End SQL> print:my_rept

Related views:

-dba_advisor_executions
-dba_advisor_sqlstats
-dba_advisor_sqlplans

11g new Features-automatic SQL tuning (Automatic SQL Tuning)