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
Le Shared Pool Analyzer va permettre d’observer comment se comporte Oracle dans sa gestion de sa zone mémoire de shared pool, afin de détecter notamment des requêtes provoquant une consommation importante de ressources.
Comment utiliser le Shared Pool Analyzer
Le Shared Pool Analyzer contient trois zones distinctes.
Parsing
Cette section présente la valeur du paramètre d’instance Oracle cursor_sharing, car il a une incidence importante sur le parsing et l’utilisation de la zone de shared pool.
Elle indique également la quantité de CPU qui est utilisée en temps réel par Oracle pour réaliser le parsing, c’est-à-dire l’analyse des requêtes SQL avant leur exécution. Pour donner un ordre de grandeur, Oracle recommande dans sa documentation Performance Tuning Guide que cette statistique soit la plus proche possible de 0%.
Shared Pool Main Areas
Cette zone résume l’utilisation mémoire : taille totale et espace libre en shared pool.
Les cinq plus importants composants de cette zone mémoire sont également affichés. Cela permet de mettre en évidence quand un composant utilise une part anormale de l’espace alloué. Si un composant semble consommer trop de mémoire, une recherche sur le site du support Oracle peut être utile, car il s’agit souvent de bugs Oracle. On notera alors le nom du composant.
Par exemple « KGH: NO ACCESS ».
Cette section du Shared Pool Analyzer permet de détecter facilement ce genre de situations.
Shared pool content
Cette section du Shared Pool Analyzer montre quelles requêtes consomment le plus d’espace en mémoire. Cela est très utile pour identifier rapidement et facilement les requêtes SQL qui pourraient amener à une saturation de la zone de shared pool en termes d’utilisation mémoire, celles qui risquent de provoquer une erreur Oracle ORA-4031 « unable to allocate %s bytes of shared memory » par manque de place, ou celles qui amènent Oracle à consommer trop de CPU pour analyser les requêtes (parsing) au lieu d’utiliser cette ressource à exécuter les requêtes.
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
En double-cliquant sur une ligne de la section « Content » pour obtenir plus de détails sur une requête, on peut ouvrir trois sortes de fenêtres.
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
Dans ce cas, la ligne sélectionnée regroupe plusieurs « distinct queries », c’est-à-dire plusieurs SQL ID différents.
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.
Pour aller encore plus loin, il est à nouveau possible en double-cliquant sur une ligne de cette fenêtre d’obtenir plus de détails, en entrant dans une fenêtre « SQL Info » ou une « SQL cursor info » selon le nombre de children de la ligne choisie.
Comment ouvrir le Shared Pool Analyzer
Le Shared Pool Analyzer est accessible depuis le menu Analyzers de d.side :
On peut également ouvrir le Shared Pool Analyzer en double-cliquant dans l’écran principal sur un événement d’attente lié au comportement du shared pool Oracle.
Exemple :
Par ailleurs, cliquer sur la statistique « Parse CPU » de la zone « Main Statistics » de l’écran principal permet aussi d’entrer dans le Shared Pool Analyzer.
Exemples d’utilisation
Cas 1
Les informations remontées par le Shared Pool Analyzer ne mettent ici aucune dérive en évidence. Elles nous indiquent qu’il n’y a a priori aucune contention, et que les requêtes sont bien écrites, sollicitant la mémoire de manière homogène, sans excès de CPU :
Le shared pool d’Oracle ne semble pas poser de problème ici, et aucun composant ni aucune requête SQL ne sont pointés comme étant des pistes d’optimisation.
Cas 2
En revanche ici on observe bien un type de requêtes SQL qui occupe à lui seul le quart de la zone de shared pool.
L’ensemble des 5 000 requêtes débutant par les 54 premiers mêmes caractères occupent ici à elles seules 25% de la zone allouée au shared pool. Les 619 Mo rapportés aux 2,42 Go de la taille totale représentent bien 25%, ce que matérialise la barre de progression « Sharable Memory » pour cette ligne.
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”.
