Job de collecte des statistiques
Dans la partie I de cet article nous avons décrit en quoi des statistiques à jour étaient importantes pour le bon fonctionnement de l’optimiseur et comment Oracle gérait leur obsolescence.
Dans cette partie II, nous décrivons le Job Oracle chargé de la collecte. Celui-ci est actif par défaut après la création de toute base depuis Oracle 10g. Il y a eu des évolutions importantes en version 11g que nous décrivons ci-dessous.
Job et Windows
Le job auto optimizer stats collection est conçu pour recalculer les statistiques de toutes les tables ayant des statistiques obsolètes ou vides. Son exécution a lieu une fois par jour en fonction de la définition de « fenêtres » (WINDOWs) associées.
Il y a une fenêtre par jour de la semaine (MONDAY, TUESDAY, …. SUNDAY). Les 7 fenêtres sont regroupées dans le groupe de fenêtres MAINTENANCE_WINDOW_GROUP.
Une fenêtre est un objet très simple avec principalement deux propriétés : une date-heure d’ouverture de la fenêtre et une durée. Lorsque l’heure du système est égale à l’heure d’ouverture de la fenêtre, celle-ci devient active. Le Job auto optimizer stats, qui est associé au groupe de fenêtres démarre alors.
Remarque : Depuis Oracle 11g, le job de collecte n’est plus créé à partir du package DBMS_SCHEDULER comme en 10g mais possède sa propre couche logicielle interne à la base appelée autotask framework.
Fonctionnement du Job automatique
Ce job commence par les tables ayant eu le plus de modifications. Si le job se termine avant la fin de la fenêtre, toutes les tables ont des statistiques à jour. Si la fenêtre se termine avant la fin normale du JOB, celui-ci est interrompu. Les objets qui n’auront pas pu avoir de nouvelles statistiques seront traités à la prochaine ouverture de fenêtre.
Remarque : d’autres jobs sont associés aux fenêtres de maintenance ci-dessus : le job du segment advisor et celui de l’automatic SQL tuning :
select CLIENT_NAME from DBA_AUTOTASK_CLIENT CLIENT_NAME ---------------------------------------------------------------- auto optimizer stats collection auto space advisor sql tuning advisor
Le rôle des 2 autres jobs sort du contexte de l’article mais il faut savoir qu’il s’exécuteront en parallèle du Job de collecte des statistiques à moins de les désactiver.
Contrôle du Job automatique
Le job de collecte de statistiques peut être inhibé pour toutes le fenêtres en une seule commande PL/SQL ou dans Entreprise Manager (Attention ! Dans ce cas plus aucune table n’aura des statistiques rafraichies).
BEGIN /* Desactivation */ DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END ;
BEGIN /* Activation */ DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END ;
A tout moment on peut voir l’historique de l’exécution des Jobs
SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%'; JOBS JOBS JOBS CLIENT_NAME WINDOW_NAME JOBS_CREATED JOBS_STARTED JOBS_COMPLETED ------------------------------- ----------------- ------------ ------------ -------------- auto optimizer stats collection FRIDAY_WINDOW 1 1 1 auto optimizer stats collection SATURDAY_WINDOW 4 4 4 auto optimizer stats collection SUNDAY_WINDOW 4 4 4 auto optimizer stats collection TUESDAY_WINDOW 1 1 1 auto optimizer stats collection WEDNESDAY_WINDOW 1 1 1
Contrôle des fenêtres
Le contrôle du Job se fait essentiellement via le paramétrage des fenêtres, le but n’étant généralement pas désactiver le Job totalement mais d’en contrôler finement l’exécution. Pour cela on va ajuster les fenêtres pour qu’il se déclenche au moment opportun. Le fait d’avoir 7 fenêtres offre une grande flexibilité.
Paramétrage par défaut des fenêtres :
- Les fenêtres de semaine (MONDAY à FRIDAY) s’ouvrent à 22h et durent 4h. Elles se ferment donc à 2h du matin.
- Les fenêtres de week-end (SATURDAY et SUNDAY) s’ouvrent à 6h du matin et durent 20h. Elles se ferment donc à 2h du matin (à J+1).
Ces horaires ont été décidés par Oracle dans l’idée que les applications se trouvaient en fonctionnement non intensif à ces moments. Mais cela dépend et votre application peut justement être très active à ce moment (utilisation 24/7, exécution de batchs, ajout massif de données, export datapump, etc …). Pas de souci en revanche s’il y a un backup online avec Rman au même moment.
Ainsi, la première chose que doit faire le DBA est de s’assurer que l’ouverture de ces fenêtres est compatible avec l’utilisation de la base.
Par exemple si des traitements lourds (chargement de données par exemple) démarrent le soir à 22h, il ne faut pas que la fenêtre de maintenance des statistiques s’ouvre aussi à 22h !
Un peu de syntaxe
La modification des fenêtres peut se faire par Enterprise Manager ou un script PL/SQL.
Exemple 1 : Modification de la fenêtre SUNDAY
BEGIN /* Desactivation fenetre SUNDAY */ DBMS_SCHEDULER.DISABLE(name=>'"SYS"."SUNDAY_WINDOW"',force=>TRUE); /* Modification de la durée à 540 minutes */ DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SUNDAY_WINDOW"', attribute=>'DURATION', value=>numtodsinterval(540, 'minute')); /* Modification de l’heure du declenchement a 11h30 */ DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SUNDAY_WINDOW"', attribute=>'REPEAT_INTERVAL', value=>'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=11;BYMINUTE=30;BYSECOND=0'); /* Re-activation fenetre SUNDAY */ DBMS_SCHEDULER.ENABLE(name=>'"SYS"."SUNDAY_WINDOW"'); END;
Exemple 2 : Désactivation / Re-activation de toutes les fenêtres (désactive tous les jobs associés) :
BEGIN /* Désactivation de l’ensemble des fenètres */ DBMS_SCHEDULER.DISABLE( name => 'SYS.MAINTENANCE_WINDOW_GROUP', force => TRUE); /* Re-activation de l’ensemble des fenètres */ DBMS_SCHEDULER.ENABLE( name => 'SYS.MAINTENANCE_WINDOW_GROUP'); END; /
Remarque : les tâches automatiques du segment advisor et de l’automatic sql tuning dépendent de ces fenêtres et seront également désactivées si on désactive une ou plusieurs fenêtres.
Surveillance des Windows et du Job
La requête suivante permet de voir la durée des fenêtres et du job de collecte de statistiques :
col CLIENT_NAME for A20 col WINDOW_GROUP for a20 col MEAN_JOB_DURATION for a15 col MEAN_JOB_CPU for A20 col MEAN_JOB_ATTEMPTS A10 select CLIENT_NAME, STATUS, WINDOW_GROUP, MEAN_JOB_DURATION, MEAN_JOB_CPU, MEAN_JOB_ATTEMPTS, MAX_DURATION_LAST_7_DAYS, WINDOW_DURATION_LAST_7_DAYS from DBA_AUTOTASK_CLIENT where client_name like '%optimizer%' Dans la partie III nous décrivons les concepts plus avancés de la collecte des statistiques (statistiques sur le dictionnaire, sur le hardware, resource manager, etc ...)