Fabien Celaia

[Actualité] Corriger les fenêtres de maintenance de votre base de données Oracle

Noter ce billet

1 Commentaire

par

, 26/09/2017 à 17h48 (12000 Affichages)

Introduction

Oracle intègre un certain nombre de tâches qu'il doit exécuter en arrière-plan. Elles dépendent des options Oracle activées, mais la plupart du temps, on retrouve principalement

Optimizer Statistics Gathering (rejoue les statistiques des tables n'ayant pas de statistiques à jour et plus de 10 % de données modifiées) ;
Segment Advisor (analyse la segmentation des données et donne des conseils quant à leur réorganisation) ;
Automatic SQL Tuning (depuis 11.2, analyse des requêtes traitées et conseils d'amélioration).

Ces tâches peuvent avoir des impacts négatifs sur les traitements en cours. Elles ont donc été créées arbitrairement par Oracle dans une période qui lui semble creuse, à savoir entre 22 h et 2 h du matin.
Dans la plupart des serveurs de production que j'ai eu l'occasion d'administrer, cette période correspond pourtant à une période très chargée : batches de nuits, sauvegardes, chargement des ODS...
Il convient donc de recalibrer ces fenêtres de maintenance afin qu'elles collent au mieux aux périodes creuses de vos systèmes... et souvent, un système n'a pas les mêmes périodes creuses qu'un autre.

Ah ! encore un petit mot... il ne sert à rien d'exécuter un Segment Advisor sept fois par semaine s'il n'est pas traité / lu / analysé.
Si vous ne l'utilisez pas, supprimez-le de votre plan de maintenance ou inactivez-le.

État des lieux
Dans un premier temps, munissez-vous de votre bâton de pèlerin (un plutôt de votre agenda) et auditez vos opérateurs pour déterminer les zones creuses ou moins impactantes. Évitez comme la peste, par exemple, un calcul de statistiques pendant des chargements massifs de données ou des vidanges de tables.
La requête suivante vous remonte l'état des lieux des fenêtres de maintenance

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select OWNER||'.'||WINDOW_NAME WINDOWS,
    REPEAT_INTERVAL,
    COMMENTS,
    NEXT_START_DATE PROCHAIN_RUN,
    NEXT_START_DATE+DURATION FIN_PROCHAIN_RUN
from dba_scheduler_windows
where enabled = 'TRUE'
and RESOURCE_PLAN='DEFAULT_MAINTENANCE_PLAN';

Correction
Modification des fenêtres de maintenance par défaut
Tout peut se faire via ligne de commande ou via Oracle Enterprise Manager / grid control (cf. ci-dessous).
Voici par exemple la façon de faire pour faire glisser les fenêtres par défaut à des heures plus supportables, soient

en jour de semaine, de 1 h à 6 h du matin ;
le samedi, de 13 h à minuit ;
le dimanche, toute la période.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
BEGIN
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."TUESDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."WEDNESDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."THURSDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."FRIDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."SATURDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."SUNDAY_WINDOW"',force=>TRUE);
 
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(5, 'hour'));
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."TUESDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(5, 'hour'));
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."TUESDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."WEDNESDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(5, 'hour'));
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."WEDNESDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=WED;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."THURSDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(5, 'hour'));
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."THURSDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=THU;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."FRIDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(5, 'hour'));
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."FRIDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SATURDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(23, 'hour'));
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SATURDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SUNDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(24, 'hour'));
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SUNDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=0;BYMINUTE=0;BYSECOND=0');
 
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."TUESDAY_WINDOW"');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."WEDNESDAY_WINDOW"');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."THURSDAY_WINDOW"');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."FRIDAY_WINDOW"');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."SATURDAY_WINDOW"');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."SUNDAY_WINDOW"');
END;
/

Ajout d'une fenêtre de maintenance
Dans un premier temps, on définit une nouvelle fenêtre de maintenance, et on lui attribue un plan de ressource par défaut. Dans l'exemple ci-dessous, j'ajoute une fenêtre sur les pauses de midi (du midi à 13 h 30) de la semaine de travail :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"MIDDAY_WINDOW"',
resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
start_date=>systimestamp at time zone 'Europe/Zurich',
duration=>numtodsinterval(90, 'minute'),
repeat_interval=>'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=12;BYMINUTE=0;BYSECOND=0',
end_date=>null,
window_priority=>'LOW',
comments=>'Fenêtre de maintenance pour les pauses de midi en semaine');
END;
/

Ensuite, on rattache cette nouvelle fenêtre au groupe de maintenance souhaité

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER(group_name=>'"MAINTENANCE_WINDOW_GROUP"',window_list=>'"MIDDAY_WINDOW"');
END;
/

Traitement d'un Segment Advisor

C'est bien beau tout ça : vous avez maintenant une tâche de fond qui analyse vos segments... encore faudrait-il traiter l'information.

Je peux, par exemple, décider de ne faire cette analyse qu'une seule fois par semaine. Je désactive donc pour les autres jours :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
BEGIN
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>'MIDDAY_WINDOW');
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>'THURSDAY_WINDOW');
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>'FRIDAY_WINDOW');
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>'SATURDAY_WINDOW');
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>'MONDAY_WINDOW');
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>'TUESDAY_WINDOW');
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>'WEDNESDAY_WINDOW');
END;

Un petit SQL pour afficher les recommandations :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT segment_owner||'.'||segment_name||' ('||segment_type||')' as objet,
           round( allocated_space/1024/1024,1 ) allocation_Mo,
           round( used_space/1024/1024, 1 ) Utilisation_Mo,
           round( reclaimable_space/1024/1024) Reclamation_Mo,
           round( reclaimable_space/allocated_space*100,0 ) Pct_recupere,
           recommendations Conseil
      FROM TABLE(dbms_space.asa_recommendations())
order by 4 desc

Et un autre pour générer les traitements à faire:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT (CASE     WHEN segment_type='INDEX'       THEN         'ALTER INDEX '||segment_owner||'.'||segment_name||' shrink space;'
                  WHEN segment_type='TABLE' THEN  'ALTER TABLE '||segment_owner||'.'||segment_name||' enable row movement;'||chr(10)
                                               ||'ALTER TABLE '||segment_owner||'.'||segment_name||' shrink space;' END ) 
FROM TABLE(dbms_space.asa_recommendations())

Oracle Enterprise Manager / Grid control
Fenêtres de maintenance, groupes de maintenance, jobs agendés, advisors... si tout ceci vous semble un peu abscons, vous pouvez préférer la navigation graphique au travers du Grid control.
Connectez-vous à votre base cible de prédilection et naviguez dans le menu Administration -> Oracle Scheduler.
Vous pourrez y créer/modifier vos fenêtres de maintenance (sous Windows), les attribuer au bon groupe (sous Window Groups) et déterminer ce que vous voulez activer et quand (sous Automated Maintenance Tasks)