Les parties I et II de cet article décrivent l’intérêt des statistiques et la manière de les collecter en se focalisant sur le Job automatique Oracle. Cette partie III complète ces informations avec des concepts plus avancés :

Limiter la consommation de ressources du Job automatique

La fonctionnalité Ressource Manager (non disponible en Oracle Standard Edition)  fait partie intégrante de la collecte des statistiques. Le lien entre Resource Manager et la collecte des statistiques est effectué au niveau de l’objet fenêtre (WINDOW) vu dans la partie II.

En effet, chaque fenêtre est associée par défaut au plan de de consommation DEFAULT_MAINTENANCE_PLAN. A chaque fois qu’une fenêtre s’ouvre, ce plan de ressource est automatiquement activé.

Ce plan de ressources a pour but de préserver la majorité des ressources CPU à l’application, ce qui permet de collecter les statistiques sur les systèmes fonctionnant en 24/7 sans trop d’impact.

3 stratégies s’offrent au DBA :

1. Ne rien faire de plus (cela convient à la plupart des bases)
2. Modifier les caractéristiques du plan de consommation DEFAULT_MAINTENANCE_PLAN
3. Définir son propre plan de ressources et l’associer aux fenêtres

La gestion des plans de ressource avec Resource Manager sort du cadre de cet article. En deux mots, il est possible de limiter la consommation Cpu d’un Job ou le degré de parallélisme des requêtes. Resource Manager peut être configuré avec Enterprise Manager.

 

Quelles statistiques sur quels objets  ?

La collecte des statistiques peut se faire à différents niveaux :

Statistiques sur les tables et les indexes applicatifs

Comment ?

• Collecte automatique : auto optimizer stats collection
• Collecte manuelle : gather_table_stats, gather_schema_stats, gather_database_stats

Nécessaires ?

Oui !

Quand ?

Quand le seuil de 10% de modifications est dépassé

Méthode conseillée

Collecte automatique conseillée, collecte manuelle uniquement dans certains cas (après un chargement massif d’une table par exemple).

 

Statistiques sur le dictionnaire

Comment ?

• Collecte manuelle : gather_dictionary_stats

Nécessaires ?

Oui, surtout lors avant un Export au niveau schéma ou base et obligatoire avant un upgrade. Penser à les calculer si on se prépare à faire des accès massifs aux Meta-Données.

Quand ?

A faire lorsque le dictionnaire est « plein » donc après la création de tous les objets de la base (tables,  vues, privilèges, packages, etc …).

Ne pas collecter de façon régulière mais uniquement en cas de changement important des données du dictionnaire (nombreuses définitions de tables, vues, packages ajoutés, supprimées ou modifiées).

Méthode conseillée

• Procédure gather_dictionary_stats
• Attention, la procedure gather_schema_stats(‘SYS’) n’est pas équivalente car elle ne collecte des statistiques que sur le schéma SYS alors que gather_dictionary_stats va au delà des objets de SYS (le schéma SYSTEM est concerné aussi).

Statistiques sur les vues dynamiques (V$)

Comment ?

• Collecte manuelle : gather_fixed_stats

Quand ?

A faire lorsque les vues dynamiques sont « pleines » donc lorsque l’instance est en activité et si possible une activité représentative de la charge habituelle (avec un nombre sessions connectées et actives important).

Nécessaires ?

Oui, la collecte des statistiques sur les V$ peut paraitre étonnante mais il faut savoir qu’elle concerne principalement les tables X$ sur lesquelles s’appuient les vues V$ et que ces vues V$ sont parfois composées de SELECT complexes (jointures, aggrégats, etc …) à partir des X$. Une absence de statistiques sur les objets X$ peut conduire à des accès lents ou consommateurs sur les V$. Des outils comme Enterprise Manager ou Toad accèdent de manière fréquente aux V$ et une absence de statistiques sur ces vues peut entrainer des surconsommation CPU  lors d’un simple clic dans ces outils !

Les statistiques sur les V$ ont pris tout leur sens depuis que l’optimiseur Oracle prend en compte le coût Cpu. Car bien évidemment, il n’y a généralement pas d’accès disques avec les V$ (à quelques exceptions près comme les vues qui regardent le controlfile et notamment le référentiel Rman). Mais il peut y avoir beaucoup de Cpu consommé en fonction du plan d’exécution choisi, et Oracle recherche le plan qui minimise la consommation Cpu.

En général les DBAs ne collectent pas les statistiques sur les V$, sauf s’ils rencontrent un problème mais cela devrait être fait de manière proactive.

Méthode conseillée

gather_fixed_stats

 

Statistiques système

Pourquoi ?

Ces statistiques feront l’objet d’un article spécifique. Elles sont très importantes car elles permettent à l’optimiseur de tirer profit au mieux du hardware en adaptant les plans d’exécution à ses caractéristiques.

Son pris en compte en particulier :

• La vitesse des Cpus (on prend en compte la vitesse d’un Core sans cumul des Cores)
• Les temps d’accès disques pour des lectures d’un block ou de N blocks
• La vitesse de transfert des disques

Si ces statistiques ne sont pas collectées, Oracle doit se contenter de statistiques dites NOWORKLOAD qui sont calculées lors de l’installation d’Oracle. Ces statistiques sont à considérer comme insuffisantes. Les statistiques en mode WORKLOAD sont nécessaires.

Comment ?

• Collecte manuelle : gather_system_stats

Quand ?

A faire sur une instance chargée (mode WORKLOAD).

Nécessaires ?

Oui

Méthode conseillée

gather_system_stats  sur une instance à pleine charge et sur une durée significative (60 minutes ou plus).

 

Dans la dernière partie IV de cet article nous allons voir comment l’outil  Lighty distribué par Setra permet une surveillance des statistiques.