Voici un post pour mettre en lumière un petit programme Java qui remplace avantageusement la page performance de votre DBconsole ou de votre Grid, il se nomme ASH viewer, il est sous licence GPL (General Public License) et a été créé en 2009 par Alex Kardopolov.
Vous pouvez le télécharger à cette url : ASH Viewer project page et vous rendre compte par vous même de son usage, j’ai souhaité cependant vous en faire partagé ici sa description, son installation et quelques exemples d’utilisation.
Pour commencer, bien que la dernière version commence à dater (18 mars 2010), l’utilitaire est compatible avec les versions 8i/9i/10g et 11GR2 de la base de données Oracle, c’est du moins ce qui est écrit dans le fichier README, car je n’ai plus à disposition de version 8i ou 9i, ce que j’ai fait comme essais ne concerne que des versions 10g ou 11g.

Description

L’outil permet d’obtenir des informations graphiques sur les sessions les plus actives d’une base de données. Le terme actif dans ce cadre désignant une session qui est en train de consommer du CPU ou en attente d’un évènement significatif.
Il s’appuie sur la vue V$ACTIVE_SESSION_HISTORY, cette vue est peuplée chaque seconde par le moteur et contient seulement des informations concernant les sessions actives (sur un CPU, ou en attente d’un événement significatif). L’accès à cette vue est soumise à l’acquisition de la licence « Diagnostic Pack » , donc théoriquement réservé pour des versions « Enterprise ».
Les données sont aussi stockées en local via une base Oracle Berkeley DB Java Edition embarquée avec un enregistrement des informations toutes  les 30 secondes.

• Pour les bases en version 10g ou 11g la connexion peut se faire via le compte SYSTEM

• Pour les bases en version 9i (voir 8i) une émulation de la vue V$ACTIVE_SESSION_HISTORY est réalisée en local avec une collecte d’information toutes les secondes, il faut cependant ajouter les deux vues et droits de lecture suivant pour le compte SYSTEM :

create view x_$ksuse as select * from x$ksuse;
create view x_$ksusecst as select * from x$ksusecst;
grant select on sys.x_$ksuse to system;
grant select on sys.x_$ksusecst to system;

Rappelez vous que ce programme est dans le domaine public et qu’à condition de spécifier son usage vous pouvez l’intégrer et l’adapter à vos propre besoin.

Installation

Pré-requis

Les pré-requis sont peu nombreux et sont les suivants :

• L’application est écrite en JAVA et nécessite un JDK 1.5u11 minimum
• Les classes de connexion JDBC oracle sont nécessaire fichier ojdbc14.jar
• 256 Mo de mémoire minimale
• La place disque est dépendante de la quantité d’information que vous allez stocker (durée de l’historique)
• Aucune contrainte sur les systèmes d’exploitation à condition que le JDK soit présent.

Installation, configuration, lancement

Le téléchargement se fait depuis le site précédemment cité, l’URL pour la version courante (3-4) est la suivante:  ashv-3.4
Aller chercher les classes thin JDBC Oracle à cette endroit : ojdbc14.jar
Extraire le fichier ashv-3.4-bin.zip dans son répertoire d’installation ce qui génère ces éléments :

• license.txt , condition d’utilisation de licenseGPL
• notice.txt, licenses GPL incluses
• readme.txt, description et principes
• ASHV.jar , classe principale de l’application ASH Viewer
• run.bat, fichier de lancement pour platefome Windows
• run.sh fichier de lancement pour plateforme unix (/bin/sh)
• Sous répertoire lib contenant les classes java utilisées : commons-logging-1.1.jar, jcommon-1.0.14.jar, je-3.3.75.jar, joda-time-1.6.jar, swingx-1.0.jar

Pour configurer le produit deux actions sont à réaliser :

• Copier la classe java OracleNet ojdbc14.jar sous le sous répertoire lib du répertoire ashv-3-4-bin (répertoire d’installation du produit)
• Modifier le fichier de lancement run.bat ou run.sh pour que la variable JAVA_HOME soit correctement renseignée.

