Optimisation de la zone de partage dans la SGA

Logo

SGA et Shared Pool

Rappel de la structure de la SGA :

Structure SGA Oracle

Contenu de la zone partagée (Shared Pool)

La shared pool (ou zone partagée) contient deux structures principales et une secondaire :

  • le library cache, qui stocke le SQL et le PL/SQL partagés
  • le cache de dictionnaire de données ou dictionary cache, qui stocke les informations sur les objets du dictionnaire
  • la zone utilisateur globale (UGA) qui stocke les informations sur les connexions multithread.

Optimisation de la SGA

Une absence de données dans le cache du dictionnaire de données ou dans le library cache est plus coûteuse que dans le buffer cache des données. Par conséquent, l’optimisation de la shared pool est une priorité.

Lorsque vous optimisez la shared pool, vous serez surtout préoccupé par le library cache : l’algorithme d’Oracle maintient les données du dictionnaire en mémoire plus longtemps que les données du library cache. Par conséquent, l’optimisation du library cache à un niveau acceptable de hit ratio garantit également un hit ratio acceptable dans le cache du dictionnaire de données.

La zone partagée (Shared Pool)

La taille de la shared pool est définie à l’aide du paramètre SHARED_POOL_SIZE dans le fichier init.ora. La valeur par défaut est de 3 500 000 octets (3,5 Mo).

Le library cache (zone LC)

Le library cache contient les zones SQL et PL/SQL partagées, lesquelles sont des représentations entièrement analysées ou compilées des blocs PL/SQL et des ordres SQL.

Les blocs PL/SQL comprennent :

  • les procédures
  • les triggers
  • les fonctions
  • les packages
  • les blocs PL/SQL anonymes
  • les classes Java

Le cache du dictionnaires de données (zone DC)

Le cache du dictionnaire de données contient les définitions des objets du dictionnaire en mémoire, notamment la structure et la sécurité de tous les objets inclus dans les instructions SQL récemment utilisées.

Le Library cache

Généralités

Le library cache est utilisé pour stocker les ordres SQL et les blocs PL/SQL à partager entre utilisateurs, ce dernier est géré par un algorithme LRU (Least Recently Used).

Le library cache évite les nouvelles analyses d’ordre.

Si un utilisateur effectue un ordre déjà mis en cache, le serveur Oracle peut utiliser la version mise en cache sans avoir à l’analyser de nouveau.

Pour savoir si un ordre est déjà mis en cache, le serveur Oracle :

  • réduit l’ordre à la valeur numérique du texte ASCII
  • utilise une fonction hash de ce chiffre

Optimisation du library cache

Premier objectif d’optimisation : réduire les absences de données en minimisant l’analyse

  • si une application fait un appel d’analyse pour un ordre SQL alors que la représentation analysée de l’ordre n’existe pas dans une zone SQL partagée du library cache, le serveur Oracle analyse l’ordre et alloue une zone SQL partagée. Les ordres SQL peuvent partager une zone SQL partagée en utilisant du code générique et des bind variables plutôt que des constantes.
  • si une application fait un appel d’exécution pour un ordre SQL alors que la zone SQL partagée concernant la représentation analysée de l’ordre a été libérée du library cache pour faire place à un autre ordre, le serveur Oracle analyse de nouveau l’ordre implicitement, lui alloue une nouvelle zone SQL partagée et l’exécute. Il faut réduire les absences de données dans le library cache lors d’un appel d’exécution en allouant plus de mémoire au library cache.
  • si l’objet d’un schéma est référencé dans un ordre SQL et qu’on le modifie par la suite, la zone SQL partagée devient invalide.

Second objectif d’optimisation : éviter la fragmentation

  • en assurant suffisamment d’espace contigu pour les besoins importants en mémoire via l’allocation d’espace réservé dans la zone shared pool.
  • en maintenant en mémoire les objets volumineux les plus fréquemment demandés tels que les zones SQL et PL/SQL, au lieu de les exclure par le mécanisme LRU habituel.
  • en utilisant de petites fonctions PL/SQL au lieu de larges blocs PL/SQL anonymes.

