Les mises à jour cachées des statistiques avec Oracle 10gR2 et le package DBMS_SCHEDULER

Introduction

Avec Oracle 10gR2, le package DBMS_JOB est obsolète et remplacé par le package PL/SQL DBMS_SCHEDULER. Ce package permet de programmer des jobs.

Lors des migrations des versions Oracle 8i/9i vers Oracle 10g avec le script utlrecomp.sql (pour les recompilations) ou lors de la création de nouvelles instances Oracle 10gR2, des jobs sont automatiquement créés et activés avec DBMS_SCHEDULER.

Cet article propose de décortiquer les nouveaux jobs systèmes créés et notamment ceux qui effectuent les calculs des statistiques de façon "cachée" vers 22h00, jobs très dangereux qu'il est important d'étudier. Il est même fortement recommandé de désactiver ces jobs de calculs des statistiques car ils se basent sur une procédure stockée interne non documentée et bien souvent non adaptée à un environnement Oracle donné.

L'objectif n'est pas ici de décrire la nouveauté Oracle 10g du package DBMS_SCHEDULER, qui présente peu d'intérêt dans un contexte entreprise où un ordonnanceur standard est disponible (ControlM, etc...), mais les quelques vues et commandes utiles pour retrouver et déprogrammer au besoin les traitements créés avec DBMS_SCHEDULER.

Impact inopiné des jobs créés automatiquement avec Oracle 10gR2

Une instance Oracle 10gR2 peut soulever à des heures inattendues des erreurs comme par exemple l'erreur ORA-25191 "cannot reference overflow table of an index-organized table" ci-dessous :

Mon Oct  5 22:00:05 2009
Errors in file /Software/oracle/Instances/RISKD/bdump/riskd_j001_1686.trc:
ORA-12012: error on auto execute of job 425420
ORA-25191: cannot reference overflow table of an index-organized table

Le fichier de trace donne explicitement le module qui a généré cette erreur :

/Software/oracle/Instances/RISKD/bdump/riskd_j001_1686.trc
...
*** ACTION NAME:(GATHER_STATS_JOB) 2009-10-05 22:00:05.155
*** MODULE NAME:(DBMS_SCHEDULER) 2009-10-05 22:00:05.155
 ...

Par manque de temps pour lire l'intégralité de la documentation Oracle 10g, c'est avec des exceptions levées que l'on découvre des programmations cachées de mise à jour des statistiques avec la procédure GATHER_STATS_JOBS du package DBMS_SCHEDULER, même si des mises à jour de statistiques personnalisées sont déjà en place et maîtrisées.

Ce job de calcul des statistiques montre qu'Oracle 10g a encore beaucoup de difficultés à mettre à jour les statistiques à la volée contrairement à Sybase et SQL Server grâce au process HOUSEKEEPER. Oracle 10g annonçait une meilleure gestion des statistiques mais sans annoncer qu'il s'agissait d'une programmation de jobs cachés de calcul des statistiques à 22h00, heure à laquelle il n'est peut être pas souhaitable de calculer les statistiques (batches, sauvegardes etc...).

Les vues DBA_SCHEDULER%

Retrouver les vues DBA_SCHEDULER%

La vue système DBA_VIEWS permet de retrouver rapidement les vues en relation directe avec DBMS_SCHEDULER. La liste est donnée à titre indicatif :

select view_name from dba_views where view_name like 'DBA_SCHEDU%';
DBA_SCHEDULER_PROGRAMS
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_WINDOWS
DBA_SCHEDULER_PROGRAM_ARGS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAILS
DBA_SCHEDULER_WINDOW_LOG
DBA_SCHEDULER_WINDOW_DETAILS
DBA_SCHEDULER_WINDOW_GROUPS
DBA_SCHEDULER_WINGROUP_MEMBERS
DBA_SCHEDULER_SCHEDULES
DBA_SCHEDULER_RUNNING_JOBS
DBA_SCHEDULER_GLOBAL_ATTRIBUTE
DBA_SCHEDULER_CHAINS
DBA_SCHEDULER_CHAIN_RULES
DBA_SCHEDULER_CHAIN_STEPS
DBA_SCHEDULER_RUNNING_CHAINS

La vue DBA_SCHEDULER_JOB_LOG

La vue DBA_SCHEDULER_JOB_LOG donne par exemple le statut des derniers jobs.

select log_date, job_name, status from dba_scheduler_job_log

LOG_DATE                              JOB_NAME                  STATUS
-------------------------------       -----------------------   -------------
09/10/09 22:00:17,739410 +02:00       AUTO_SPACE_ADVISOR_JOB    SUCCEEDED

Les vues DBA_SCHEDULER_JOBS et DBA_SCHEDULER_PROGRAMS

Tous les jobs systèmes Oracle 10g créés automatiquement sont associés à des programmes. Une jointure entre DBA_SCHEDULER_JOBS et DBA_SCHEDULER_PROGRAMS permet de retrouver les noms des programmes et les traitements (procédures, fonctions...) attachés à ces programmes :

select a.JOB_NAME,
             a.PROGRAM_NAME,
             b.PROGRAM_TYPE,
             b.PROGRAM_ACTION,
             b.NUMBER_OF_ARGUMENTS
          from DBA_SCHEDULER_JOBS a, 
                  DBA_SCHEDULER_PROGRAMS b
          where a.PROGRAM_NAME = b.PROGRAM_NAME
