Optimisation de la zone de partage dans la SGA


1- SGA et Shared Pool

Rappel de la structure de la SGA :

1-1- Contenu de la zone partagée (Shared Pool)

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

1-2- 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.

2- 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 :

Le cache du dictionnaire 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.

3- Le Library cache

3-1- 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 permet d'éviter 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 :

3-2- Optimisation du library cache

Premier objectif d'optimisation

Réduire les absences de données en minimisant l'analyse :

Second objectif d'optimisation

Eviter la fragmentation en :

3-3- Terminologie

Trois mots clés pour le Library Cache :

GETS : chaque ligne de 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.

3-4- 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 :

Plus particulièrement pour la library 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

v$sqlarea : statistiques complètes sur tous les curseurs partagés et les 1000 premiers caractères de l'ordre SQL.

v$sqltext : le 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.

3-5- Partage des curseurs

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

SQL> 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....).

3-6- 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 => 2d 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 :

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

3-7- 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.

SQL> 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.

3-8- Dimensionnement du Library Cache

3-8-1- 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 (au dépens d'autres structures si nécessaire), exécuter ensuite l'application.

Pour calculer la mémoire utilisée partageable :

select  sum(sharable_mem)
from    v$db_object_cache
where   type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');
SUM(SHARABLE_MEM)
-----------------
443421
select  sum(sharable_mem)
from    v$sqlarea
where   executions > 5;
SUM(SHARABLE_MEM)
-----------------
1557221
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.

3-8-2- 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

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.

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 est l'espace libre total dans la liste réservée.
AVG_FREE_SIZE est la taille moyenne de la mémoire libre de la liste réservée
MAX_FREE_SIZE est la taille la plus grande portion de mémoire libre de la liste réservée
REQUEST_MISSES est le 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 est le nombre de fois où aucune mémoire n'a été trouvée pour satisfaire une requête
LAST_FAILURE_SIZE est la taille de la dernière requête ayant échoué.

3-8-3- 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 peuvent aider à 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 dans le 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 système d'exploitation, l'objectif est le suivant :

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 :

dans ce cas :

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 :

dans ce cas :

Règles lorsque SHARED_POOL_SIZE est trop petit

Ceci peut être le cas si :

dans ce cas :

3-8-4-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 :

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

L'écriture de la shared pool à l'aide de la commande ALTER SYSTEM FLUSH SHARED_POOL n'écrit 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, exécuter le script dbmspool.sql, ce package n'est pas créé par le script catproc.sql.

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

execute DBMS_SHARED_POOL.KEEP('package_name') ;

3-8-5- Blocs PL/SQL anonymes volumineux

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

Pour rechercher les blocs PL/SQL anonymes volumineux :

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

4- Le cache du dictionnaire de données

4-1- 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. A 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.

4-2- Outils de diagnostic pour le cache du dictionnaire de données

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

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.

4-3- 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

5- Résumé

L'optimisation consiste à s'assurer que :


Annexe

Historique

Version Date Commentaires
1.0 03/2004 Version initiale

Liens

Oracle9i Database Concepts Release 2 (9.2), Memory Architecture, Shared Pool
Oracle9i Database Concepts Release 2 (9.2), Memory Architecture, The Data Dictionary
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2), Dynamic Performance Views for Tuning, V$LIBRARYCACHE
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2), Dynamic Performance Views for Tuning, V$ROWCACHE
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2), Dynamic Performance Views for Tuning, V$DB_OBJECT_CACHE
Books OnLine Oracle 9i R2
Oracle