Automatic Maintenance und Optimizer Statistic Collection verwalten unter 12c und 11g

Theoretisch lassen sich die Automatic Maintenance Tasks mit denen die automatischen Optimizer Statistiken gesteuert werden, über Cloud Control recht einfach konfigurieren. Der Haken ist nur, dass mit Version 12c die Datenbank standardmäßig nur noch mit dem Enterprise Manager Database Express 12c ausgeliefert wird. Mit diesem lässt sich allenfalls noch Ablesen, dass Automatic Maintenance im Einsatz ist (Konfiguration > Verwendung von Datenbank-Features). Im „standard“ Enterprise Manager 11g war die entsprechende Konfigurationsmöglichkeit noch eingebaut (Server > Automatisierte Wartungs-Tasks).

Bleibt bei 12c ohne Cloud Control also nur der Weg über die Command Line. Die nachfolgenden Befehle funktionieren sowohl unter 12c als auch 11g.

Standardmäßig installiert kommt die Datenbank mit drei vorkonfigurierten Automatic Maintenance Tasks, Clients genannt. Erstens die Erfassung der Optimizer-Statistiken (Optimizer Statistics Collection), dem Segment Advisor (Space Advisor) und dem automatischem SQL Tuning (SQL Tuning Advisor).

a. Status

Zunächst die Abfrage nach dem aktuellen Stand der Dinge in DBA_AUTOTASK_CLIENTS. Nur in der Enterprise Edition ist der SQL Tuning Advisor ENABLED.

SQL> select client_name, status, attributes from dba_autotask_client;

CLIENT_NAME                        STATUS   ATTRIBUTES
---------------------------------  -------  ----------------------------------------
auto optimizer stats collection    ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor                 ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor                 ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL 

Als weitere Abfrage die eingerichteten Zeitfenster in DBA_AUTOTASK_WINDOW_CLIENTS:

select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;

WINDOW_NAME               AUTOTASK_STATUS  OPTIMIZER_STATS
------------------------  ---------------- ----------------
SUNDAY_WINDOW             ENABLED           ENABLED
SATURDAY_WINDOW           ENABLED           ENABLED
FRIDAY_WINDOW             ENABLED           ENABLED
THURSDAY_WINDOW           ENABLED           ENABLED
WEDNESDAY_WINDOW          ENABLED           ENABLED
TUESDAY_WINDOW            ENABLED           ENABLED
MONDAY_WINDOW             ENABLED           ENABLED 

Per default sind sieben Zeitfenster eingerichtet:

select * from DBA_SCHEDULER_WINDOWS; 

DBA_AUTOTASK_SCHEDULE liefert die eingerichteten Zeitfenster für die nächsten 32 Tage:

select * from DBA_AUTOTASK_SCHEDULE order by start_time; 

b. Aktivieren / Deaktivieren

Ganz generell lassen sich die Auto Tasks über das DBMS_AUTO_TASK_ADMIN Package steuern.

Um die AUTO_TASKS generell zu starten und zu stoppen genügt ein enable bzw. disable.

exec dbms_auto_task_admin.disable;

select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;

WINDOW_NAME               AUTOTASK_STATUS  OPTIMIZER_STATS
------------------------  ---------------- ----------------
SUNDAY_WINDOW             DISABLED          ENABLED
SATURDAY_WINDOW           DISABLED          ENABLED
FRIDAY_WINDOW             DISABLED          ENABLED
THURSDAY_WINDOW           DISABLED          ENABLED
WEDNESDAY_WINDOW          DISABLED          ENABLED
TUESDAY_WINDOW            DISABLED          ENABLED
MONDAY_WINDOW             DISABLED          ENABLED 

Folgendes Kommando deaktiviert ausschließlich die Optimizer Stats Collection:

begin  
dbms_auto_task_admin.disable(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
end;
/ 

select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;

WINDOW_NAME               AUTOTASK_STATUS  OPTIMIZER_STATS
------------------------  ---------------- ----------------
SUNDAY_WINDOW             ENABLED           DISABLED
SATURDAY_WINDOW           ENABLED           DISABLED
FRIDAY_WINDOW             ENABLED           DISABLED
THURSDAY_WINDOW           ENABLED           DISABLED
WEDNESDAY_WINDOW          ENABLED           DISABLED
TUESDAY_WINDOW            ENABLED           DISABLED
MONDAY_WINDOW             ENABLED           DISABLED 

SQL> select client_name, status from dba_autotask_client;

CLIENT_NAME                        STATUS   
---------------------------------  ---------        
auto optimizer stats collection    DISABLED 
auto space advisor                 ENABLED
sql tuning advisor                 ENABLED 

c. Startzeit ändern

Will man die Startzeit ändern, muss man den REPEAT_INTERVAL anpassen. Nachfolgend ändern wir für das MONDAY_WINDOW die Startzeit auf 5 Uhr mit DBMS_SCHEDULER.SET_ATTRIBUTE:

begin  
dbms_scheduler.set_attribute(
    name      => 'MONDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0');
end;
/ 

select window_name, repeat_interval, from dba_scheduler_windows where window_name = 'MONDAY_WINDOW';

WINDOW_NAME      REPEAT_INTERVAL
---------------- -----------------------------------------------------
MONDAY_WINDOW    freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0 

d. Laufzeit (Duration) ändern

Reicht das Zeitfenster von 4 Stunden nicht aus um alle Tabellen abzuarbeiten und läuft die Optimizer Stats Collection aus der vorgegebenen Dauer, muss die DURATION angepasst werden. Nachfolgend setzen wir das Fenster auf 5 Stunden hoch.

begin
  dbms_scheduler.set_attribute(
    name      => 'MONDAY_WINDOW',
    attribute => 'duration',
    value     => numtodsinterval(5, 'hour'));
end;
/ 

select window_name, duration from dba_scheduler_windows where window_name = 'MONDAY_WINDOW';
 
WINDOW_NAME      REPEAT_INTERVAL
---------------  --------------------
MONDAY_WINDOW    +00 05:00:00.000000

e. Neues Zeitfenster erstellen

begin
  dbms_scheduler.create_window(
    window_name     => 'SPECIAL_WINDOW',
    duration        =>  numtodsinterval(3, 'hour'),
    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
    repeat_interval => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=0;BYSECOND=0');
  dbms_scheduler.add_group_member(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    member      => 'SPECIAL_WINDOW');
end;
/