Tracer et Améliorer les requêtes SQL avec p6spy

Previous Section: Introduction to p6spy

p6spy

p6spy is a generic solution for tracing SQL requests in applications that use the Java Database Connectivity (JDBC) interface. It is slightly more advanced than the native Weblogic trace tool. The program is open source and can be downloaded from: http://www.p6spy.com/. it is also worth considering JDBInsight, a commercial product that offers some interesting advanced functionality.

2.1 Installation Under Weblogic

copy the files: p6spy.jar and spy.properties to the target server
make sure that spy.properties is in a directory included in the JAVACLASSPATH
add p6spy.jar to the WEBLOGICCLASSPATH
edit the weblogic properties file and search for the property weblogic.jdbc.connectionPool, note on later versions of Weblogic you will need to do this through the administration console as direct access to weblogic.properties is not allowed
comment out the existing driver by putting a '#' at the start of the line
replace the driver with the one supplied with p6spy
driver=com.pspy.engine.spy.P6SpyDriver
edit spy.properties and replace the property: realdriver with the driver you commented out in the weblogic properties file, for example:
realdriver=oracle.jdbc.driver.OracleDriver
restart Weblogic

It is possible to replace up to 3 connection pools utilising the virtual driver : com.pspy.engine.spy.P6SpyDriver2 / realdriver2 etc.

Run the tail command (Unix/Cygwin) on the spy.log file which you can find in the startup directory of Weblogic. You will see logs similar to the following each time the Java application access the database:

1038568825766|0|1|rollback||
1038568825777|4|9|statement|SELECT U.IDENT FROM USER_TABLE E, STATEMENT_TABLE U WHERE U.IDENT = E.IDENT AND U.SOCIAL_SECURITY_NUM = ? AND E.EX_NUMBER = ? |SELECT U.IDENT FROM USER_TABLE E, STATEMENT U WHERE U.IDENT = E.IDENT AND U.SOCIAL_SECURITY_NUM = '24071964665659410' AND E.EX_NUMBER = '11113946600012'
1038568825765|0|1|commit||
1038568825766|0|1|rollback||

The trace gives the following information, each field is separated by a "|" symbol:

The current time, it is obtained from the method: System.getCurrentTimeMillis() and gives the number of millisecondes since the 1st of January 1970. You can modify the format using the dateformat property in the file spy.properties.
Temps d'exécution : c’est le temps passé pour que cette méthode s'exécute. Il est important de noter que ceci n'est pas tout le coût d’une commande SQL. Par exemple, un SQL pourrait être exécuté comme commande préparée (prepared statement), dans lequel la méthode : execute() est calculée. Ceci est enregistré comme : « statement » dans la colonne 4. De plus, chaque fois vous appelez la méthode next() sur la Java objet : ResultSet, pour récupérer les données, les appels sont enregistrés comme : result dans la même colonne.
Catégorie : il y a actuellement trois catégories: debug, statement (les commandes SQL) et result (le résultat). Vous pouvez contrôler cette trace avec le fichier spy.properties.
Commande SQL virtuelle : c'est la commande SQL passée vers JDBC par l’application. Si c'est une commande préparée, ce sera avant que les paramètres soient mis en place.
Commande SQL réelle : pour la commande préparée, cette colonne montre le SQL qui est vraiment exécuté par la base de données.

En analysant le SQL, il est important de lier les requêtes SQL à des EJBs ou à des Servlets et de les grouper par des unités de travail (transactions). Les fabricants de base de données fournissent un certain nombre d'outils pour analyser le SQL.

2.2 Installation Under Websphere 4.0

Assurer que le driver JDBC est configure dans le server Websphere comme un DataSource. Le process de configuration consiste de « cloner » le Provider existant et rederiger les requettes SQL par cette Provider qui utilise le driver p6spy.

Demarrer le Websphere adminclient, par exemple:
./adminclient.sh udgir169 11220

Ouvrir l’aborescence :Resouces

Ouvrir l’aborescence : JDBC Providers

Ouvrir l’aborescence du Provider que vous veuillez espionne

Clicke sur le repertoire ‘data source’

Dans l’onglet « general » noter le JDNI Name, l’URL, le nom d’utilisateur et le mot passe (normalment remplacer avec les #, demandez au administrateur de base des donnees)

Changez le « JNDI name » par exemple, ajoute un X_ : jndi/X_ftESource

Clicke droit sur le JDBC provider et clicke « New »

Dans l’onglet « Nodes » selectionne le node, clicke sur le bouton « Specify Driver » et parcourir a p6spy.jar.

Clicke sur « Install »

Dans l’onglet General entre l’information suivant :
Name : P6SpyDriver
Implementation class : com.p6spy.engine.spy.P6ConnectionPoolDataSource

Clicke sur « OK »

Normallement un dialogue s’affichie qui confirme le manipulation

Clicke sur « OK »

Selectionne le repertoire « Data Sources » pour le nouveau Provider, entre le JNDI name noter au dessous, par example : jndi/ftESource

Sauvgarder le configuration

Configurez le fichier spy.properties qui se trouve dans le repertoire : properties, avec l’information que vous avez noter en haut :

realdatasource = jdbc/X_ftESource
realdatasourceclass = oracle.jdbc.pool.OracleConnectionPoolDataSourceourceclass
realdatasourceproperties = user;esplus,password;esplus,URL;jdbc:oracle:thin:udgir169:20011:P44SDB
realdriver = oracle.jdbc.driver.OracleDriver

redemarrer WebSphere

L’installation est terminer, vous pouvez utiliser le bouton « Test Connection » dans le onglet General dans le repertoire Data Sources pour tester chaque connection. Quand vous utiliser votre application un fichier spy.log est créer dans le repertoire de demarrage de WebSphere. Vous pouvez configurer spy.log comme avec WebLogic.

A Noter : selon le version de WebSphere vous pouvez avoir des ennuis avec les ‘2 phase commits’ parce que il y a maintenant deux drivers qui est utilise avec chaque requete. En ce cas il faut remplace le realdatasourceclass avec : oracle.jdbc.xa.client.OracleXADataSource, pour Oracle et faire le meme manipulation pour le Provider que vous espionne.