Terminologie

Trois mots clés pour le Library Cache :

GETS : chaque ligne de la vue V$LIBRARYCACHE contient des statistiques sur un type d’élément du library cache. L’élément décrit à chaque ligne est identifié par la valeur de la colonne NAMESPACE. Les lignes de la table avec les valeurs NAMESPACE suivantes, reflètent l’activité du library cache pour les ordres SQL et les blocs PL/SQL :

SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER.

Les lignes avec d’autres valeurs NAMESPACE reflètent l’activité du library cache pour des définitions d’objet utilisés par Oracle pour la gestion de la dépendance :

INDEX, CLUSTER, OBJECT, PIPE

PINS : pour chacune de ces zones, le nombre d’exécutions des ordres SQL, ou des procédures, est comptabilisé.

RELOADS : si l’appel à l’exécution d’un ordre SQL a lieu et que la zone SQL partagée contenant la représentation analysée de l’ordre a été libérée du library cache pour faire de la place à un autre ordre ou que des objets auxquels l’ordre fait référence ont été invalidés, le serveur Oracle recharge implicitement l’ordre et par conséquent l’analyse de nouveau. Le nombre de rechargements est comptabilisé pour chacun de ces objets.

Outils de diagnostic pour l’optimisation du library cache

La shared pool se comportant comme un cache, rien n’y est exclu tant qu’il reste de la mémoire disponible. La mémoire libre peut de façon plus appropriée être appelée « espace gaspillé ». Une valeur élevée de mémoire libre est plutôt un symptôme de fragmentation.

Plusieurs vues dynamiques permettent de monitorer la SGA et en particulier la library cache :

  • V$SGASTAT pour la SGA

Plus particulièrement pour la library cache :

  • V$LIBRARYCACHE
  • V$SQLAREA
  • V$SQLTEXT
  • V$DB_OBJECT_CACHE

V$SGASTAT : taille des structures SGA

select * from v$sgastat;

POOL        NAME                       BYTES
----------- -------------------------- ----------
shared pool free memory                   2960692
shared pool sessions                       366520
shared pool dictionary cache               281840
shared pool library cache                  787532
shared pool sql area                      1606036
Statistiques SGA
V$SQLAREA statistiques complètes sur tous les curseurs partagés et les 1000 premiers caractères de l’ordre SQL.
V$SQLTEXT texte SQL complet sans troncature et sur plusieurs lignes.
V$DB_OBJECT_CACHE objets de base de données mis en cache, y compris les packages ; de même que les objets tels que les tables et synonymes, lorsqu’ils sont référencés dans les ordres SQL.
V$LIBRARYCACHE statistiques sur la gestion du library cache.

Partage des curseurs

Il est possible de déterminer si les objets sont partagés avec la statistique GETHITRATIO dans la vue V$LIBRARYCACHE :

select namespace, gethitratio from v$librarycache;

NAMESPACE       GETHITRATIO
--------------- -----------
SQL AREA         ,899357602
TABLE/PROCEDURE   ,66894198
BODY                   ,125 -> PACKAGE/FONCTION
TRIGGER                   1
INDEX                     0
CLUSTER              ,96875
OBJECT                    1
PIPE                      1

Le gethitratio détermine le pourcentage d’appels d’analyse qui trouvent déjà un point d’entrée ou curseur dans la shared pool (GETHITS/GETS). Ce rapport doit avoisiner les 90 %. Dans le cas contraire, le code applicatif peut probablement être amélioré (sensitivité dans la casse des requêtes SQL, bind des variables aux requêtes SQL…).

Loads dans le Library cache

Envisageons le chargement dans la library cache de l’exécution d’une procédure stockée PROC1 selon la cinématique ci-dessous :

Execute PROC1 => 1er pin, 1 chargement

Execute PROC1 => 2nd pin, pas de rechargement

Execute PROC1 => 3ème pin, pas de rechargement

Execute PROC1 => 4ème pin, pas de rechargement

