Trois requêtes pour faire la même chose : quelle est la plus performante ?

[StringBuilder]

Bonjour,

Je travaille avec une base de données Oracle 10g :

Oracle Database 10g Release 10.1.0.5.0 - Production

J'ai deux tables :

Contrats :
CNT (CODSOC, ACHVTE, NUMCNT, ...)

Postes de contrats
CNP (CODSOC, ACHVTE, NUMCNT, TQOI, CODPRO, DATAPP, ...)

En souligné : INDEX UNIQUE faisant office de clé primaire.

Il n'y a pas de notion de clé primaire ni de clé étrangère définie dans la base (oui, je sais, c'est pourri mais c'est comme ça).

Le tuple CNP (CODSOC, ACHVTE, NUMCNT) permet de rattacher des postes à un contrat.

Je dois rechercher tous les contrats (sans doublon) pour lesquels il existe au moins un poste avec DATAPP vide (égale à ' ').

J'ai écrit trois requêtes (existe-t-il d'autres méthodes) ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
-- Celle qui vient naturellement
SELECT cnt.numcnt
FROM cnt
WHERE cnt.codsoc = 100
AND cnt.achvte = 'V'
AND EXISTS (SELECT NULL FROM cnp WHERE cnp.codsoc = cnt.codsoc AND cnp.achvte = cnt.achvte AND cnp.numcnt = cnt.numcnt AND cnp.datapp = ' ');
 
-- Celle qui semblerait-il était plus performance à l'époque de la 8i
SELECT cnt.numcnt
FROM cnt
WHERE cnt.codsoc = 100
AND cnt.achvte = 'V'
AND cnt.numcnt IN (SELECT cnp.numcnt FROM cnp WHERE cnp.codsoc = cnt.codsoc AND cnp.achvte = cnt.achvte AND cnp.numcnt = cnt.numcnt AND cnp.datapp = ' ');
 
-- Celle qui devrait être la plus naturelle pour le SGBD
SELECT DISTINCT cnt.numcnt
FROM cnt
INNER JOIN cnp ON cnp.codsoc = cnt.codsoc AND cnp.achvte = cnt.achvte AND cnp.numcnt = cnt.numcnt AND cnp.datapp = ' '
WHERE cnt.codsoc = 100
AND cnt.achvte = 'V';

Sur un volume de 107648 lignes dans CNT, 125634 dans CNP et 25808 lignes retournées, voici ce que ça donne :

- Les deux premières ont strictement le même plan d'exécution.
- Les trois ont un plan d'exécution qui a le même coût (554).

La troisième requête, pourtant plus naturelle pour le SGBD, est 2 fois plus lente (~0,2 seconde contre ~0,1 seconde pour les deux premières).

La première semble plus rapide que la seconde, alors qu'elle a le même plan d'exécution (en exécutant une dizaine de fois chacune des deux requêtes, la première est presque toujours < 0,1 alors que la seconde est presque toujours > 0,1).

Ce poste est motivé par la volonté de comprendre pourquoi telle syntaxe est plus rapide qu'une autre (alors qu'on fait exactement la même chose) et comment tirer une règle générale lors de l'écriture des requêtes.

Ce qui m'étonne, c'est que j'ai souvenir d'avoir préféré longtemps le EXIST plutôt que le DISTINCT/INNER JOIN et le IN, alors que le IN était plus rapide à une certaine époque (la 8i il me semble, ou la 9g), et que le DISTINCT/INNER JOIN est plus rapide avec SQL Server par exemple.

A part benchmarker chaque requête que j'écris, existe-t-il une règle générale ?

[mnitu]

L’optimiseur de requête prend la liberté de transformer vos requêtes comme bon lui semble ! Dans votre exemple les deux premières requêtes sont transformées de la même façon.
La troisième requête donne le même résultat mais ne fait pas le même travail, vous avez en fait remplacée une semi-jointure par une jointure.

[skuatamad]

- Les trois ont un plan d'exécution qui a le même coût (554).

Déjà le coût n'est pas un indicateur de performance et il est totalement inutile de vouloir comparer le coût de 2 requêtes sémantiquement différentes.
Le seul intérêt du coût est de comprendre pourquoi oracle a chosit un index ou un FTS, un hash join ou un nested loop pour une même requête.
Le coût le plus faible étant probablement la méthode d'accès choisie.
Mais là encore ce n'est pas forcément la méthode d'accès la plus performante.
Si le CBO en amont n'a pas les bonnes infos (statistiques) ou a des infos incomplètes, le coût déterminé sera faux.
Concernant le coût tu peux lire Comment l'optimiseur d'Oracle calcule le coût,et également faire un tour sur la bibliographie.

