Pour comprendre les détails d'un plan d'exécution, vous devez d'abord comprendre la façon dont l'optimiseur fonctionne. L'optimiseur est une fonction de la base de données Oracle qui choisit la méthode qu'il estime la plus efficace pour exécuter une instruction SQL. Il s'agit d'une étape importante dans le traitement de toute instruction en langage de manipulation de données (DML) : SELECT, INSERT, UPDATE ou DELETE. Il existe souvent de nombreuses méthodes différentes pour exécuter une instruction SQL, par exemple en changeant l'ordre d'accès aux tables ou aux index/partitions. La procédure qu'utilise Oracle pour exécuter une instruction peut avoir des conséquences importantes sur la vitesse d'exécution de l'instruction. Les chemins d'accès que l'utilitaire d'optimisation choisit peuvent être affichés par génération des détails du plan d'exécution.
L'optimiseur possède quatre modes de fonctionnement principaux : Règle, Coût - Première ligne, Coût - Toutes les lignes et Choisir. Le mode que vous sélectionnez détermine la stratégie de l'optimiseur :
Règle évalue les chemins d'exécution possibles et les classes en fonction d'une série de règles syntaxiques.
Coût - Première ligne exécute l'instruction de façon à extraire le plus efficacement possible la première ligne de données. L'accent est mis sur la rapidité d'extraction.
Coût - Toutes les lignes exécute l'instruction de façon à réduire au maximum l'impact possible sur les performances globales. L'accent est mis sur la rapidité de débit total.
Choisir appelle le mode Coût toutes les lignes si des tables ont été analysées et le mode basé sur les règles si aucune table n'a été analysée.
Vous pouvez fixer un objectif d'optimisation par défaut en spécifiant le paramètre OPTIMIZER_MODE dans le fichier init.ora de votre base de données ou vous pouvez le modifier au niveau de la session grâce à une commande ALTER SESSION. De plus, vous pouvez configurer l'optimiseur pour une instruction SQL spécifique en y ajoutant des conseils. Les différences entre l'optimisation basée sur les règles et celle basée sur les coûts sont présentées ci-dessous.
Approche basée sur les règles
Lorsqu'il utilise l'approche basée sur les règles, l'optimiseur choisit les détails d'un plan d'exécution sur la base des chemins d'accès disponibles et de leur rang. Vous pouvez utiliser l'optimisation basée sur les règles pour accéder à la fois aux données relationnelles et aux types d'objet. La classification par rang des chemins d'accès effectuée par Oracle est heuristique. S'il existe plusieurs méthodes pour exécuter une instruction SQL, l'approche basée sur les règles utilise toujours l'opération de rang inférieur. En règle générale, ces opérations sont plus rapidement exécutées que celles de rang supérieur.
Approche basée sur les coûts
Lorsqu'il utilise l'approche basée sur les coûts, l'optimiseur détermine le plan d'exécution le plus efficace en tenant compte des chemins d'accès disponibles et en intégrant des informations basées sur des statistiques du dictionnaire de données relatives aux objets de schéma (tables, clusters, ou index/partitions) auxquels l'instruction accède. L'approche basée sur les coûts prend également en considération les conseils, ou suggestions d'optimisation, figurant dans un commentaire dans l'instruction.
L'approche basée sur les coûts est constituée des étapes suivantes :
4. L'optimiseur compare le coût des plans d'exécution et choisit le plan ayant le coût le plus faible.
Remarque : vous devez analyser le schéma de votre base de données à l'aide de la commande ANALYZE afin de maintenir les statistiques à jour. Si vous n'analysez pas le schéma de base de données de façon régulière, l'optimiseur utilisera des statistiques qui risquent d'être non valides et ne parviendra pas à choisir la méthode d'accès la plus efficace.
Objectif de l'approche basée sur les coûts
Par défaut, l'objectif de l'approche basée sur les coûts est d'améliorer le débit ou de réduire l'utilisation des ressources lors du traitement de toutes les lignes auxquelles l'instruction accède.
Oracle peut également optimiser une instruction en vue d'améliorer le temps de réponse ou de réduire les ressources nécessaires au traitement de la première ligne à laquelle une instruction SQL accède.
Statistiques pour l'approche basée sur les coûts
L'approche basée sur les coûts utilise des statistiques pour estimer le coût de chaque plan d'exécution. Ces statistiques quantifient la répartition des données et les caractéristiques de stockage des tables, des colonnes, des index et des partitions. Vous pouvez générer ces statistiques à l'aide de la commande ANALYZE. L'optimiseur se sert de ces statistiques pour estimer la quantité d'E/S, de temps UC et de mémoire nécessaire pour exécuter une instruction SQL en utilisant un plan d'exécution particulier.
Quand utiliser l'approche basée sur les coûts ?
Oracle SQL Analyze vous permet de tester à la fois l'approche basée sur les règles et celle basée sur les coûts. En règle générale, cependant, il est conseillé d'utiliser l'approche basée sur les coûts pour toutes les nouvelles applications. L'approche basée sur les règles est conçue pour des applications écrites avant que l'optimisation basée sur les coûts soit disponible. L'optimisation basée sur les coûts peut être plus efficace, car son évaluation est basée sur les informations les plus récentes concernant les performances de votre base de données. Elle choisit généralement un plan d'exécution aussi bon, voire meilleur, que celui choisi par l'approche basée sur les règles, particulièrement en ce qui concerne les interrogations volumineuses dotées de plusieurs jointures ou de plusieurs index.
Cependant, comme l'optimisation basée sur les coûts dépend fortement des statistiques d'objet, il est important de régénérer régulièrement ces statistiques à l'aide de la commande ANALYZE.
Les fonctionnalités suivantes ne peuvent utiliser que l'optimisation basée sur les coûts :
tables partitionnées
vues de partition
tables organisées en index
index de clé inverse
index bitmap
interrogation parallèle et instruction DML parallèle
transformation en étoile
jointure en étoile
L'optimisation basée sur les coûts doit être utilisée pour améliorer les performances d'interrogation en étoile. De même, elle doit être utilisée avec les jointures de hachage et les histogrammes.
L'optimisation basée sur les coûts est toujours utilisée avec l'interrogation parallèle et avec les tables partitionnées.