Pour les 4 exécutions de la procédure PROC1, 4 pins et 0 rechargement.

Dans l’idéal, les rechargements ou reloads doivent valoir 0 ou au pire jamais plus de 1% des pins.

La vue V$LIBRARYCACHE permet de connaître exactement les ordres déjà analysés mais pour lesquels les analyses ont été exclues par manque d’espace mémoire dans la library cache. Le nombre de RELOADS ne doit pas être supérieur à 1% du nombre de PINS.

select  sum(pins) "Executions",
        sum(reloads) "Rechargements",
        sum(reloads)/sum(pins)
from    v$librarycache

Executions Rechargements SUM(RELOADS)/SUM(PINS)
---------- ------------- ----------------------
8195                  11             ,001342282 < 1
select  namespace, pinhitratio, reloads
from    v$librarycache;

NAMESPACE       PINHITRATIO RELOADS
--------------- ----------- ----------
SQL AREA          ,95900927 11
TABLE/PROCEDURE  ,612579763 0
BODY                     ,5 0
TRIGGER                   1 0
INDEX                     0 0
CLUSTER          ,977375566 0
OBJECT                    1 0
PIPE                      1 0

Si le rapport reloads-to-pins est supérieur à 1%, deux raisons sont possibles :

  • des zones d’analyse partagées ont été exclues par manque d’espace mémoire bien que requises par des réexécutions successives.
  • des zones d’analyse partagées sont rendues invalides.

Pour éviter des rechargements fréquents, augmenter le paramètre SHARED_POOL_SIZE dans le fichier init.ora.

Invalidations

La colonne invalidations dans la vue V$LIBRARYCACHE représente le nombre de fois où les objets de la colonne namespace ont été marqués invalides, provoquant des rechargements.

select namespace, pins, reloads, invalidations from v$librarycache;

NAMESPACE       PINS       RELOADS    INVALIDATIONS
--------------- ---------- ---------- -------------
SQL AREA              7579         11             0
TABLE/PROCEDURE       1112          0             0
...

Lorsque l’objet d’un schéma est référencé dans un ordre SQL et qu’il fait l’objet de modifications ultérieures, la zone SQL partagée est invalidée (marquée invalide) et l’ordre doit être de nouveau analysé à sa prochaine exécution, par conséquent rechargé.

Une invalidation se produit par exemple lors de la création, de la suppression, de la modification d’une table, d’une vue, d’un synonyme ou lors d’une nouvelle compilation de spécification de procédure ou de package : toutes les zones SQL dépendantes sont invalidées. C’est également le cas lors des analyses de statistiques.

Dimensionnement du Library Cache

Allocation d’espace global

Pour une application existante, le test consiste à utiliser les vues dynamiques pour connaître la quantité de mémoire utilisée. Il suffit de commencer par appliquer une très grande valeur au paramètre SHARED_POOL_SIZE (aux dépens d’autres structures si nécessaire), exécuter ensuite l’application.

Pour calculer la mémoire utilisée partageable :

  • pour des objets stockés tels que les packages, fonctions et procédures, utiliser la requête ci-dessous : 
    select  sum(sharable_mem)
from    v$db_object_cache
where   type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');

SUM(SHARABLE_MEM)
-----------------
443421
  • pour des ordres SQL, interroger V$SQLAREA après une certaine période d’exécution de l’application. Pour des ordres fréquemment émis, utiliser la requête ci-dessous bien que celle-ci ne prenne pas en compte le SQL dynamique : 
    select  sum(sharable_mem)
from    v$sqlarea
where   executions > 5;

SUM(SHARABLE_MEM)
-----------------
1557221
  • il est nécessaire de prévoir 250 octets par utilisateur et par curseur partagé dans la shared pool. Ceci peut être testé aux heures de forte activité avec la requête ci-dessous : 
    select  sum(250 * users_opening)
from    v$sqlarea;

SUM(250*USERS_OPENING)
----------------------
7000

    Dans un environnement de test, la mesure peut être effectuée en sélectionnant le nombre de curseurs ouverts pour un utilisateur de test. Le résultat est alors multiplié par le nombre d’utilisateurs attendus.

    select  250 * value bytes_per_user
