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 ).

Plan d'exécution ( partie 2 )

Une fois une requête exécutée, vous pouvez visualiser son plan d'exécution contenu dans la library cache, une partie du shared pool ( SGA ) via la commande suivante: select * from table(dbms_xplan.display_cursor('id_requete', 0));.

L'identifiant de requête ( sql_id ) est une chaîne de caractères ( exemple: dqbzw72kjmvhb ). On peut le retrouver à l'aide de la vue v$sql ( colonnes sql_text et sql_fulltext pour une recherche sur le texte de la requête, colonnes last_load_time et last_active_time pour la première ou dernière exécution ).

Pour utiliser le package dbms_xplan, il faut pouvoir lire les vues du catalogue. Pour ce faire, l'instruction est la suivante: grant select_catalog_role to 'utilisateur';.

Pour information, la fonction display_cursor est une encapsulation des vues v$sql_plan et v$sql_plan_statistics_all.