Apprendre � corriger les fen�tres de maintenance de votre base de donn�es Oracle

Un tutoriel de Fadace

Le 27 septembre 2017, par Fabien Celaia

0PARTAGES

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

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

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

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

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

BEGINdbms_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

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

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)

Vous avez lu gratuitement 0 articles depuis plus d'un an.
Soutenez le club developpez.com en souscrivant un abonnement pour que nous puissions continuer � vous proposer des publications.

Une erreur dans cette actualit� ? Signalez-nous-la !

Votre nom : Votre e-mail :

D�crivez l'erreur que vous souhaitez porter � notre connaissance :

Identifiez-vous

Cr�er un compte

Apprendre � corriger les fen�tres de maintenance de votre base de donn�es Oracle

Un tutoriel de Fadace

Identifiant
Mot de passe

Mot de passe oubli� ?

Identifiez-vous

Cr�er un compte

Apprendre � corriger les fen�tres de maintenance de votre base de donn�es Oracle Un tutoriel de Fadace

Apprendre � corriger les fen�tres de maintenance de votre base de donn�es Oracle

Un tutoriel de Fadace