On peut utiliser par exemple le JDK fourni lorsque l’on installe la version « Enterprise » 11.2 du noyau Oracle, dans ce cas mettre une ligne du type suivant dans  le fichier run.bat :

set JAVA_HOME=D:apporacleproduct11.2.0dbEEjdk

Pour démarrer l’application :

• Depuis une plateforme Windows :
  • Exécuter run.bat
• Depuis une plateforme Unix
  • Exécuter run.sh

La première exécution génère le sous répertoire profile dans le répertoire du produit qui contiendra une paire de fichiers par connexion que vous définirez :

• <nom_de_connexion>.ini , description de la connexion, nom , type de driver, chaine de connexion, compte de connexion
• <nom_de_connexion.pwd, mot de passe crypté utilisé pour la connexion

Exemples d’utilisation

La connexion

La première boite de dialogue nommée « ASH Viewer Login » permet de créer une connexion ou de gérer les connexions existantes :

Les options sont explicites, les champs possibles pour une connexion (en cliquant sur « Edit Connection » ) sont : le nom de la connexion, le compte et le mot de passe utilisé, le SID de la base cible, le nom du serveur, le port du listener  ainsi que le choix de la version du noyau pour cette base , ce que l’on voit dans l’image ci-dessous:

Lorsque l’on se connecte à une version « Standard » , la possibilité de générer le report ASH pour la période n’existe plus, ni la fonctionnalité de retrouver le plan d’exécution d’une requête depuis le référentiel AWR.
Si on configure pour une version « Enterprise » alors que la base est en standard, il n’y a pas d’affichage des informations (pas de graphique, pas de TOP SQL), une petite vérification préalable est donc utile.

Graphique par défaut

Une fois  la connexion réalisée  on se retrouve avec le graphique « Top Activity », c’est lui qui synthétise l’activité de vos sessions et qui permettra de voir rapidement s’il y a un problème et dans quel secteur il se situe. Dans l’exemple, après le démarrage de mon simulateur, qui à généré le pic d’IO de type “User IO”, mes sessions restent majoritairement en consommation de CPU,  avec des “commit”  (liés à mes transactions) et de l’IO de type “System IO”, consécutif à l’action du process LOGWRITER, la limite “Maximum CPU” permet  de se rendre compte du dépassement ou non des capacités de votre serveur :

Graphique détails

Chaque classe d’attente que l’on retrouve dans le graphique de synthèse possède ses propres courbes que l’on peut visualiser  en cliquant sur l’onglet “Detail”. On obtient un sous-onglet pour chacune des classes, en 11G:  “CPU Used”, “Scheduler” ,”User IO”, “System IO”, “Concurrency”, “Application”,”Commit”, “Configuration”, “Administration”, ”Network’” , “Queuing”, “Cluster” et “Other” (l’appendice C du manuel de référence Oracle ,  ici , décrit chaque évènement d’attente et la classe à laquelle il appartient).
En voici trois exemples :

• CPU

• User IO

• System IO

Tous les éléments qui composent la classe d’attente et qui sont survenus pendant la période ont leur propre courbe dans chacun des graphique de la classe, on peut ainsi se faire une idée précise de qui provoque un ralentissement.

TOP SQL

Pour obtenir les informations sur les requêtes et les sessions les plus consommatrices, il faut définir le mode “Auto” dans le paramétrage, en cliquant dans le bandeau sur “Settings” :

Passer « Selection Mode » sur «  Auto » avec une valeur d’intervalle (qui par défaut est de 5 minutes) :

Ceci fait apparaitre  onglets « TOP SQL & Sessions » et « ASH History » , attention à l’effet de bord de ce mode « AUTO » qui déclenche le ré-affichage de la fenêtre JAVA toutes les 10 s :

