I enable the auto tuning sql task, I try to see results but never get anything, this is what I run.

BEGIN
dbms_auto_task_admin.enable(client_name => ‘sql tuning advisor’, operation => NULL, window_name => NULL);
END;

BEGIN
dbms_auto_task_admin.enable(client_name => ‘sql tuning advisor’, operation => NULL, window_name => ‘FRIDAY_WINDOW’);
dbms_auto_task_admin.enable(client_name => ‘sql tuning advisor’, operation => NULL, window_name => ‘SUNDAY_WINDOW’);
dbms_auto_task_admin.enable(client_name => ‘sql tuning advisor’, operation => NULL, window_name => ‘TUESDAY_WINDOW’);
END;

BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( ‘LOCAL_TIME_LIMIT’, 15);
dbms_sqltune.set_auto_tuning_task_parameter( ‘MAX_SQL_PROFILES_PER_EXEC’, 50);
dbms_sqltune.set_auto_tuning_task_parameter( ‘MAX_AUTO_SQL_PROFILES’, 10000);
END;

sys@COREDB> SET LINESIZE 80 PAGESIZE 0 LONG 100000
SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;sys@COREDB>
ERROR:
ORA-13631: The most recent execution of task SYS_AUTO_SQL_TUNING_TASK contains
no results.
ORA-06512: at “SYS.PRVT_ADVISOR”, line 5739
ORA-06512: at “SYS.PRVT_SQLADV_INFRA”, line 620
ORA-06512: at “SYS.DBMS_AUTO_SQLTUNE”, line 121
ORA-06512: at line 1

DBA_AUTOTASK_WINDOW_CLIENTS

sys> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME   WINDOW_NEXT_TIME     WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
MONDAY_WINDOW 20-JUN-16 10.00.00.000000 PM EST5EDT     FALSE ENABLED  DISABLED DISABLED DISABLED DISABLED
TUESDAY_WINDOW 21-JUN-16 10.00.00.000000 PM EST5EDT     FALSE ENABLED  DISABLED DISABLED ENABLED  DISABLED
WEDNESDAY_WINDOW 22-JUN-16 10.00.00.000000 PM EST5EDT     FALSE ENABLED  DISABLED ENABLED  DISABLED DISABLED
THURSDAY_WINDOW 23-JUN-16 10.00.00.000000 PM EST5EDT     FALSE ENABLED  DISABLED DISABLED ENABLED  DISABLED
FRIDAY_WINDOW 17-JUN-16 10.00.00.000000 PM EST5EDT     FALSE ENABLED  DISABLED DISABLED DISABLED DISABLED
SATURDAY_WINDOW 18-JUN-16 06.00.00.000000 AM EST5EDT     FALSE ENABLED  DISABLED DISABLED ENABLED  DISABLED
SUNDAY_WINDOW 19-JUN-16 06.00.00.000000 AM EST5EDT     FALSE ENABLED  ENABLED ENABLED  DISABLED DISABLED

DBA_SCHEDULRE_WINDOWS

sys> select window_name, to_char(START_DATE, ‘DD/MM/RRRR HH24:MI:SS’),to_char(next_start_date, ‘DD/MM/RRRR HH24:MI:SS’),duration, enabled, active from dba_scheduler_windows;

WINDOW_NAME TO_CHAR(START_DATE, TO_CHAR(NEXT_START_DURATION     ENABL ACTIV

SUNDAY_WINDOW 19/06/2016 06:00:00  +000 20:00:00     TRUE  FALSE
SATURDAY_WINDOW 18/06/2016 06:00:00  +000 20:00:00     TRUE  FALSE
FRIDAY_WINDOW 17/06/2016 22:00:00 +000 04:00:00     TRUE  FALSE
THURSDAY_WINDOW 23/06/2016 22:00:00 +000 04:00:00     TRUE  FALSE
WEDNESDAY_WINDOW 22/06/2016 22:00:00 +000 04:00:00     TRUE  FALSE
TUESDAY_WINDOW 21/06/2016 22:00:00 +000 04:00:00     TRUE  FALSE
MONDAY_WINDOW 20/06/2016 22:00:00 +000 04:00:00     TRUE  FALSE
WEEKEND_WINDOW 21/11/2015 00:00:00 +002 00:00:00     FALSE FALSE
WEEKNIGHT_WINDOW  18/11/2015 22:00:00  +000 08:00:00     FALSE FALSE

9 rows selected.

Check the output of the following:

select program_name, program_action from dba_scheduler_programs where program_name = ‘AUTO_SQL_TUNING_PROG’;

If there is no rows returned, do the following.

Load ?/rdbms/admin/execsqlt.sql to create the scheduler program that was dropped.

Nightly job should run:

GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 15

 

Advertisements