Concernant tes intérogations sur les précédentes versions tu peux lire IN & EXISTS (poste qui remonte à l'année 2000), mais comme tu l'as constaté les choses changent le CBO évolue, un peu plus bas dans la discussion on peu lire IN and Inner Join

Pour en revenir à la performance des 3 approches, pour les comparer il faut regarder la ligne CONSISTENT GET de l'autotrace.

[ojo77]

Pour autant que je sache, et autant que cela est possible il est préférable de remplacer les sous requêtes (requêtes dans la clause where) par des vues dynamiques (requêtes dans la clause "from" qui peuvent aussi être écrites avant le select dans une clause "with").

Maintenant, comme le dit mnitu Oracle en interne lors du parsing oracle tente une réécriture des requêtes et choisis généralement le type de requête sans sous requêtes et avec de vues dynamiques, bref celle qui, selon vos termes, lui "semble la plus naturelle".

Coté tuning, et c'est toujours un avis personnel, il n'est pas forcément nécessaire de tuner chaque requête, les plus consommatrices en temps sont celles qui méritent de l'être (tunées).

[Mohamed.Houri]

En règle générale, puisque vous parlez de règle générale si tant est qu’une règle générale existe en ORACLE, à chaque fois que vous devez analyser la performance d’une requête SQL ou simplement lorsque vous voulez comprendre le choix fait par Oracle (CBO) pour exécuter votre requête vous devez passer par un explain plan. Dans ce cas vous devez:

1. Obtenir l’explain plan (de préférence le vrai explain plan et non l’approximatif)
2. L’interpréter
3. Juger son efficacité

Comme le précise Jonathan Lewis dans son livre ‘’Cost Based Oracle Fundamentals’’, le CBO peut se tromper pour les raisons suivantes :

1. Des hypothèses inappropriées existent dans le model du CBO
2. Les statistiques représentants la répartition des données sont absentes
3. Les statistiques représentants la répartition des données sont présentes mais ne reflètent pas fidèlement la réalité de cette répartition
4. Les caractéristiques de la machine (hardware performance characteristics) sont inconnues
5. Le volume des données est inconnu
6. Il existe un bug dans le code du CBO

La plupart des mauvais choix faits par le CBO proviennent de la différence d’opinion entre votre connaissance propre de vos données et celle que le CBO estime connaitre. C’est pour cela que tous mes explains plan (enfin la majorité) je les prends en utilisant le hint /*+ gather_plan_statistics */ dans mes requêtes et en utilisant:

Code :

SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL, ‘ALLSTATS LAST’)) ;

Ainsi, grâce à E-Rows et A-Rows, je peux tout de suite savoir si les estimations faites par le CBO sont correctes ou pas. Dans le cas contraire, c’est une indication de statistiques ne reflétant pas la réalité. Je donne aussi une très grande importance à la partie ‘’Predicate’’ de mon explain plan parce qu’en effet ,souvent, cette partie montre les transformations faites par le CBO et surtout les conversions implicites empêchant l’utilisation des indexes lorsque ceux-ci sont appropriés.

Pour revenir à votre cas, je vous conseille:

1. De générer les trois explains plans(les vrais avec E-Rows et A-Rows) avec leur partie predicate
2. D’interpréter ces trois explains plans
3. De trouver le plus efficace d’entre eux et de savoir pourquoi

Enfin, comme il s’agit ici de l’optimisation d’une jointure à deux tables, c’est avec un immense plaisir que je vous conseille la présentation suivante faite par Jonathan Lewis:

http://jonathanlewis.wordpress.com/2011/06/23/video/

dont j’ai l’intention d’en faire un résumé en français un de ces jours.

[Pomalaix]

Envoyé par StringBuilder

...
- Les deux premières ont strictement le même plan d'exécution.
- Les trois ont un plan d'exécution qui a le même coût (554).

Par quelle méthode produisez-vous le plan d'exécution ? EXPLAIN PLAN, par exemple, fournit un plan théorique. Le plan réel peut-être différent.

