Discussion :

[remi4444]

... il ne restera que cette f*** requête qui ramera malgré tout nos efforts parce qu'un jour, quelqu'un aura pris la décision de mettre un hint qui ne sera plus du tout d'actualité !...

Mais là on entre dans un autre débat qui est celui de la compatibilité ascendante des produits... peut etre aussi qu'il y aura des instructions PL/SQL qui ne seront plus d'acutalité, peut-etre qu'il y aura des syntaxes sql qui ne seront plus d'acutalité etc... etc...
On s'en sort plus si on s'interdit d'utiliser des outils par peur qu'ils ne fonctionnent plus un jour. Les HINT ont une utilité concrète et logique, ils sont recomandés, documentés, utilisés à fond dans les outils oracle etc... si leur pérénité était menacée, je pense qu'oracle le signalerait quelques versions auparavant. Je pense qu'il serait suicidaire de leur part de faire une rupture dans la compatibilité à ce niveau là.

Encore une fois, si on fait une utilisation irrationnelle des hint, alors oui on s'expose à des dangers, mais ce à n'importe quel moment. De toutes façons, si on ne comprend pas un plan d'exéction de manière générale (pour une requête complexe) on s'expose déja à de l'instabilité.
Pour mettre un HINT, il faut respecter plusieurs principes:
- comprendre le plan d'exécution original
- avoir une idée précise du plan d'exécution qu'on veux obtenir
- comprendre surtout pourquoi oracle ne choisi pas le bon plan
- comprendre ce que fait le HINT
- et comprendre pourquoi le HINT va aiguiller oracle dans le bon sens

Personnellement, je préfère assurer une stabilité des requêtes sensibles dans l'utilisation normale d'une application, et je prends le pari de la compatibilité ascendente d'oracle (et des autres produits), mais ce n'est que mon avis...

[LeoAnderson]

je ne parle pas des hints qui ne seront plus reconnus, je parle du cas où forcer l'utilisation de tel index peut s'avérer désastreux car le contexte aura changé !
tu développes sur une base de 10 Mo, tu as d'excellentes perfs avec ton hint. Tu l'implémentes.
Ton appli passe en prod.

Le volume passe de 10 Mo à 100 Mo... le DBA tune la base, ça tient le coup
Le volume passe à 500 Mo. là, on ne peut plus rien faire. ça rame. tu l'as dans l'os. parce qu'utiliser l'index n'est maintenant plus du tout rentable !!

donc, figer, en développement le plan d'exécution alors que les données vont vivre, c'est une hérésie.

[nuke_y]

Je suis assez d'accord, partant du principe que si il y a besoin de mettre des hints dans une appli dès son développement, c'est que quelquechose cloche dans la conception.

Est-ce qu'il y a des cas où il n'y a pas vraiment moyen de faire sans les hints dès la phase de développement ?

[remi4444]

On va avoir du mal à s'en sortir si on reste dans un débat de principe. Je n'ai jamais dit qu'il falait utiliser les hint à tout prix, j'ai juste dit que pour certains cas bien particuliers ou l'on sait que la répartition des données est faite d'une certaine manière, ça pouvait etre une solution tout à fait défendable.

Ton exemple peut tout à fait servir les 2 argumentations, car si c'est la table de pifor qui passe de 10M à 500M, le full scan qui était anodin au départ devient catastrophique à l'arrivée.
De toutes manières si tes données sont complètement bouleversées à un moment donné, tu ne vas pas pouvoir échaper à un réexamen de l'optimisation, parceque ça va craquer à plusieurs endroits...

Je ne dis pas que le cas que tu donnes ne peut pas arriver, mais d'expérience j'ai déja travaillé sur des applications tres grosses et tres sensibles, je n'ai jamais eu à enlever un seul hint qui avait été rajouté les années précédentes. Mais évidement, le hint était utilisé avec parcimonie pour des cas bien étudiés compris et maitrisés.
J'ai par contre vu des catastrophes dues à des calculs d'histogrammes qui avait été stoppés par inadvertance, ou des stats passées à un moment ou certaines tables de travail étaient vides (rien de pire que des stats archi fausses!)

