Oracle automatic sql tuning advisor

1. New features of oracle11g

The new asta task runs nightly by default. Starts by default when creating a database. Sys_auto_sql_tuning_task, which runs automatically nightly as part of the automated management task framework.

Extract TOP SQL from AWR on a weekly, daily, hourly basis, and give it to advisor for analysis and optimization.

Oracle does not automatically implement all sql profile generated during tuning, it only implements SQL profile recommendations that improve performance by at least 3x.

-query the open status

Select client_name,status from DBA_AUTOTASK_CLIENT

Select * from DBA_AUTOTASK_WINDOW_CLIENTS

-query parameter settings

Select * from dba_ADVISOR_parameters where task_name='SYS_AUTO_SQL_TUNING_TASK'

When 'ACCEPT_SQL_PROFILES' is set to true, oracle automatically applies profile

-tuning will not be performed automatically in the following situations, and these recommendations must be implemented manually like oracle10g

Parallel query

Specific SQL

Any SQL statement that has been executed up to once in the past week

DDL statement

Even after the profile is constructed, the query that is still at the top of the list of poorly performing queries

Recursive SQL statement

Create an index

Refresh statistics

Rewrite SQL

-View improvement report

SELECT sys.DBMS_SQLTUNE.REPORT_TUNING_TASK ('SYS_AUTO_SQL_TUNING_TASK') from dual

-on / off

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