from    v$sesstat s, v$statname n
where   n.statistic# = s.statistic#
and     n.name='opened cursors current'
and     s.sid=18;

BYTES_PER_USER
--------------
7250

Dans le meilleur des cas, l’application doit disposer d’un library cache aussi grand que la somme des trois valeurs ci-dessus, plus une petite allocation pour le SQL dynamique.

Shared Pool réservée : besoins importants en mémoire

Il est possible de réserver de l’espace dans la shared pool pour les besoins importants en mémoire afin

  • de satisfaire les requêtes d’obtention de larges portions de mémoire contigues.
  • réserver de la mémoire non fragmentée dans la shared pool.

Le DBA peut réserver de la mémoire dans la shared pool pour satisfaire les allocations importantes au cours d’opérations telles que la compilation de PL/SQL ou de trigger. Des objets plus petits ne fragmentent pas la liste réservée, aidant ainsi à garantir la présence de larges portions de mémoire contigues dans la liste réservée. Une fois que la mémoire allouée à partir de la liste réservée est libre, la zone réservée de mémoire dans la shared pool retourne dans la liste réservée.

Zones réservées SGA

La taille de la liste réservée, de même que la taille minimum des objets pouvant être alloués à partir de la liste réservée, est contrôlée par deux paramètres d’initialisation :

SHARED_POOL_RESERVED_SIZE : contrôle la quantité de SHARED_POOL_SIZE réservée aux allocations importantes (établissez la valeur initiale à 10% de la SHARED_POOL_SIZE).

SHARED_POOL_RESERVED_MIN_ALLOC : contrôle l’allocation de la mémoire réservée (pour créer une liste réservée, SHARED_POOL_RESERVED_SIZE doit être supérieure à SHARED_POOL_RESERVED_MIN_ALLOC. Seules les allocations supérieures à SHARED_POOL_RESERVED_MIN_ALLOC peuvent allouer de l’espace à partir de la liste réservée si une portion de mémoire suffisante n’est pas trouvée dans les listes de blocs libres de la shared pool. Dans la plupart des cas, la valeur par défaut convient).

La vue V$SHARED_POOL_RESERVED aide à optimiser l’espace et le pool réservé de la shared pool.

Les colonnes de la vue ne sont valides que si le paramètre SHARED_POOL_RESERVED_SIZE est établi à une valeur valide.

desc v$shared_pool_reserved;

Nom                                       NULL ?   Type
----------------------------------------- -------- ----------------------------
FREE_SPACE                                          NUMBER
AVG_FREE_SIZE                                       NUMBER
FREE_COUNT                                          NUMBER
MAX_FREE_SIZE                                       NUMBER
USED_SPACE                                          NUMBER
AVG_USED_SIZE                                       NUMBER
USED_COUNT                                          NUMBER
MAX_USED_SIZE                                       NUMBER
REQUESTS                                            NUMBER
REQUEST_MISSES                                      NUMBER
LAST_MISS_SIZE                                      NUMBER
MAX_MISS_SIZE                                       NUMBER
REQUEST_FAILURES                                    NUMBER
LAST_FAILURE_SIZE                                   NUMBER
ABORTED_REQUEST_THRESHOLD                           NUMBER
ABORTED_REQUESTS                                    NUMBER
LAST_ABORTED_SIZE                                   NUMBER

où :

FREE SPACE Espace libre total dans la liste réservée.
AVG_FREE_SIZE Taille moyenne de la mémoire libre de la liste réservée
MAX_FREE_SIZE Taille de la plus grande portion de mémoire libre de la liste réservée
REQUEST_MISSES Nombre de fois où la liste réservée a manqué de mémoire libre pour satisfaire la requête et a démarré l’écriture d’objets à partir de la liste LRU.

Les colonnes suivantes de la vue contiennent des valeurs valides même si le paramètre n’est pas établi :

