Méthode de détection d'un problème ( partie 1 )

Sur ce sujet, je vous conseille de consulter les différents messages de Tanel Poder et James Morle:
- http://jamesmorle.wordpress.com/2009/11/09/the-oracle-wait-interface-is-useless-sometimes-pt/
- http://blog.tanelpoder.com/2010/01/15/beyond-oracle-wait-interface-part-2/
- http://jamesmorle.wordpress.com/2010/01/18/the-oracle-wait-interface-is-useless-sometimes-part-3a/
- http://jamesmorle.wordpress.com/2010/02/16/the-oracle-wait-interface-is-useless-sometimes-part-3b/

Pour détecter un problème dans une instance Oracle, on peut commencer par utiliser l' interface des wait events.
Un wait event représente une attente d'un processus utilisateur ( session ) ou de l'instance ( PMON, SMON ... ). Elle peut être liée à l'accès à une ressource ( données du cache, verrous ... ) . Par exemple, une session qui a besoin d'un bloc d'index absent dans la SGA effectue une demande de lecture au système, puis attend la réception du bloc. Ce temps d'attente est un type de wait event, en l'occurence le wait event db file sequential read.
Les deux vues utiles sont v$session_wait ( niveau session ) et v$system_event ( niveau instance ). Le script sw.sql de T.Poder donne un exemple d'utilisation de la vue v$session_wait.

Une description détaillée des wait events est disponible à cette adresse: http://sites.google.com/site/embtdbo/wait-event-documentation.

L'event trace de type 10046 permet aussi de récupérer les wait events. Au niveau d'une session, on l'active de la manière suivante:
- alter session set timed_statistics = true; -- Activation des Extended SQL Trace
- alter session set max_dump_file_size = unlimited;
- alter session set events '10046 trace name context forever, level 8';
- alter session set events '10046 trace name context off';
Pour tracer une session autre que la sienne, on peut utiliser la méthode set_ev du package dbms_system ou l'outil de debug d'Oracle, oradebug.

On peut récupérer les traces dans le répertoire indiqué par le paramètre d'initialisation USER_DUMP_DEST. Le suffixe des fichiers trace est trc ou TRC. On peut faciliter l'identification du fichier trace via cette commande: alter session set tracefile_identifier = 'MonFichierTrace'. L'utilitaire tkprof permet de mettre en forme les fichiers trace.

Le chapitre 5 du livre de Cary Millsap, Optimizing Oracle Performance, constitue une introduction intéressante pour la compréhension des traces SQL étendues. Une mise à jour est disponible à cette adresse: http://method-r.com/downloads/doc_details/72-mastering-performance-with-extended-sql-trace.

Si nécessaire, l'analyse du problème peut être étendue à la couche système. Pour commencer, on utilise la commande vmstat pour avoir une vision générale du système ( CPU, mémoire virtuelle ). Puis, on peut étudier plus en détail les processus ou threads ( top, topas, prstat ), la CPU ( mpstat ), les interruptions (intrstat ), les appels système ( truss, strace ).