Comprendre les résultats de Execute Explain Plan dans Oracle SQL Developer
J'essaie d'optimiser une requête mais je ne comprends pas très bien certaines des informations renvoyées par Explain Plan . Quelqu'un peut-il me dire l'importance des options et des colonnes de coûts? Dans la colonne OPTIONS, Je ne vois que le mot complet. Dans la colonne Coût, je peux en déduire qu'un coût inférieur signifie une requête plus rapide. Mais que représente exactement la valeur de coût et qu'est-ce qu'un seuil acceptable?
5 réponses
La sortie de EXPLAIN PLAN est une sortie de débogage de l'optimiseur de requête D'Oracle. Le coût est la sortie finale de L'optimiseur basé sur les coûts (CBO), dont le but est de sélectionner lequel des nombreux plans possibles doit être utilisé pour exécuter la requête. Le CBO calcule un coût relatif pour chaque plan, puis choisit le plan avec le coût le plus bas.
(Note: dans certains cas, le CBO n'a pas assez de temps pour évaluer tous les plans possibles; dans ces cas, il choisit simplement le plan avec le le coût le plus bas trouvé à ce jour)
En général, l'un des plus gros contributeurs à une requête lente est le nombre de lignes lues pour traiter la requête (blocs, pour être plus précis), de sorte que le coût sera basé en partie sur le nombre de lignes que les estimations de l'optimiseur devront lire.
Par exemple, disons que vous avez la requête suivante:
SELECT emp_id FROM employees WHERE months_of_service = 6;
(la colonne months_of_service a une contrainte NOT NULL et un index ordinaire.)
Il y a deux plans de base le optimiseur peut choisir ici:
- Plan 1: Lisez toutes les lignes de la table "employés", pour chacune, vérifiez si le prédicat est vrai (
months_of_service=6). - Plan 2: Lisez l'index où
months_of_service=6(il en résulte un ensemble de ROWIDs), puis accédez à la table en fonction des ROWIDs retournés.
Imaginons que la table "Employés" comporte 1 000 000 (1 million) de lignes. Imaginons en outre que les valeurs de months_of_service vont de 1 à 12 et sont assez uniformément réparties pour certains raison.
Le coût de Plan 1 , qui implique une analyse complète, sera le coût de la lecture de toutes les lignes de la table employees, qui est approximativement égal à 1 000 000; mais comme Oracle sera souvent capable de lire les blocs en utilisant des lectures multi-blocs, le coût réel sera inférieur (selon la façon dont votre base de données est configurée)-par exemple imaginons que le nombre de lectures multi - blocs est de 10-le coût calculé de l'analyse complète sera de 1 000 000 / 10; coût global = 100 000.
Le le coût de Plan 2, qui implique une analyse de plage D'INDEX et une recherche de table par ROWID, sera le coût de l'analyse de l'index, plus le coût d'accès à la table par ROWID. Je n'entrerai pas dans la façon dont les scans de plage d'index sont chiffrés, mais imaginons que le coût de l'analyse de plage d'index est de 1 par ligne; nous nous attendons à trouver une correspondance dans 1 cas sur 12, donc le coût de l'analyse d'index est de 1 000 000 / 12 = 83 333; plus le coût lit ici) = 83 333; coût global = 166 666.
Comme vous pouvez le voir, le coût du Plan 1 (Analyse complète) est inférieur au coût du Plan 2 (Analyse d'index + accès par rowid) - ce qui signifie que le CBO choisirait l'analyse complète.
Si les hypothèses faites ici par l'optimiseur sont vraies, alors en fait le Plan 1 sera préférable et beaucoup plus efficace que le Plan 2 - ce qui réfute le mythe selon lequel les analyses complètes sont "toujours mauvaises".
Les résultats seraient très différents si l'objectif d'optimisation était FIRST_ROWS (n) au lieu de ALL_ROWS-auquel cas l'optimiseur favoriserait le Plan 2 car il retournera souvent les premières lignes plus rapidement, au prix d'être moins efficace pour l'ensemble de la requête.
Le CBO construit un arbre de décision, estimant les coûts de chaque chemin d'exécution possible disponible par requête. Les coûts sont définis par le paramètre CPU_cost ou I/O_cost défini sur l'instance. Et le CBO estime les coûts, du mieux qu'il peut avec les statistiques existantes des tables et des index que la requête utilisera. Vous ne devez pas régler votre requête en fonction du seul coût. Le coût vous permet de comprendre pourquoi l'optimiseur fait ce qu'il fait. Sans frais vous pourriez comprendre pourquoi l'optimiseur choisir le plan qu'il a fait. Faible coût ne signifie pas une requête plus rapide. Il y a des cas où cela est vrai et il y aura des cas où cela est faux. Le coût est basé sur les statistiques de votre table et s'ils sont faux, le coût va être faux.
Lorsque vous réglez votre requête, vous devriez regarder la cardinalité et le nombre de lignes de chaque étape. Ont-elles un sens? La cardinalité que l'optimiseur suppose est-elle correcte? Est les lignes de retour raisonnable. Si l'information présente est mal alors il est très probable que l'optimiseur n'ait pas les informations appropriées dont il a besoin pour prendre la bonne décision. Cela pourrait être dû à des statistiques périmées ou manquantes sur la table et l'index ainsi que sur les statistiques cpu. Il est préférable d'avoir des statistiques mises à jour lors du réglage d'une requête pour tirer le meilleur parti de l'optimiseur. Connaître votre schéma est également d'une grande aide lors du réglage. Savoir quand l'optimiseur a choisi une très mauvaise décision et le pointer dans le bon chemin avec un petit indice peut économiser beaucoup de temps.
Voici une référence pour utiliser EXPLAIN PLAN avec Oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm ), avec des informations spécifiques sur les colonnes trouvées ici: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i18300
Votre mention de 'FULL' m'indique que la requête effectue une analyse complète de la table pour trouver vos données. C'est correct, dans certaines situations, sinon un indicateur de mauvaise indexation / requête écriture.
Généralement, avec explain plans, vous voulez vous assurer que votre requête utilise des clés, ainsi Oracle peut trouver les données que vous recherchez en accédant au moins de lignes possible. En fin de compte, vous pouvez parfois seulement aller jusqu'à présent avec l'architecture de vos tables. Si les coûts restent trop élevés, vous devrez peut-être envisager d'ajuster la disposition de votre schéma pour qu'il soit davantage basé sur les performances.
Dans les versions récentes D'Oracle, le coût représente le temps que l'optimiseur s'attend à ce que la requête prenne, exprimé en unités du temps nécessaire à la lecture d'un seul bloc.
Donc, si une seule lecture de bloc prend 2ms et que le coût est exprimé en "250", la requête pourrait prendre 500ms à compléter.
L'optimiseur calcule le coût en fonction du nombre estimé de lectures monobloc et multibloc et de la consommation CPU du plan. ce dernier peut être très utile pour minimiser le coût en effectuant certaines opérations avant d'autres pour essayer d'éviter les opérations de coût élevé du processeur.
Cela soulève la question de savoir comment l'optimiseur sait combien de temps les opérations. les versions récentes D'Oracle permettent les collections de "statistiques système", qui ne doivent certainement pas être confondues avec les statistiques sur les tables ou les index. Les statistiques du système sont des mesures de la performance du matériel, surtout important:
- Combien de temps un seul bloc lire prend
- Combien de temps prend une lecture multibloc
- Quelle est la taille d'une lecture multibloc (souvent différente au maximum possible en raison des étendues de table étant plus petites que le maximum, et d'autres raisons).
- performances du processeur
Ces chiffres peuvent varier considérablement en fonction de l'environnement d'exploitation du système, et différents ensembles de statistiques peuvent être stockés pour les opérations "jour OLTP" et les opérations" nuit batch reporting", et pour les" rapports de fin de mois " si vous le souhaitez.
Compte tenu de ces ensembles de statistiques, un plan d'exécution de requête donné peut être évalué pour le coût dans différents environnements d'exploitation, ce qui peut favoriser l'utilisation d'analyses de table complètes à certains moments ou d'analyses d'index à d'autres.
Le coût n'est pas parfait, mais l'optimiseur s'améliore à l'auto-surveillance à chaque version, et peut évaluer le coût réel par rapport au coût estimé afin de prendre de meilleures décisions pour l'avenir. cela rend également plus difficile prédire.
Notez que le coût n'est pas nécessairement l'Heure de l'horloge murale, car les opérations de requête parallèles consomment un temps total sur plusieurs threads.
Dans les anciennes versions D'Oracle, le coût des opérations CPU était ignoré, et les coûts relatifs des lectures monoblocs et multiblocs étaient effectivement fixés en fonction des paramètres d'initialisation.
FULL fait probablement référence à une analyse de table complète, ce qui signifie qu'aucun index n'est utilisé. Cela indique généralement que quelque chose ne va pas, sauf si la requête est censée utiliser toutes les lignes d'une table.
Le coût est un nombre qui signale la somme des différentes charges, processeur, mémoire, disque, E / S et les nombres élevés sont généralement mauvais. Les chiffres sont additionnés lorsque vous vous déplacez à la racine du plan, et chaque branche devrait être examinée pour localiser les goulets d'étranglement.
Vous pouvez aussi vous voulez interroger V$sql et V $ session pour obtenir des statistiques sur les instructions SQL, et cela aura des métriques détaillées pour tous les types de ressources, de timings et d'exécutions.