Interpréter le contenu du Shared Pool Analyzer
Trucs et Astuces
✓ d.side temps réel
✓ d.side Replay
Objectifs des Analyzers d.side
Les Analyzers de d.side, comme l’écran principal de d.side, ont pour fonction de détecter si l’instance Oracle surveillée est pénalisée par un problème de performances. Et, le cas échéant, de mettre sur la piste d’une résolution en pointant l’origine du ralentissement ou du blocage.
Pour cela on cherche donc à repérer un éventuel excès de consommation ou d’attente.
Et une fois les symptômes détectés, l’Analyzer nous permet de comprendre et expliquer ce qui en est la cause, afin de travailler sur les éléments responsables de cette situation.
Objectifs du Shared Pool Analyzer
Dans le cas du Shared Pool Analyzer, un excès est lié à une sollicitation anormale de la zone mémoire Oracle de shared pool.
Cela peut par exemple se traduire par :
- Une zone mémoire Oracle de shared pool trop sollicitée ou mal utilisée
- Des sessions en attente sur des contentions de type « latch free » au niveau library cache ou shared pool
- Des requêtes qui utilisent beaucoup d’espace mémoire
- Une consommation élevée de CPU destinée à réaliser le parsing des requêtes
- Un nombre de requêtes à analyser bien supérieur à ce qui devrait être nécessaire
- Un composant Oracle qui occupe de l’espace mémoire en shared pool de manière inhabituelle
- Une zone mémoire de shared pool de grande taille dans laquelle il ne reste jamais d’espace libre
Comment utiliser le Shared Pool Analyzer
Parsing
Shared Pool Main Areas
Par exemple « KGH: NO ACCESS ».
Cette section du Shared Pool Analyzer permet de détecter facilement ce genre de situations.
Shared pool content
Les requêtes SQL basées sur des constantes au lieu de bind variables sont détectées instantanément dans cette section.
Pour plus de fluidité, seuls les 10 types de requêtes SQL les plus consommateurs en shared pool sont affichés dans cette liste, l’objectif étant bien de voir les plus importantes valeurs, représentant les requêtes qui peuvent poser des problèmes, pas de tout lister.
Autres informations
SQL Info
La ligne sélectionnée correspond à une seule « distinct query » et un seul « curseur ».
Dans ce cas, d.side aiguille sur la fenêtre SQL Info qui permet d’obtenir tous les éléments nécessaires à l’analyse de cette requête, de son texte à son plan d’exécution, en passant par les statistiques d’exécution.
SQL Cursor Info
La ligne sélectionnée correspond à une seule « distinct query », mais la requête choisie dispose de plusieurs curseurs, plusieurs « children ».
Dans ce cas d.side ouvre une fenêtre « SQL cursor info » qui permet d’avoir un résumé de ces différents children. Et notamment, quand Oracle fournit l’information, les raisons pour lesquelles ces curseurs n’ont pas pu être partagés et ont amené Oracle à créer différents children.
Shared pool content Analyzer
Ces requêtes débutent toutes par les mêmes « n » caractères. « n » étant la valeur fournie dans le compteur « SQL text substring size » :
Dans ce cas, d.side affiche la liste complète des requêtes débutant par les mêmes 40 caractères, permettant de mettre facilement en évidence les requêtes qui pourraient profiter de l’utilisation de bind variables plutôt que de s’appuyer sur des constantes.
Comment ouvrir le Shared Pool Analyzer
Exemple :
Exemples d’utilisation
Cas 1
Cas 2
Ces 5 000 requêtes sont pourtant toutes basées sur le même texte :
select customer_name from customers where customer_num...
Par ailleurs, le taux de CPU consacré au parsing semble anormalement élevé, avec 10% des ressources utilisées pour analyser les requêtes, plutôt que pour les exécuter (aller chercher les lignes pour un SELECT, par exemple).
Ces éléments ont amené à proposer une nouvelle écriture de ces requêtes en exploitant une bind variable au lieu des 5 000 constantes différentes.
Conclusion
Le Shared Pool Analyzer de d.side permet d’observer avec précision et rapidité tout ce qu’il est important de savoir sur le fonctionnement du shared pool Oracle. On peut ainsi détecter et déterminer la cause de ralentissements ou de contentions sur cette zone mémoire.
Les responsables de ces problèmes de performances peuvent être la manière dont est écrite une requête SQL ou une zone mémoire de shared pool trop petite, par exemple.
Il existe de très nombreux bugs Oracle sur la gestion des curseurs en shared pool, mais dans la plupart des cas, utiliser des bind variables au lieu de constantes dans l’écriture des requêtes SQL identifiées sera la manière la plus efficace de résoudre les problèmes ou diminuer les attentes sur cette zone mémoire de shared pool. Cela peut passer par l’utilisation du paramètre d’instance Oracle cursor_sharing. Et optimiser la phase de parsing permettra également de prévenir des erreurs Oracle du type ORA-4031 “unable to allocate %s bytes of shared memory”.