Oups, désolé, je n'avais pas vu l'intervention de Mohamed.

[Pomalaix]

Envoyé par skuatamad

Pour en revenir à la performance des 3 approches, pour les comparer il faut regarder la ligne CONSISTENT GET de l'autotrace.

Si vous recommandez ceci pour distinguer 3 plans qui ont le même coût, je veux bien (en laissant de côté le problème de plan théorique contre plan réel).
Mais de manière générale, l'algorithme de coût prenant en compte non seulement les lectures logiques, mais aussi la charge CPU induite, il n'y a pas de raison de se focaliser uniquement sur les CONSISTENT GET.

[mnitu]

Envoyé par ojo77

Pour autant que je sache, et autant que cela est possible il est préférable de remplacer les sous requêtes (requêtes dans la clause where) par des vues dynamiques (requêtes dans la clause "from" qui peuvent aussi être écrites avant le select dans une clause "with").
....

Non, pas du tout en règle générale !


@Mohamed Houri
Sour ce point je trouve que vous avez une approche DBA rigide:
requête => problème => analyse => solution=>test=>validation=>résolue.

Or la question ici est la suivante : comment écrire la requête d’une manière proactive, avant que le problème arrive ! Et peu importe l’explain plan.

[skuatamad]

Envoyé par Pomalaix

Si vous recommandez ceci pour distinguer 3 plans qui ont le même coût, je veux bien (en laissant de côté le problème de plan théorique contre plan réel).

Non justement, je ne m'intéresse pas au coût :
Consider Cost or Time - trying to compare the COST of two queries
C'est sûr cette discussion a débuté il y a longtemps sur de vieux produits les choses se sont sûrement améliorées.

Si 3 requêtes donnent le même résultat avec des plans différents, je regarde les Logical IO, le moins il y en a, le moins il y aura de physical IO.
C'est déjà un bon moyen de sélectionner quelle requête est la plus pertinante, non ?

Mais je suis d'accord que le CPU peut être un autre facteur important (un peu plus bas dans la page):

typically, in general, it is LIO based, the less LIO the better.

however, there are exceptions -- eg: a hash join will generally do lots less LIO but might chew up
more cpu -- and if cpu is a scarce resource in your system...

So, you benchmark them.

[Mohamed.Houri]

Marius,

Vous pouvez écrire la requête comme bon vous semble, si la performance vous est acceptable vous vous arrêterez là. Si par contre la performance n’est pas acceptable et que vous embarquez immédiatement dans la réécriture de la requête c’est que vous avez conclu sans preuve ni évidence que la lenteur de la requête provient de son écriture et qu’il suffit d’une réécriture adéquate et le tour est joué !!! C’est ce que j’appelle ‘’Hit and Try’’ ou changeant et voyant ce qui se passe.

Il se peut que cela fonctionne parfaitement ; mais il se peut que malgré vos efforts de réécriture le CBO continue de choisir un chemin non performant à cause de l’absence d’information reflétant la réalité de la distribution des données ou à cause de de l’absence d’index précis pour couvrir la requête.

C’est clair qu’ici je ne parle pas des requêtes où des clauses WHERE et AND sont inutiles ou une jointure est manquante ou une jointure est en trop, ou etc… Je parle d’une requête cohérente comme celle présentée dans l’exemple qui a conduit à cette discussion.

L’école que je suis m’a enseigné de regarder l’explain plan aux fins d’y trouver l’explication à toute lenteur. Parfois, et même souvent, une réécriture de la requête permet d’aboutir à un temps de réponse acceptable. N’empêche que cette réécriture m’est dictée par l’explain plan. Lorsque je constate, par exemple, que je commence par une opération qui génère 145.000 lignes pour ne laisser à la fin qu’une centaine de lignes, alors dans ce cas, j’utilise le très formidable axiome de Jonathan Lewis: ‘’Eliminate Earlier, start small and try to keep small ‘’, qui veut dire dans la langue de Molliere :’’Eliminez des lignes le plutôt possible, commencer avec un petit volume et essayer de maintenir ce petit volume’’, afin de réécrire ma requête.

Tout cela je le vois bien dans mon explain plan alors que je ne le vois pas dans ma requête surtout si je suis amené à améliorer la performance d’une application dont je ne connais pas la nature et la répartition des données.

je répète que je parle de requêtes cohérentes.

[Pomalaix]

