Discussion :

[macben]

Bonjour,

Je travaille sur une base de données infocentre sur lequel nous subissons aléatoirement de gros problèmes de dégradation de plans.

Des requêtes qui mettent 4 mins, vont du jour au lendemain passer à 6 heures, sans que rien ne le justifie (pas de recalcul de stats entre temps, plan validé dans la base line).

La seule solution que nous avons trouvée pour l'instant est de passer par un hint pour l'obliger à passer par les index clé primaires.

Et c'est cela qui nous étonne le plus. Par défaut le plan choisi fait un Full sur toutes les tables de la requête alors que les conditions de jointures sont correctes et faites sur les clés primaires.

En ajoutant les hints /*+ Index(table indexPK)*/ le temps passe à 2 mins ! De plus il faut pour chacune des tables indiquer l'index par lequel passer, sinon c'est un FULL qui est choisi.

Nous avons retourné le problème dans tous les sens impossible de comprendre :

- d'où vient ce changement de plan
- pourquoi il nous est impossible de faire choisir le bon plan naturellement (sans hint) à l'optimiseur.

A vrai dire nous ne savons plus trop par où prendre le problème et où regarder.

Si vous avez des idées, je ne met pas plus de précisions sur le paramétrage pour l'instant car je ne sais quoi mettre tellement le problème nous paraît insoluble.

Macben

[Mohamed.Houri]

Normalement l’Optimisateur d’Oracle(CBO) fournit le meilleur plan d’exécution possible s’il a une vue fidèle et représentative du volume de données que vous voulez rapatrier ainsi que de sa répartition dans la base de données (bien sûr sauf bug et parfois paramètres non adéquats du CBO). Prenez donc le problème d’une manière scientifique et commencez par poster le plan d’exécution de votre requête avec les estimations (E-Rows) faites par le CBO et les valeurs réelles (A-Rows). Utilisez pour cela le package dbms_xplan dont plusieurs exemples se trouvent déjà dans ce site. En procédant ainsi, vous aurez déjà accompli un pas : savoir si vos statistiques reflètent la réalité ou pas ?

D’ailleurs, à votre place, moi j’aurai tout de suite comparé le bon plan avec le mauvais à la recherche de la différence qui peut s’avérer être un problème de statistiques

[macben]

J'applique la méthode pour comparer avec dbms_xplan.

Concernant la comparaison, je l'ai effectuée, le mauvais fait un FULL TABLE sur les 8 tables en jointures, le bon plan fait un INDEX_PK sur chacune des 8 tables.

Je sais que mes statistiques ne sont pas fraîches (la volumétrie a augmenté) car les tables étant volumineuses leur rafraichissement est mensuel, mais dans tous les cas je ne comprend pas qu'un FULL soit privilégié à un INDEX_PK.

Je suis certainement exigeant avec lui mais là, quand même, l'optimiseur et moi on ne se comprend pas.

[mnitu]

En plus de ce que Mohamed vous a déjà recommandé, faite une trace de l’optimiseur (événement 10053) pour essayer de trouver des indices en ce qui concerne ses choix.
Mais je commencerais aussi par l’étude de la requête, de son plan d’exécution, des statistiques, des paramètres de l'optimiseur et des données.

[mnitu]

...je ne comprend pas qu'un FULL soit privilégié à un INDEX_PK.

Si un full est plus efficace pourquoi pas ? La question est plutôt pourquoi croit-il que c’est plus efficace quand la réalité démontre le contraire.

[Mohamed.Houri]

dans tous les cas je ne comprend pas qu'un FULL soit privilégié à un INDEX_PK.

Ah que la Philosophy numéro 15 de Jonathan Lewis sied parfaitement à votre question.

Allez au commentaire 7 pour trouver la traduction en Français de cette philosophie 15

[mnitu]

All is takes is one row (that the optimizer knows about) to change from one plan to another ...

Je sais que mes statistiques ne sont pas fraîches (la volumétrie a augmenté) car
...

Bon il reste un niveau de dynamic samping plus agressif [edit] et
"How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? " [/edit]