Présentation de l'optimiseur

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 :

vtc00090000.gif Règle évalue les chemins d'exécution possibles et les classes en fonction d'une série de règles syntaxiques.

vtc00090000.gif 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.

vtc00090000.gif 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.

vtc00090000.gif 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 :

  1. L'optimiseur génère un ensemble de plans d'exécution potentiels pour l'instruction, sur la base des chemins d'accès disponibles et des conseils de cette instruction.

  2. L'optimiseur estime le coût de chaque plan d'exécution sur la base de la distribution des données et des statistiques de caractéristiques de stockage des tables, des clusters et des index dans le dictionnaire de données.

  3. Le coût est une valeur estimée, proportionnelle au taux d'utilisation des ressources prévu, nécessaire pour exécuter l'instruction à l'aide du plan d'exécution. L'optimiseur calcule le coût sur la base des ressources informatiques estimées, y compris (mais pas seulement) les E/S, le temps UC et la mémoire, nécessaires pour exécuter l'instruction à l'aide du plan.

Les plans d'exécution en série avec des coûts élevés ont une durée d'exécution plus longue que celle des plans aux coûts réduits. Lorsque vous utilisez un plan d'exécution parallèle, cependant, l'utilisation des ressources n'est pas directement liée au temps écoulé.

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 :

vtc00090000.gif tables partitionnées

vtc00090000.gif vues de partition

vtc00090000.gif tables organisées en index

vtc00090000.gif index de clé inverse

vtc00090000.gif index bitmap

vtc00090000.gif interrogation parallèle et instruction DML parallèle

vtc00090000.gif transformation en étoile

vtc00090000.gif 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.