Envoyé par mnitu

Or la question ici est la suivante : comment écrire la requête d’une manière proactive, avant que le problème arrive ! Et peu importe l’explain plan.

Tu n'as que trop raison, écrire directement des requêtes efficaces, c'est le Graal, le but ultime, le rêve absolu.

Intellectuellement, la méthode par essais / corrections me désole, mais force est de constater que personne n'a encore trouvé le Graal (ou alors tu t'es bien gardé de nous en faire part). A part quelques réglounettes basiques, personne à ma connaissance ne propose rien de concret.

Ca me fait bien marrer de lire, régulièrement, que le SQL est un langage non procédural dans lequel il suffit de spécifier ce qu'on recherche, et non pas comment le trouver. Dès lors qu'on se préoccupe des performances, cette affirmation est une escroquerie, car il devient indispensable de comprendre la logique interne de l'optimiseur, c'est à dire son algorithme !

Face aux problèmes de performances, on peut distinguer plusieurs écoles, en particulier les suivantes :

1) la méthode que je dirais fonctionnaliste, qui reste au niveau SQL
Selon Stéphane Faroult dans "refactoring SQL applications"

I have never learned anything really useful from the analysis of an execution plan that execution time, a careful reading of statements, and a couple of queries against the data dictionary didn't tell me as clearly.

Autrement dit :

Je n'ai jamais appris quelque chose de véritablement utile de l'analyse d'un plan d'exécution, que le temps d'exécution, une lecture attentive des instructions [SQL] et quelques requêtes sur le dictionnaire de données ne m'avaient déjà dit aussi clairement.

2) Celle qui ne jure que par l'analyse des plans d'exécution
Dan Tow avec son "SQL Tuning" en est sans doute le représentant ultime, lui qui prétend qu'on peut en quelque sorte faire de l'optimisation symbolique, sans même comprendre les requêtes.

3) Celle qui ne jure que par l'analyse de la trace étendue (10046)

4) Une petite nouvelle, cheval de bataille de Grégory Guillou :

Zero SQL Tuning' va à l'encontre de nombreuses idées reçues. En pratique, la complexité liée à l'optimisation d'une requête SQL s'exécutant sur une base de données Oracle dépend très peu du nombre de ses jointures, du nombre de pages qui constitue son code, du nombre de vues qu'elle manipule, du nombre d'étapes de son plan d'exécution ou du type de ses opérations comme le “table access full”.

Il me paraît clair que les 3 premières constituent un zoom progressif, et qu'il convient d'en respecter la chronologie, en s'arrêtant à la première ou à la deuxième si c'est suffisant.

Quant à la quatrième, j'ignore ce qui se cache concrètement derrière...

[mnitu]

Mohamed,

La question est bien : comment je dois écrire cette requête pour qu’elle ne pose pas des problèmes des performances à l’exploitation de l’application. Encore plus précis : je peux obtenir le même résultat correct en écrivant N requêtes différentes et syntactiquement correctes. Est-ce que la performance est la même pour tout l’espace des requêtes possible ou pas ?

Il n’y a pas de plan d’exécution ! Il n’y pas de problème ! Il n'y pas de lenteur! Qu’est-ce que vous nous conseiller :

Vous pouvez écrire la requête comme bon vous semble, si la performance vous est acceptable vous vous arrêterez là...

ou

... à chaque fois que vous devez analyser la performance d’une requête SQL ou simplement lorsque vous voulez comprendre le choix fait par Oracle (CBO) pour exécuter votre requête vous devez passer par un explain plan. ...

ou quoi d'autre ?

[mnitu]

Cher Pomalaix,

