Avec une base de données d’une taille importante (à partir de quelques Teraoctets ) contenant de nombreuses tables partitionnées sur lesquelles un calcul de statistiques incrémentales à été configuré, il peut arriver que le job de mise à jour des statistiques ne parvienne plus à analyser tous les objets devant l’être dans la fenêtre d’exécution quotidienne.
Ceci peut avoir de multiples causes, dont :
- Recalcul intempestif des statistiques de toutes les partitions d’une grosse table dans le calcul d’un nouvel histogramme ou de trop de partitions dont les statistiques sont obsolètes
- La mise à jour des « ‘synopsis » des tables partitionnées contenant un grand nombre de partitions est (très) longue
- Quelques bugs liés aux statistiques incrémentales en 11gR2, …
- La purge quotidienne des statistiques au delà de la rétention définie n’aboutit plus
On voit que toutes ces causes peuvent conduire rapidement à une situation de blocage pouvant mettre en danger la production (mauvaise estimation des cardinalités, plans d’exécution non adaptés, dérive des temps des requêtes, …).
Il est donc important de vérifier régulièrement que les travaux automatiques de maintenance dont le calcul des statistiques ainsi que la purge de celles-ci aboutissent.
Contrôle de la rétention de statistiques
La rétention par défaut des statistiques est de 31 jours. Afin de limiter la taille du tablespace SYSAUX dans lequel sont stockées les tables d’historique, la base qui sert d’exemple ici a été configurée avec une rétention de 10 jours.
select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ————————— 10
Les statistiques sont donc disponibles (et peuvent être restaurées ) jusqu’à sysdate -10 :
SELECT dbms_stats.get_stats_history_availability FROM dual; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 09-SEP-18 01.29.26.520516000 AM +02:00
La rétention peut être modifiée en utilisant la procédure DBMS_STATS.ALTER_STATS_HISTORY_RETENTION, par exemple pour porter la rétention à 20 jours :
exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(20);
Une fenêtre de maintenance de 4 heures est ouverte tous les soirs de la semaine à 22h par défaut. Le week-end, la durée de celle fenêtre est portée à 20 h à partir de 6h du matin.
Dans le cas notre base, la fenêtre de maintenance en semaine est de 8h.
Statut des jobs de calcul des statistiques
Il faut s’assurer que le job de calcul des statistiques se termine correctement en requêtant la vue dba_autotask_job_history :
SELECT * FROM dba_autotask_job_history WHERE client_name LIKE '%stats%' AND window_start_time >= SYSDATE - 10 ORDER BY window_start_time DESC
Comme le montre le résultat (partiel) ci-dessous, les jobs de la fenêtre du vendredi et samedi se sont terminés correctement JOB_STATUS=SUCCEEDED alors que ceux du lundi et du mardi ont échoué après 8h de traitement.
CLIENT_NAME WINDOW_NAME WINDOW_START_TIME WINDOW_DURATION JOB_NAME JOB_STATUS JOB_START_TIME JOB_DURATION JOB_ERROR JOB_INFO auto optimizer stats collection SATURDAY_WINDOW 2018-09-22 06:00:00.054689 2:00 0 19:59:59.98621 ORA$AT_OS_OPT_SY_13606 2018-09-22 14:00:35.399575 Europe/Vienna 0 0:59:9.0 0 auto optimizer stats collection FRIDAY_WINDOW 2018-09-21 18:00:00.12726 2:00 0 7:59:59.909034 ORA$AT_OS_OPT_SY_13658 SUCCEEDED 2018-09-21 18:00:01.259348 Europe/Vienna 0 6:37:22.0 0 auto optimizer stats collection TUESDAY_WINDOW 2018-09-18 18:00:00.06091 2:00 0 7:59:59.974535 ORA$AT_OS_OPT_SY_13616 STOPPED 2018-09-18 18:00:02.190845 Europe/Vienna 0 7:59:59.0 0 REASON="Stop job called because associated window was closed" auto optimizer stats collection MONDAY_WINDOW 2018-09-17 18:00:00.033554 2:00 0 8:0:0.005272 ORA$AT_OS_OPT_SY_13600 STOPPED 2018-09-17 18:00:06.434309 Europe/Vienna 0 7:59:56.0 0 REASON="Stop job called because a
Dans le cas où les travaux pendant les fenêtres de maintenance du week-end aboutissent mais celles de la semaine ne se terminent pas systématiquement, il faut commencer par la solution la plus simple, c’est à dire d’augmenter la durée de la fenêtre de maintenance quotidienne et même de la décaler dans un créneau d’activité moins soutenue (en dehors de la sauvegarde ou des batchs notamment).
Les travaux de calcul des statistiques pendant les fenêtres de maintenance comportent une étape de purge, il faut s’assurer que celle-ci s’exécute correctement, c’est à dire qu’elle n’accumule pas du retard de jour en jour.
Efficacité de la purge de statistiques
La requête ci-dessous permet d’afficher le nombre de jours à purger par rapport à la date des statistiques les plus anciennes et la durée de rétention configurée, ceci pour les 4 tables principales qui historisent ces statistiques.
Lorsque la purge fonctionne de manière optimale, le nombre de jours à purger doit être égal à zéro.
SELECT Min_SavTime, table_name, (CASE WHEN TRUNC(dbms_stats.get_stats_history_availability) <= TRUNC(Min_SavTime) THEN 0 ELSE TRUNC(dbms_stats.get_stats_history_availability) - TRUNC(Min_SavTime) END) Nbj_to_purge, TRUNC(dbms_stats.get_stats_history_availability) AS history_availability FROM (SELECT MIN(savtime) AS Min_SavTime, 'WRI$_OPTSTAT_AUX_HISTORY' as Table_Name FROM sys.WRI$_OPTSTAT_AUX_HISTORY UNION SELECT MIN(savtime), 'WRI$_OPTSTAT_HISTGRM_HISTORY' FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY UNION SELECT MIN(savtime), 'WRI$_OPTSTAT_HISTHEAD_HISTORY' FROM sys.WRI$_OPTSTAT_HISTHEAD_HISTORY UNION SELECT MIN(savtime), 'WRI$_OPTSTAT_TAB_HISTORY' FROM sys.WRI$_OPTSTAT_TAB_HISTORY ) ORDER BY 1, 2;
On peut voir dans l’exécution du 10 septembre ci-dessous qu’il y a un (gros) problème :
Il y a 41 jours de retard de statistiques non purgées dans la table WRI$_OPTSTAT_HISTGRM_HISTORY et 27 jours dans la table WRI$_OPTSTAT_HISTHEAD_HISTORY.
Dans notre cas, la table WRI$_OPTSTAT_HISTGRM_HISTORY atteignait ainsi une taille de plus de 30 Go et ses 2 indexes près de 10 Go et 20 Go.
ès History
MIN_SAVTIME TABLE_NAME NBJ_TO_PURGE Availability
---------------------------------------- ----------------------------------- ------------ -----------------
31-JUL-18 10.28.01.539697 PM +02:00 WRI$_OPTSTAT_HISTGRM_HISTORY 41 10-SEP-18
14-AUG-18 09.45.05.167335 AM +02:00 WRI$_OPTSTAT_HISTHEAD_HISTORY 27 10-SEP-18
10-SEP-18 01.43.08.147187 AM +02:00 WRI$_OPTSTAT_TAB_HISTORY 0 10-SEP-18
WRI$_OPTSTAT_AUX_HISTORY 10-SEP-18
Pour purger ces statistiques obsolètes, il va falloir les nettoyer en appelant manuellement la procédure DBMS_STATS.PURGE_STATS qui est celle est qui est utilisée par le job automatique.
L’idéal est de purger depuis la date la plus ancienne par paquets de quelques jours avec la procédure suivante :
ALTER SESSION SET "_optimizer_purge_stats_iteration_row_count"=100000; begin for i in reverse 35..40 loop dbms_stats.purge_stats(trunc(sysdate) - i); end loop; end; /
La première exécution lancée sur 5 jours à purger a duré plus de 2 heures.
On relance ensuite la purge jusqu’à ce qu’il n’y ait plus de retard : Nb jours à purger = 0
La purge exécute en base de données des DELETE qui accèdent par les indexes aux tables WRI$_OPTSTAT_HISTGRM_HISTORY et WRI$_OPTSTAT_HISTHEAD_HISTORY principalement.
Comme les indexes de ces tables sont devenus fragmentés et volumineux au fil du temps, il intéressant de les reconstruire après la 1ère série de jours purgés afin d’accélérer les ordres delete qui suivront.
alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild; alter index SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild; alter index SYS.I_WRI$_OPTSTAT_H_ST rebuild;
De plus, on peut augmenter notablement la vitesse de purge en jouant la valeur du paramètre _optimizer_purge_stats_iteration_row_count , au niveau de la session de préférence, mais il est possible de le modifier par un alter system pour qu’il soit pris en compte par la purge automatique (à utiliser avec précaution en production).
Avant de lancer la purge, on pourra donc modifier le paramètre comme ci-dessous :
alter session set _optimizer_purge_stats_iteration_row_count=100000
Celui-ci a une valeur de 10.000 par défaut, il spécifie le nombre de lignes purgées à chaque itération (je suis monté à 1 million sans problèmes).
Il faut savoir que lorsque cette procédure est appelée par le job automatique, elle possède une fenêtre d’exécution de 5 minutes par itération, ce qui contribue à accumuler le retard lorsque celle-ci deviennent trop longues, d’où l’intérêt de suivre le bon fonctionnement de ces opérations de maintenance. En exécution manuelle, il n’y a pas, par contre, de limite de temps à une itération ce qui permet de mener la purge à son terme.
A la fin de la purge, il est préférable de réorganiser les deux tables WRI$_OPTSTAT_HISTGRM_HISTORY et WRI$_OPTSTAT_HISTHEAD_HISTORY par un move puis rebuild des indexes.
alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace SYSAUX; alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX; alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild; alter index SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild; alter index SYS.I_WRI$_OPTSTAT_H_ST rebuild;
La table WRI$_OPTSTAT_HISTGRM_HISTORY est ainsi passée de 30 Go à 2,5 Go et les indexes à quelques centaines de Mo pour 10 jours d’historique conservé.
Si, malgré toutes ces attentions, vos jobs de statistiques ne se terminent toujours pas, il faudra aller regarder plus en détail sur quel objets ils passent du temps.