Dans tous les cas, effectivement ce n'est pas au développeur de mettre des hint partout d'entrée, le développeur doit faire des requêtes les plus logiques possibles en utilisant les instructions qui collent au plus pres de son besoin.
Exemple: S'il veux ramener les lignes d'une table par rapport à des critères pris dans une autres table, il faut utiliser les EXISTS et non une jointure avec un vilain DISTINCT au dessus.

Il faut qu'il respecte aussi quelques règles simples comme par exemple éviter de faire un "where" discriminant sur un calcul. A son niveau il peut éventuellement identifier des colonnes supplémentaires à indexer.
Ensuite il faut tester les modules avec un gros volume de données (chose qui est trop souvent oublié...). Ensuite seulement, on optimise plus finement par réécriture de requête, histogrammes et/ou des hint.
Dans tous les cas, une fois la mise en prod effectuée, il faut toujours surveiller car il se présente toujours des cas imprévus à optimiser...

[remi4444]

Je suis assez d'accord, partant du principe que si il y a besoin de mettre des hints dans une appli dès son développement, c'est que quelquechose cloche dans la conception.

Pas forcément, si on s'amuse à tracer toutes les requêtes de toad ou meme des outils d'oracle, on vois que les hints sont trés souvent utilisés, j'ose espérer qu'il ne sont pas trop trop nuls en conception...

Il faut bien comprendre que le CBO, c'est un concepteur automatique d'algorithme, c'est quasiement de l'intelligence artificielle, mais ce n'est pas une baguette magique ni une boule de cristal...

Est-ce qu'il y a des cas où il n'y a pas vraiment moyen de faire sans les hints dès la phase de développement ?

Des hints qu'on peut utiliser sans trop commetre d'hérésie ()seront par exemple les très généraux "ALL_ROWS" ou "FIRST_ROWS" puisque ça indique à oracle simplement l'intention qu'on a dans l'utilisation des données.

On peut éventuellement anticiper dans des cas comme celui de pifor. Par exemple, on sais applicativement qu'une inégalité ne ramènera toujours que très peu de lignes.

Aussi on peu anticiper l'accès à une colonne avec une répartition très hétérogène. Par exemple une table où sur 100 000 lignes, une valeur de colonne se retrouve identique pour 99 900 lignes et qui prend des valeurs diverses pour les 100 lignes qui restent (un statut de traitement par exemple). Si les stats sont passées sur l'index (sans histogramme) alors oracle va systématiquement discalifier l'index sur tous ses plans. Lorsqu'on requête sur une valeur qu'on sais rare, il est concevable de forcer le passage par l'index parcequ'on sais que la cardinalité est faible (et pas oracle)...
A noter que sur ce dernier cas, c'est le passage de stats qui peut provoquer le "mauvais" plan , c'est très facheux car ça peut amener certains à effacer les stats pour débloquer une situation... et aprés bonjours la galère pour expliquer le bien-fondé du passage de stats...

[nuke_y]

Pas forcément, si on s'amuse à tracer toutes les requêtes de toad ou meme des outils d'oracle, on vois que les hints sont trés souvent utilisés, j'ose espérer qu'il ne sont pas trop trop nuls en conception...

Il faut bien comprendre que le CBO, c'est un concepteur automatique d'algorithme, c'est quasiement de l'intelligence artificielle, mais ce n'est pas une baguette magique ni une boule de cristal...

Dans ce cas là, il faut vraiment considérer les hints comme une fonctionnalité à part entière, avec ses cas d'utilisation, ses pièges, etc. Mais le problème c'est que cette fonctionnalité est rarement intégrée dans des outils tiers ou dans des applications.

Ce que disait LeoAnderson est d'autant plus juste alors : un hint ok, mais avec la possibilité de le modifier/désactiver sans avoir à recompiler l'application.

A noter que sur ce dernier cas, c'est le passage de stats qui peut provoquer le "mauvais" plan , c'est très facheux car ça peut amener certains à effacer les stats pour débloquer une situation... et aprés bonjours la galère pour expliquer le bien-fondé du passage de stats...