Restons dans le concret, quelqu’un dit : voilà mes tables, voilà mes indexes, voilà ma volumétrie, voilà mes 3 requêtes (et il doit y avoir au moins 15 encore autres façons d'écrire la requête ...) que est-ce que j’ai dois choisir et vous me faite la théorie du Sant Graal. Moi je vois une requête basique est-ce qu’elle est complexe pour vous ?
J’ai la même question pour vous : dans ce cas qu’est-ce que vous nous recommandez ?

[Mohamed.Houri]

Pomalaix,

C'est bien écrit. C'est bien résumé.

Ca me fait bien marrer de lire, régulièrement, que le SQL est un langage non déclaratif dans lequel il suffit de spécifier ce qu'on recherche, et non pas comment le trouver

C'est comme quand vous arrivez dans une ville que vous ne connaissez pas. Vous dites au chauffeur de taxi, voici l'adresse prenez y moi le plus rapidement possible. Si le chauffeur connait très bien votre adresse il vous y emmènera en toute vitesse et cela ne vous coutera que le temps qu'a décompté le compteur. Si, par contre, le chauffeur ne connait pas le chemin le plus rapide il se baladera à droite et à gauche avant de vous emmener à destination mais à quel prix. C'est pour cela, que pour payer moins vous devriez donner au chauffeur de taxi le plus d'information possible concernant votre adresse, après laissez le faire son travail.

A un degré près que le CBO est honnête et que le chauffeur de taxi peut ne pas l'être

[mnitu]

Voilà, après la trace c’est maintenant la parabole des chauffeurs de taxi qui nous éclaira sur le sujet !

[Mohamed.Houri]

Envoyé par mnitu

Voilà, après la trace c’est maintenant la parabole des chauffeurs de taxi qui nous éclaira sur le sujet !

Pourtant elle ne vient pas de moi. Elle vient de quelqu'un que vous connaissez très bien et que vous appreciez enormément, j'en suis sûr.

[Mohamed.Houri]

Envoyé par mnitu

Voilà, après la trace c’est maintenant la parabole des chauffeurs de taxi qui nous éclaira sur le sujet !

J'ajouterai que lorsque Tom Kyte est venu à Paris en decembre 2008 et qu'il nous a parlé du désorde de sa fille dans sa chambre faisant en sorte qu'elle soit toujours en retard le matin pour aller à l'école et que si elle prenait le soin de ranger le soir chaque chose dans sa place elle mettra moins de temps pour les retrouver et ne sera pas en retard à l'école, et de finir par dire, c'est exactement le même cas pour les tables et les indexes, qu'allez vous dire dans ce cas?

Et voila la parabole de la fille en retard à l'école pour nous éclairer sur le sujet des indexes et des tables??

[mnitu]

Envoyé par Mohamed.Houri

J'ajouterai que lorsque Tom Kyte est venu à Paris en decembre 2008...

Posez-lui la exactement même question sur son site et donnez nous la réponse!

[Pomalaix]

Envoyé par mnitu

Restons dans le concret, quelqu’un dit : voilà mes tables, voilà mes indexes, voilà ma volumétrie, voilà mes 3 requêtes (et il doit y avoir au moins 15 encore autres façons d'écrire la requête ...) que est-ce que j’ai dois choisir et vous me faite la théorie du Sant Graal. Moi je vois une requête basique est-ce qu’elle est complexe pour vous ?
J’ai la même question pour vous : dans ce cas qu’est-ce que vous nous recommandez ?

La question qui est posée par le demandeur est celle-ci :

Ce poste est motivé par la volonté de comprendre pourquoi telle syntaxe est plus rapide qu'une autre (alors qu'on fait exactement la même chose) et comment tirer une règle générale lors de l'écriture des requêtes.

Moi je n'ai pas de règle magique à proposer, je fais partie de ceux qui pensent qu'il faut, malheureusement, en passer par le plan d'exécution.

Quant au saint Graal, il s'impose d'évidence dès lors que tu dis qu'il faut écrire la (bonne) requête de manière proactive.
Le petit bémol, c'est que tu oublies juste de proposer une solution !

[StringBuilder]

Ben moi qui m'attendait à ne pas avoir de réponse, ou une petite règle générale du genre "celle que tous tes collègues arrivent à relire", me voilà bien avancé

Le débat est tout à fait intéressant.

Pour en revenir à mes moutons, sachez que vous pouvez autant débattre que vous le voulez, finalement, mon problème ne se pose plus...

En avançant dans la mise en place de ma solution, j'ai dû passer par une bête jointure (sans distinct, ni exist ni rien) dans une vue, et c'est les critères appliqués sur la vue qui font le travail.

Non seulement le temps de réponse est raisonnable, mais en plus, l'optimisation du plan d'exécution ne se pose plus : les critères appliqués sur la vue sont dynamiques, et j'ai pu constater que le plan d'exécution était parfois très différent (dans certains cas, on commence par un full scan dans la table CNP -assez lent- et dans d'autres cas, on commence par un scan unique index dans CNT, ce qui est on ne peut plus rapide).