JOB_NAME                PROGRAM_NAME             PROGRAM_TYPE        PROGRAM_ACTION                              NUMBER_OF_ARGUMENTS
----------------------  -----------------------  -----------------   ----------------------------------------    -------------------
AUTO_SPACE_ADVISOR_JOB  AUTO_SPACE_ADVISOR_PROG  STORED_PROCEDURE    dbms_space.auto_space_advisor_job_proc      0
GATHER_STATS_JOB        GATHER_STATS_PROG        STORED_PROCEDURE    dbms_stats.gather_database_stats_job_proc   0
PURGE_LOG               PURGE_LOG_PROG           STORED_PROCEDURE    dbms_scheduler.auto_purge                   0

Lorsque des arguments sont donnés aux programmes (NUMBER_OF_ARGUMENTS > 0), la vue DBA_SCHEDULER_PROGRAM_ARGS permet de retrouver ces arguments et leurs valeurs

Les jobs systèmes cachés AUTO_SPACE_ADVISOR_JOB et GATHER_STATS_JOB

2 jobs systèmes qui requièrent une attention toute particulière sont créés avec Oracle 10gR2 avec des fenêtres de programmation différentes :

  • GATHER_STATS_JOB
  • AUTO_SPACE_ADVISOR_JOB

L'interrogation SQL ci-dessous des vues dba_scheduler_jobs, dba_scheduler_wingroup_members et dba_scheduler_windows retourne la programmation de ces jobs :

SELECT
   a.job_name,
   a.enabled,
   c.window_name,
   c.schedule_name,
   c.start_date,
   c.repeat_interval
FROM
   dba_scheduler_jobs             a,
   dba_scheduler_wingroup_members b,
   dba_scheduler_windows          c
WHERE
   job_name in ('GATHER_STATS_JOB','AUTO_SPACE_ADVISOR_JOB')
   and
     a.schedule_name=b.window_group_name
   and
     b.window_name=c.window_name;
JOB_NAME                       ENABL WINDOW_NAME
------------------------------ ----- ------------------------------
SCHEDULE_NAME
--------------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
--------------------------------------------------------------------------------
GATHER_STATS_JOB               TRUE  WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

GATHER_STATS_JOB               TRUE  WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

AUTO_SPACE_ADVISOR_JOB         TRUE  WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

AUTO_SPACE_ADVISOR_JOB         TRUE  WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

Pour chacun des 2 jobs systèmes créés, 2 programmations sont réalisées (fenêtre de temps)

  • À 22h00 les lundi, mardi, merc-redi, jeudi et vendredi
  • À 00h00 les samedi

Le job AUTO_SPACE_ADVISOR_JOB

La vue DBA_SCHEDULER_PROGRAMS indique que le job AUTO_SPACE_ADVISOR_JOB déclenche la procédure stockée DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC qui ne prend aucun argument en entrée.

DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC déclenche le conseiller de la gestion automatique des segments (Automatic Segment Advisor), conseiller qui identifie les segments présentant de la place à récupérer et donne des recommandations à consulter avec Enterprise Manager ou les vues DBA_AUTO_SEGADV_SUMMARY et DBA_AUTO_SEGADV_CTL.

Si le lancement manuel du conseiller de la gestion automatique des segments est préféré au déclenchement automatique par DBMS_SCHEDULER, ce job est désactivé avec la procédure disable du package DBMS_SCHEDULER :

execute dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB')

Le job GATHER_STATS_JOB

Pour ce qui concerne le job GATHER_STATS_JOB, la vue DBA_SCHEDULER_PROGRAMS indique qu'il s'agit de la procédure stockée DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC qui est déclenchée, celle-ci ne prend aucun paramètre également.

Le job GATHER_STATS_JOB se base une procédure interne totalement non documentée DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC et il priorise la collecte des statistiques sur les tables qui en ont le plus besoin et plus particulièrement les tables sans statistiques ou avec des statistiques anciennes. Ainsi les tables hautement volatiles et les tables de chargement peuvent voir leurs statistiques collectées en priorité dans ce job alors que le nombre de lignes n'est pas pas représentatif.

Des études techniques montrent que la procédure GATHER_DATABASE_STATS_JOB_PROC semble se baser sur les paramètres par défaut METHOD_OPT, GRANULARITY, DEGREE, CASCADE, ESTIMATE_PERCENT du package DBMS_STATS avec recréation des statistiques, paramètres par défauts qui ne sont pas adaptés pour toutes les tables.

select dbms_stats.get_param('CASCADE') as cascade,
            dbms_stats.get_param('DEGREE') as degree,
            dbms_stats.get_param('ESTIMATE_PERCENT') as estimate_percent,
            dbms_stats.get_param('METHOD_OPT') as method_opt,
            dbms_stats.get_param('GRANULARITY') as granularity
         from dual;
CASCADE                  DEGREE  ESTIMATE_PERCENT              METHOD_OPT                  GRANULARITY
-----------------------  ------  ---------------------------   -------------------------   -----------
DBMS_STATS.AUTO_CASCADE  NULL    DBMS_STATS.AUTO_SAMPLE_SIZE   FOR ALL COLUMNS SIZE AUTO   AUTO
Il est recommandé par tous les experts de désactiver ce job très dangereux avec la procédure disable du package dbms_scheduler et de conserver ses méthodes maîtrisées de calcul des statistiques en fonction de l'environnement et du comportement des tables.
execute dbms_scheduler.disable('GATHER_STATS_JOB')

Pour vérifier que le job GATHER_STATS_JOB est bien désactivé :

select job_name, enabled from dba_scheduler_jobs
    where job_name='GATHER_STATS_JOB'
JOB_NAME                       ENABL
------------------------------ -----
GATHER_STATS_JOB               FALSE