C’est presque l’équivalent du bon vieux TOP Session,  mais les informations sont limitées, les colonnes affichées sont uniquement le numéro de la session, le nom de l’utilisateur et le programme en cours, le tri est fixe sur le pourcentage d’activité le plus élevé. Mais fonctionnalité intéressante, il y a dans la même fenêtre les requêtes les plus consommatrices, et pour obtenir le texte complet et passer les onglets “SQL TEXT” et “SQL PLAN” d’inaccessibles (grisés) à actifs, il faut simplement sélectionner une des requêtes :

Attention en mode “auto” la fenêtre se rafraichit toutes les 10 secondes ce qui annule votre sélection, pour être tranquille lors de votre analyse, repassez “Selection mode” sur “Manuel” (vous le repasserez sur “Auto” ensuite)
Cliquer sur l’onglet “SQL Text” pour obtenir le texte complet de la requête (formaté s’il vous plait) :

Il existe certainement des limites sur la taille du buffer pour les requêtes monstrueuses générées par certains outils décisionnels, mais je n’ai pas été au bout de ce type de test.
Pour obtenir le plan d’exécution (d’une requête préalablement sélectionnée) , cliquer sur l’onglet “SQL PLan” , la première fois vous obtiendrez un message du type :

Sélectionner l’un des choix “Update” (équivalent de DBMS_XPLAN au format tabulaire sans information sur les prédicats ou les filtres), “Cursor Cache” (contenu de V$SQL_PLAN avec les informations sur les prédicats et les filtres) ou “AWR” (contenu du référentiel en provenance des snapshots réalisés sur votre instance). Voici les exemples :

• Mode “Update”

• Mode “Cursor Cache”

• Mode AWR

Si vous êtes connectés à une version “Enterprise” , l’onglet “ASH Report” est actif, il permet de générer le rapport sur la dernière période de prise de statistiques (intervalle correspondant au paramétrage du mode “AUTO”). Ici par exemple pour la dernière demi-heure en cliquant sur le bouton « Get ASH Report » on obtient :

Un dernier intérêt de l’outil est de stocké l’historique des informations utilisées pour générées le graphique “TOP Activity” afin de le reproduire ultérieurement. Pour cela il faut cocher «save to local DB» dans le paramétrage  :

Lors du lancement de l’application, il est alors possible d’utiliser le mode déconnecté (OFFLINE) , très utile , une fois votre collecte faite,  d’avoir toutes les courbes pour fournir un joli rapport :

Vous avez uniquement accès à l’onglet “History”  qui vous permettra en trois temps d’afficher le graphique :

• Sélection du bon profile (correspondant aux informations par connexion)
• Sélection de la date dans le calendrier
• Génération du graphique en cliquant sur le bouton “Select”

Il est aussi possible de parcourir cet historique quand vous êtes en cours de collecte, l’onglet “History” est toujours actif :

Dernière petite astuce, tous les graphiques peuvent être imprimés en utilisant le menu contextuel de la souris (bouton droit “Imprimer”) , l’envoi vers un fichier PDF (à l’aide de pdfcreator par exemple) peut servir , surtout pour les graphiques de détail qui ne sont semble-t-il pas accessibles depuis l’historique (alors que l’option existe, mais je n’ai pas réussi à les avoir).

Conclusion

En résumé un outil très pratique, prenant peu de place (attention quand même à la base de données des historiques), avec une prise en main rapide et une relative stabilité (la commande “enregistrer sous” du menu contextuel de la souris crash en beauté l’application, mais uniquement sous Windows 7 ).
Il permet d’obtenir rapidement des informations pertinentes et de montrer du doigt le ou les problèmes potentiels. Bien sur c’est moins complet que les outils mis à disposition par Oracle depuis l’onglet performance de la Dbconsole, mais c’est suffisant dans bien des cas pour faire remonter à vos équipes applicatives les quelques requêtes qui posent réellement problèmes. Je lui fais donc cette publicité et si vous aussi vous avez des outils simples et efficaces à nous faire partager, n’hésitez pas.