Ce cours s’adresse aux personnes chargées d’optimiser les performances des instructions SQL et/ou des blocs PL/SQL utilisés par une application. Il requiert une très bonne connaissance de SQL et de PL/SQL.
Plan détaillé
Introduction et rappels
- Paramètres d’initialisation.
- Gestion des données de diagnostic.
- Documentation d’Oracle.
- Démarches et choix d’optimisation.
- Configuration de l’optimiseur.
- Collecte des statistiques.
Traitement des instructions SQL et outils de mesure
- Phases de traitement d’une instruction SQL.
- Vues v$sqlarea et v$sql.
- Statistiques d’activité.
- Les zones de travail (SQL Work Areas).
- Visualisation des plans d’exécution, statistiques des plans d’exécution, plans adaptatifs.
- Traçage des sessions SQL et utilisation de TKPROF.
- Utilisation de DBMS_APPLICATION_INFO.
Travaux pratiques : utilisation des vues v$sqlarea/v$sql, les statistiques d’activité, explain plan, autotrace de sqlplus, les zones de tri et l’effet sur la performance, statistiques des plans d’exécution, traçage/tkptof, utilisation de DBMS_APPLICATION_INFO.
Indexation et organisation de données
- Types d’index dans Oracle.
- Tables globales temporaires.
- Groupements de tables (Clusters).
- Partitionnement de tables et d’index.
- Tables externes.
- Vues matérialisées.
Travaux pratiques : indexation des NULLs, intérêt des tables globales temporaires, intérêt du partitionnement des tables, vues matérialisées.
Fonctionnement de l’optimiseur
- Effet de la sélectivité.
- Histogrammes, génération, types et intérêt.
- Méthodes d’accès aux données.
- Méthodes d’exécution des jointures.
- Utilisation de la collecte dynamique de statiques.
- Traitement des requêtes imbriquées, semi jointures et anti jointures.
- Utilisation des indications d’optimisation (optimizer hints), les indications « recommandées ».
Travaux pratiques : statistiques multicolonnes, traitement des requêtes imbriquées, collecte dynamique des statistiques.
Optimisation des instructions SQL
- Recommandations générales.
- Types d’applications et « parses ».
- Utilisation des index.
- Utilisation des variables de liens et curseurs adaptatifs.
- Traitement et factorisation des sous-requêtes.
- Placement des instructions dans le cache.
Travaux pratiques : éviter les jointures inutiles, index basés sur des fonctions, transformation des requêtes afin de bébéfitier des index, variables de liens et curseurs adaptatifs.
Optimisation de PL/SQL
- PL/SQL et l’interaction avec la base, utilisation des curseurs.
- Utilisation des collections (tableaux) dans PL/SQL, clause BULK COLLECT.
- Intérêt de l’utilsiation de FORALL.
- Intérêt de l’utilisation des traitements stockés.
- Mesurer les performances du code PL/SQL par DBMS_PROFILER.
- Performance des fonctions stockées, utilisation de DETERMINISTIC, utilisation de PIPELINED, cache de résultat pour les fonctions stockées.
Travaux pratiques : curseurs explicites utilisant BULK COLLECT, utilisation de FORALL, intérêt des traitements stockés, utilisation de DBMS_PROFILER, utilisation des fonctions DETERMINISTIC et PIPELINED.
Parallélisation des requêtes SQL
- Notion de coordinateur, degré de parallélisme, déroulement des opérations parallèles.
- Configuration du parallélisme.
- Exécution des instructions LDD en parallèle.
- Exécution des instructions LMD en parallèle.
- Interrogation parallèle.
Travaux pratiques : exécution LDD en parallèle, exécution d’instructions de mise à jour en parallèle, exécution d’interrogation en parallèle.