Je confirme, c'est le problème que j'ai en ce moment...

[LeoAnderson]

attention : je ne critique pas les hints, je critique la lourdeur qu'ils ont quand on ne peut pas les enlever et qu'ils ont été utilisés à mauvais escient par un développeur qui ne maitrisait pas tous les tenants et aboutissants.

C'est dans ce sens que j'émettais de vive réserves.

Je ne voudrais surtout pas qu'un lecteur peu averti reste sur la conclusion "ça va aller mieux si je lui mets un hint" !

Quant aux exemples que tu prends (Toad et Oracle), les tables qu'ils manipulent (celles du dictionnaires n'évoluent pas, leur volumétrie est sensiblement identique, dans chaque installation).
Ce n'est pas du tout le cas pour une application de gestion par exemple. Un site aura beaucoup de devis, tandis qu'un autre ne fera que des factures sans devis.

[remi4444]

attention : je ne critique pas les hints, je critique la lourdeur qu'ils ont quand on ne peut pas les enlever et qu'ils ont été utilisés à mauvais escient par un développeur qui ne maitrisait pas tous les tenants et aboutissants.

C'est dans ce sens que j'émettais de vive réserves.

Je ne voudrais surtout pas qu'un lecteur peu averti reste sur la conclusion "ça va aller mieux si je lui mets un hint" !

Je suis d'accord.
De manière générale, il faut s'abstenir d'utiliser quelque chose qu'on ne maitrise pas... j'enfonce une porte ouverte en disant ça mais malheureusement, beaucoup se font pieger par l'apparante simplicité du SQL...

[sygale]

Bonjour, je viens de finir de lire ce super post, où j'ai appris beaucoup de chose.

Personne n'aurait un peu de temps libre pour faire un tuto sur les plan table, les hints etc ??

Cela serait très pratique, car il n'y a pas tant de monde que ca qui comprenne vraiement tout (moi le premier )

En tout cas merci pour ce cours fort intéressant !

PS je connais la doc Optimisation ORACLE mais elle est tres générale et présente "des pistes à explorer". Mais elle a le mérite d'exister !

[orafrance]

c'est dans les cartons

Pour amener de l'eau au moulin, l'optimiseur Oracle ne peut malheureusement pas répondre à toutes les situations. Ainsi, il se trouve qu'on peut être amener à orienter l'optimiseur. Pour se faire il n'y a que 3 solutions : changer l'optimizer_mode, les hints ou les outilnes et malheureusement aucune des 3 solutions n'est complétement satisfaisante. Bon... il y a aussi la solution de changer de requête ou d'autres paramètres de la base mais je schématise hein

Donc, pour en revenir à ce que chacun disait, les hints sont UNE solution parmis d'autres, elle ne doit pas être écartée à tout prix et ne doit pas non plus être la solution prioritaire. En effet, un mauvais plan peut être le symptome d'un problème plus profond (stats mal calculé, conception à revoir, index à redéfinir, etc...).

Sinon, j'ai pas lu dans le détail, mais concernant l'index sur une date, la conclusion est-elle bien que l'index est utilisé si aucune fonction n'est appliquée à la colonne indexée et qu'il est suffisamment intéressant pour justifier son utilisation ?

[remi4444]

Sinon, j'ai pas lu dans le détail, mais concernant l'index sur une date, la conclusion est-elle bien que l'index est utilisé si aucune fonction n'est appliquée à la colonne indexée et qu'il est suffisamment intéressant pour justifier son utilisation ?

Pour résumer, le problème n'est pas du au fait que ce soit physiquement une date ou pas, le problème est celui des inégalités. Sans histogramme ni hint, une inégalité sera le plus souvent considérée comme non discriminante, donc l'index ne sera à priori pas utilisé...
Il se trouve que c'est souvent sur les dates qu'on à tendance à faire des inégalités ou encadrements voilà tout...

[orafrance]

ha oui en effet... merci