Discussion :

[kinettoman]

Bonjour à tous.

Tout d'abord, voici ce que j'ai cru comprendre sur les calculs de statistique sur des colonnes :
l'optimiseur oracle, pour calculer la pertinence de l'utilisation de telle ou telle restriction, ou de telle ou telle index, va les utiliser dans un ordre préférenciel de restrictivité (plus plein d'autre criteres). Il va pour cela se baser sur le nombre de valeurs distinctes de la colonne (sur la base de ses calculs de statistique précédent).
Dans le cas d'une colonne à faible cardinalité (par exemple 2 valeur distincte O ou N), il n'utilisera pas la restriction imédiatement, car celle ci ne lui semble pas assez restrictive. Pourtant, dans le cas ou les valeur ne sont pas équilibré (1% de O et 99 de N), il serait pour lui plus avantageux de le faire (si on fait une jointure avec la valeur la plus restrictive évidemment) .
Dans ce cas, on peut par exemple le forcer à utiliser un index sur cette colonne (via un hint), mais on peut aussi lui fournir l'information qui lui manque en lui faisant calculer la répartition effective des donnée dans la colonne par un "analyze MATABLE compute statistics for columns size 2 MACOLONNE".

Si j'ai effectivement bien compris l'utilisation de ces statistiques (mais rien n'est moins sur ), la deuxieme solution me semble -a priori- largement plus séduisante. Nous les avons utilisées dans un ou deux cas, et les gains ont été plus que notable (requete passant de 20 minute à 3 secondes !)


CEPENDANT, notre DBA à tempéré mon optimisme sur cette méthode en me disant qu'à sa connaissance, la généralisation de ces statistiques est à proscrire, et à n'utiliser qu'en solution de dépanage temporaire en attendant les corrections applicatives, et qu'il est recommandé de ne pas en utiliser plus de 1 ou 2 sur une instance donnée.
il n'a par contre pas su me donner la raison exacte pour laquelle ces statistiques serait à éviter (perf? stabilité? modification subite de comportement de l'optimiseur?).

Pouvez vous me donner votre expérience sur ce type de statistique et sur la manière dont vous les avez mis en place -ou pas.

Merci à ceux qui auront eu le courage de lire jusqu'au bout, et merci d'avance à ceux qui pourront me répondre

[kinettoman]

Un petit up, histoire surtout de savoir si ma question était suffisamment claire ?

Sinon, peut etre pourrez vous me donnez quelques tuyaux pour pousser plus loin mes recherches (forum ?, site spécialisés ?).

d'avance merci.

[orafrance]

d'abord, il faut préférer DBMS_STATS au bon vieu ANALYZE

Ensuite, orienter l'optimiseur grace aux analyses me parait surtout risqué dans le sens où c'est particulièrement bien caché. En effet, un hint ou le choix d'un index particulier et plus facile à repérer.

La meilleure des solutions pour s'assurer d'un plan d'exécution immuable et optimal, c'est d'utiliser les OUTLINES : http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/outlines.htm#13091

bon courage

[kinettoman]

Merci pour la réponse, mais qui malheureusement m'ammene à d'autres questions

D'abord, je n'ai pu ouvrir le lien du message sur les OUTLINES, êtes vous sur qu'il soit correct ?

Ensuite, vous dites :

d'abord, il faut préférer DBMS_STATS au bon vieu ANALYZE

D'apres mon dba, nous n'utilions pas le package DBMS_STATS pour cause de bug/incompatibilité(?) du package dans certaines version d'oracle (nous avons environ 200 clients, utilisant des versions d'oracle allant de la 7 à la 10). Est ce de la paranoia de sa part, ou êtes vous au courant de tels problèmes ?

Ensuite, orienter l'optimiseur grace aux analyses me parait surtout risqué dans le sens où c'est particulièrement bien caché. En effet, un hint ou le choix d'un index particulier et plus facile à repérer.

Certe, c'est effectivement bien caché, mais surtout c'est intrinseque à ORACLE. La question n'est elle pas tout betement de choisir entre une optimisation syntaxique et une optimisation statistique. Auquels cas, il faut effectivement que ces statistique soit le plus complete possible.

La meilleure des solutions pour s'assurer d'un plan d'exécution immuable et optimal, ...

C'est principalement là que je ne suis pas d'accord : un plan d'exécution immuable ne pourra pas être optimal dans tous les cas, car il dépend des données.

Pour clarifier ma vision du truc, je donne un petit exemple auquel nous avons été confronté, et qui nous a conduit vers ce type d'optimisation :
Pour nos éditions, nous nous basons sur une vue qui est une jointure entre une table DOSSIER et une table EDITIONDOSSIER (en simplifiant). Cette dernier table contient un champ EDITE à O ou N, avec quelques dizaine de N et plusieurs centaines de milliers de O.
Selon que cette vue est appelé dans un cadre d'édition (critere EDITE=N) ou de réédition (critere EDITE=O + autres critères de sélection sur la table DOSSIER), le plan d'exécution optimal est completement différent.
Qui plus est, dans le cadre d'une optimisation classique, la jointure EDITE=N n'est pas considéré comme assez restrictive par oracle (cardinalité 2 -> donc seulement 50% de restrictivité), et donc oracle partait en full acces sur notre table EDITIONDOSSIER et les perf était lamentables. Nous avions commencé par forcer le plan d'exécution en ajoutant des hint dans la vue pour obliger celle ci à se servir de l'index sur la colonne EDITE. Le resultat a été excellent en mode édition, mais lamentable en rééditon, car oracle utilisait alors le même plan d'exécution dans un contexte totalement différent. Lorsque nous avons créé un histogramme sur cette colonne EDITION, nous avons obtenu immédiatement un plan d'exécution DIFFERENT et OPTIMAL dans les deux contextes.
Certe, il est possible aussi d'utiliser deux requetes différentes correspondant chacune à un contexte spécifique, mais n'est ce pas dommage de s'astreindre a un tel travail, alors qu'oracle peut le faire de manière completement dynamique et transparante ?

