Un DBA est souvent confronté à la situation suivante : Il vient de rendre son plus bel audit qui liste un certains nombre de problèmes dont quelques ordres SQL sur lesquels il pourrait être intéressant de se pencher.
Le tuning SQL étant un travail de Maitrise d’Oeuvre voire de Tierce Maintenance Applicative et là c’est le début de la fin. En effet, bien souvent (devrais-je dire trop ?) les responsables des correctifs ou du développement sont certes pléthore mais ne compte pas parmi leurs troupes de développeur SQL voire de DBA. Bien évidemment le chef de projet demande si ça ne pourrait pas être résolu par du paramétrage ( comment ça, le paramètre caché _fast n’existe pas ? ) et après des palabres interminables sur le pourquoi du comment de qui fait quoi, le DBA de production se retrouve à devoir faire les propositions d’améliorations, « si possible sans toucher à la base, merci » … !
Un outillage assez pauvre
Si la base de données Oracle est riche en API de diagnostique globales, elle pêche un peu quand il s’agit de diagnostiquer individuellement du SQL.
On dispose bien de traces pour comprendre ce qui se passe mais celles-ci ne sont pas toujours efficaces. Par exemple une trace 10053 peut, on l’a vu, vous induire en erreur en présence de variables de liaison (bind-variables) et une trace 10046 nécessite l’exécution du SQL, et le chef de projet ayant demandé d’éviter de toucher à la base il est délicat d’y faire tourner les quatre ou cinq requêtes les plus consommatrices que vous vous avez croisé.
Aller à la pêche aux informations
Pour effectuer le diagnostique on a donc besoin d’aller chercher un maximum d’informations dans la base de production (voire de développement ou de pré-production) sans lui faire trop de mal. On peut aller voir si le curseur est en mémoire, aller chercher les plans dans les historiques AWR ou Perfstat, aller chercher la descriptionset les stats sur les objets, regarder le paramétrage …
Cela peut-être long et fastidieux et n’est pas absolument certain d’être exhaustif. Fort heureusement depuis quelque temps les Center Of Expertise (COE) d’Oracle, et plus particulièrement Carlos Sierra, écrit des scripts et des packages pour aider au diagnostique.
Trace Analyzer
Le premier des scripts écrit par C.S. est Trace Analyzer (TRCA). Assez limité, il tout de même une alternative à tkprof en présentant les informations issues d’une trace 10046 sous forme de fichier html plus agréable à lire qu’un simple fichier plat. Seulement il faut faire cette trace 10046 et donc impacter la production.
SQL Trace
Le deuxième package développé par C.S. est SQL Trace (SQLT). Il intègre TRCA et permet d’aller chercher toutes les informations relatives à un SQL, de les consolider dans un schéma, des créer des jeux de tests. Le seul soucis est la création nécessaire d’un schéma sur la base de production qui au fur et à mesure des versions a enflé. S’il vous est possible de l’utiliser sur votre production n’hésitez pas, les informations remontées sont les plus complètes qui soient. En outre cet outil permet de monter des tests cases (avant test case builder, oui madame), de positionner des profils d’exécution, de tester l’effet du paramétrage sur le SQL …
SQL Health Check
Dernier né des créations C.S. SQL Health Check (SQLHC), une pure merveille (c’est un avis personnel que vous finirez bien par partager :p), Il va chercher les principales informations relatives à un SQL_ID et donne le résultat dans quelques fichiers html. Léger comme une plume il ne nécessite pas de schéma propre et comme tous les scripts de C.S. n’utilise que les options que vous voulez bien qu’il utilise.
Où trouver ces formidables scripts ?
Simple, ils sont sur My Oracle Support :
Et pleins d’infos sur le blog de Carlos Sierra.