REQUEST_FAILURES, LAST_FAILURE_SIZE, ABORTED_REQUEST_THRESHOLD, ABORTED_REQUESTS, LAST_ABORTED_SIZE

où :

REQUEST_FAILURES Nombre de fois où aucune mémoire n’a été trouvée pour satisfaire une requête
LAST_FAILURE_SIZE Taille de la dernière requête ayant échoué.

Optimisation de l’espace réservé de la shared pool

Diagnostics avec la vue V$SHARED_POOL_RESERVED

Les statistiques à partir de la vue V$SHARED_POOL_RESERVED aident à optimiser les paramètres. Sur un système muni de beaucoup de mémoire libre permettant d’augmenter la SGA, l’objectif est d’avoir REQUEST_MISSES = 0.

Diagnostics avec la procédure ABORTED_REQUEST_THRESHOLD

La procédure ABORTED_REQUEST_THRESHOLD du package DBMS_SHARED_POOL permet de limiter la quantité de shared pool à flusher avant de rapporter l’erreur ORA-4031, afin de limiter le déversement de la shared pool qui pourrait se produire en raison d’un objet volumineux.

Règles d’initialisation des paramètres

Si le système est contraint par la mémoire du système d’exploitation, l’objectif est le suivant :

  • REQUEST_FAILURES = 0 ou n’augmente pas
  • LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC
  • AVG_FREE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

si ni le deuxième, ni le troisième objectif ne sont atteints, augmenter SHARED_POOL_RESERVED_MIN_ALLOC et SHARED_POOL_SIZE de la même quantité.

Règles lorsque SHARED_POOL_RESERVED_SIZE est trop petit

Le pool réservé est trop petit lorsque REQUEST_FAILURES > 0 et au moins une des conditions suivantes est remplie :

  • LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC
  • MAX_FREE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
  • FREE_SPACE < SHARED_POOL_RESERVED_MIN_ALLOC

dans ce cas :

  • augmenter SHARED_POOL_RESERVED_SIZE et SHARED_POOL_SIZE en conséquence ou
  • augmenter SHARED_POOL_RESERVED_MIN_ALLOC (mais il faut peut être augmenter SHARED_POOL_RESERVED_SIZE).
Règles lorsque SHARED_POOL_RESERVED_SIZE est trop grand

Trop de mémoire peut avoir été allouée à la liste réservée si :

  • REQUEST_MISS = 0 ou n’augmente pas
  • FREE_SPACE => 50 % de SHARED_POOL_RESERVED_SIZE au minimum

dans ce cas :

  • diminuer SHARED_POOL_RESERVED_SIZE ou
  • diminuer SHARED_POOL_RESERVED_MIN_ALLOC (si ce n’est pas la valeur par défaut).
Règles lorsque SHARED_POOL_SIZE est trop petit

Ceci peut être le cas si :

  • REQUEST_FAILURES > 0 et augmente
  • LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

dans ce cas :

  • diminuer SHARED_POOL_RESERVED_SIZE ou
  • diminuer SHARED_POOL_RESERVED_MIN_ALLOC (si supérieur à la valeur par défaut).

Conservation d’objets volumineux

Le chargement d’objets volumineux est la première cause de fragmentation. Le temps de réponse des utilisateurs est affecté par le grand nombre de petits objets à exclure de la shared pool pour libérer de l’espace. Pour éviter ces situations, conserver les objets volumineux ou souvent demandés dans la shared pool afin de garantir qu’ils n’y soient jamais exclus par manque d’espace mémoire.

Pour trouver rapidement les objets PL/SQL non conservés dans la library cache :

select  *
from    v$db_object_cache
where   sharable_mem > 10000
and     type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
and     kept='NO';

Les objets à conserver sont :

  • les objets volumineux procéduraux souvent requis comme les packages STANDARD et DIUTIL, ainsi que ceux pour qui la mémoire pouvant être partagée dépasse un seuil défini.
  • les triggers compilés souvent exécutés sur des tables fréquemment utilisées.
  • les séquences, puisque les numéros de séquence sont perdus lorsque la séquence est exclue de la shared pool par manque d’espace mémoire.

