Oracle SQL Auto tuning advisor task

Oracle SQL auto tuning advisor task and implementing sql profiles


Running status of Oracle auto tuning adviosor task  can be checked using below queries



select * from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NAME; 

select client_name,status,window_group from dba_autotask_client; 

select * from dba_ADVISOR_TASKS where TASK_NAME='SYS_AUTO_SQL_TUNING_TASK'; 

select * from dba_autotask_job_history where client_name='sql tuning advisor';



if sql tuning tasks are not properly running you can recreate the task using below command

$ sqlplus / as sysdba 

SQL> @?/rdbms/admin/execsqlt.sql 



if you want to run sql tuning task on total database (if you dont know what are all sqls performing slow) , run below command

SQL> exec DBMS_AUTO_SQLTUNE.execute_auto_tuning_task; 


recommendation will be displayed on Enterprise Manager