Personnelement, je reste convaincu que si oracle ne fourni pas le meilleur plan d'exécution, il vaux mieux lui donner l'information qui lui manque que de le forcer par des hint ou autre.

Néanmoins, et c'était avant tout le but de mon post, je n'ai pas encore la certitude que l'utilisation d'histogramme soit sans danger pour les perf ou la stabilité. C'est pourquoi je voulais obtenir un retour d'utilisation de ces statistique par des personnes les ayant mise en place.

La théorie de mon DBA quand à ces stats (si j'ai bien compris ce qu'il m'a dit) c'est que comme le plan d'exécution dépend des données, oracle va stocker en SGA beaucoup plus de plans d'exécutions différents, au détriment du cache et donc des perfs. Qui plus est, il semblerait que la présence d'histogramme sur une table conduit oracle a ne tenir compte que de ceux ci et à ignorer la présence des stats classiques, bref, à utiliser un algoritme d'optimisation différent et donc potentiellement moins bon (?).

Voila, je crois que j'ai fait encore plus long que le premier coup, désolé.

Merci a tout ceux qui pourrons me corriger si j'ai dit des bétises, ou m'apporter les informations qui me manque pour me faire une opinion.

[orafrance]

D'abord, je n'ai pu ouvrir le lien du message sur les OUTLINES, êtes vous sur qu'il soit correct ?

C'est un lien vers la doc Oracle, il nécessite une inscription complétement gratuite et rapide

D'apres mon dba, nous n'utilions pas le package DBMS_STATS pour cause de bug/incompatibilité(?) du package dans certaines version d'oracle (nous avons environ 200 clients, utilisant des versions d'oracle allant de la 7 à la 10). Est ce de la paranoia de sa part, ou êtes vous au courant de tels problèmes ?

En effet, DBMS_STAT ne fonctionne pas sur toutes les versions. Néanmoins il peut être utilisé sans crainte depuis la 8i (8.1.7)

Certe, c'est effectivement bien caché, mais surtout c'est intrinseque à ORACLE. La question n'est elle pas tout betement de choisir entre une optimisation syntaxique et une optimisation statistique. Auquels cas, il faut effectivement que ces statistique soit le plus complete possible.

Non, le mode RULE est déconseillé dans TOUS les cas
Entre les hints, les outlines et les alter de session pour modifier le contexte, les outils sont suffisamment nombreux pour s'assurer de la qualité du plan

C'est principalement là que je ne suis pas d'accord : un plan d'exécution immuable ne pourra pas être optimal dans tous les cas, car il dépend des données.

Certes, mais il est des cas où c'est pourtant nécessaire. Par exemple, si il y a beaucoup d'indexes, la moindre fluctuation de volumétrie peut perturber défavorablement le plan. Par ailleurs, un très bon plan le reste quelque soit la volumétrie à de rares exceptions selon mon expérience personnel. Quand la volumétrie change au point de perturber les perfs, il faut revoir les indexes eux-même, mettre en place des purges ou partitionner entre autre.

Pour clarifier ma vision du truc, je donne un petit exemple auquel nous avons été confronté, et qui nous a conduit vers ce type d'optimisation :
Pour nos éditions, nous nous basons sur une vue qui est une jointure entre une table DOSSIER et une table EDITIONDOSSIER (en simplifiant). Cette dernier table contient un champ EDITE à O ou N, avec quelques dizaine de N et plusieurs centaines de milliers de O.

Est-ce bien un index bitmap sur EDITE ? Sinon, dans ce type de problèmatique on peut très bien imaginer une vue par type avec un UNION ALL, chacun des types peu alors être optimisé comme on veut

Personnelement, je reste convaincu que si oracle ne fourni pas le meilleur plan d'exécution, il vaux mieux lui donner l'information qui lui manque que de le forcer par des hint ou autre.

En effet, le hint est le dernier recours après : revue du modèle, revue du process, revue de code et revue des indexes.

oracle va stocker en SGA beaucoup plus de plans d'exécutions différents, au détriment du cache et donc des perfs.

Bah non, si le plan change le précédent est "flushé" si Oracle a besoin de mémoire

Qui plus est, il semblerait que la présence d'histogramme sur une table conduit oracle a ne tenir compte que de ceux ci et à ignorer la présence des stats classiques, bref, à utiliser un algoritme d'optimisation différent et donc potentiellement moins bon (?).

Moins bon, ce serait surprenant, mais pour le cas particulier dont il est question, en effet, Oracle aura du mal à avoir la même démarche intellectuel qu'un humain et risque donc de se tromper . N'oublions pas aussi qu'il est limité en nombre de permutation et que si il choisi un mauvais plan c'est peut-être que le nombre de permutations possible est insuffisant

Merci a tout ceux qui pourrons me corriger si j'ai dit des bétises, ou m'apporter les informations qui me manque pour me faire une opinion.

+1, je suis loin d'être infaillible

[kinettoman]

Merci pour la réponse rapide, je vais essayer "d'ingurgiter" tout ça