Le mieux est de conserver ces objets au démarrage de l’instance, cela évite une fragmentation supplémentaire.

Le "flush" de la shared pool à l’aide de la commande ALTER SYSTEM FLUSH SHARED_POOL n’exclut pas les objets conservés (cette commande est utilisée en cas de blocage de la shared pool).

Le package DBMS_SHARED_POOL et la procédure KEEP permet de conserver des objets dans la shared pool.

Pour créer le package DBMS_SHARED_POOL, exécuter le script dbmspool.sql, ce package n’est pas créé par le script catproc.sql.

La procédure UNKEEP supprime de la shared pool les objets maintenus en mémoire.

execute DBMS_SHARED_POOL.KEEP('package_name') ;

Blocs PL/SQL anonymes volumineux

Il existe deux solutions pour éliminer les blocs PL/SQL anonymes volumineux :

  • identifier ces derniers pour les convertir en petits blocs PL/SQL anonymes qui appellent les fonctions d’un package.
  • si un bloc PL/SQL ne peut être converti en package, il peut être identifié dans V$SQLAREA et marqué comme KEPT avec la procédure DBMS_SHARED_POOL.KEEP('address*.hash_value');

Pour rechercher les blocs PL/SQL anonymes volumineux :

select  sql_text
from    v$sqlarea
where   command_type=47
and     length(sql_text) > 500;

Le cache du dictionnaire de données

Terminologie et optimisation

Deux mots clés pour le cache du dictionnaire de données :

GETS : indique le nombre total de requêtes pour des informations sur l’élément correspondant (par exemple, dans la ligne contenant les statistiques sur les descriptions de fichiers, cette colonne contient le nombre total de requêtes pour des données sur les descriptions de fichiers).

GETMISSES : indique le nombre de requêtes pour des données se soldant par une absence de données dans le cache.

Dans certains cas, des absences de données dans le cache sont à prévoir. Au démarrage d’une instance, le cache du dictionnaire de données est vide, par conséquent, tout ordre SQL émis risque de se solder par une absence de données. Au fur et à mesure que des données sont lues dans le cache, le risque d’absence de données diminue. En fin de compte, la base de données atteint un état stable dans lequel les données du dictionnaire les plus utilisées sont dans le cache. À ce stade, très peu d’absences doivent se produire. Pour optimiser le cache, examiner son activité qu’après l’exécution de l’application.

Outils de diagnostic pour le cache du dictionnaire de données

La vue V$ROWCACHE permet de contrôler le cache du dictionnaire.

SGA Dictionary cache

Les colonnes présentant le plus d’intérêt figurent dans le tableau suivant :

PARAMETERS Catégories des éléments du dictionnaire de données
GETS Requêtes pour des informations sur cette catégorie
GETMISSES Requêtes résultant en absence de données

Il est impossible de dimensionner directement le cache du dictionnaire de données, ce dernier ne peut être modifié qu’à partir du paramètre SHARED_POOL_SIZE. L’algorithme d’allocation de l’espace de la shared pool privilégie le cache du dictionnaire.

Optimisation du cache du dictionnaire de données

Le rapport de la somme de tous les GETMISSES et de la somme de tous les GETS doit être inférieur à 15% au cours d’une exécution normale. S’il est plus élevé, envisager d’augmenter le paramètre SHARED_POOL_SIZE.

Il est impossible d’atteindre la valeur zéro pour GETMISSES, car après le démarrage, la première fois qu’un serveur requiert la définition d’un objet, celle-ci doit être chargée dans le cache.

select parameter, gets, getmisses from v$rowcache;

dc_objects   567 158
dc_synonyms  28   23
dc_sequences  6    3
dc_users      4    3

Résumé

L’optimisation consiste à s’assurer que :

  • le gethitratio sur le library cache est supérieur à 90%.
  • le pinhitratio sur le library cache est inférieur à 1%.
  • le gethitratio sur le cache du dictionnaire est supérieur à 85%.