Mettre le doigt sur un problème de performances

[StringBuilder]

Bonjour,

Je travaille depuis quelques mois chez un client.

Le SGBD est "Oracle Database 10g Release 10.1.0.5.0 - Production".

Le modèle des données est un modèle éditeur.
Etant expert sur le logiciel, je maîtrise parfaitement ce modèle des données.

Parmi les dizaines de clients chez qui je suis intervenu, c'est la première fois que j'ai autant de problèmes de performances.

Le client, qui se vexe lorsque je lui fait remarquer ces problèmes me rétorques :
- Que leur base de données est immense
- Qu'il y a énormément de traitements dessus

Ça, c'est vrai, il s'agit d'une base volumineuse, et entre les traitements interactifs et les traitements batch, Oracle ne chôme pas une seconde.

Mais malgré ça, je persiste et signe : le serveur se comporte anormalement.

Il utilise par exemple les index n'importe comment : lors d'une jointure sur une FK, il va par exemple parfois préférer faire des nested loop sur un full table scan plutôt qu'un index scan...

Du coup, d'une requête à l'autre (avec pour ainsi dire aucune modification), on passe de quelques millisecondes d'exécution à plusieurs heures.

Aussi, phénomène que je n'ai pas vu depuis Oracle 7, l'optimiseur est sensible à l'ordre des tables dans la clause FROM :

Code :

1
2
3
4
5
6
 
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.fk
INNER JOIN table3 t3 ON t3.id = t2.fk
INNER JOIN table4 t4 ON t4.id = t1.fk

=> table4 est lu à grands couprs de full table scan

Alors que :

Code :

1
2
3
4
5
6
 
SELECT *
FROM table1 t1
INNER JOIN table4 t4 ON t4.id = t1.fk
INNER JOIN table2 t2 ON t2.id = t1.fk
INNER JOIN table3 t3 ON t3.id = t2.fk

=> Tout fonctionne à merveille

Comment expliquez-vous ce phénomène ?

Un DBA vient une fois par mois regarder la base. J'aimerais pouvoir le coincer avec une étude un peu approfondie en lui disant "ben vérifie ceci, parce que visiblement ça va pas du tout"

Parmi les pistes que j'ai il y a le recalcul des index : ils sont calculés tous les dimanches soir... Alors que toute la semaine, il y a des gros traitements qui modifient plus de 50% des lignes des plus grosses table... sans recalcul des index !

Mais ceci n'explique pas le problème d'ordre des table cité ci-dessus...

[zinzineti]

Envoyé par StringBuilder

Bonjour,

Code :

=> table4 est lu à grands couprs de full table scan

Alors que :

Code :

=> Tout fonctionne à merveille

Comment expliquez-vous ce phénomène ?

C'est peut être lié aux index créés sur les tables concernées.

Envoyé par StringBuilder

Un DBA vient une fois par mois regarder la base. J'aimerais pouvoir le coincer avec une étude un peu approfondie en lui disant "ben vérifie ceci, parce que visiblement ça va pas du tout"

Tu peux lui présenter clairement le problème et solliciter son aide pour qu'ensemble vous puissiez trouver une solution aux problèmes de performances. Tu peux essayer de communiquer avec lui (avant qu'il ne passe sur le site) sur les difficultés que tu rencontres. Il peut peut être de guider sur ce qu'il faut faire avant son passage hebdomendaire

Envoyé par StringBuilder

Parmi les pistes que j'ai il y a le recalcul des index : ils sont calculés tous les dimanches soir... Alors que toute la semaine, il y a des gros traitements qui modifient plus de 50% des lignes des plus grosses table... sans recalcul des index !
Mais ceci n'explique pas le problème d'ordre des table cité ci-dessus...

Demande au DBA s'il n'est pas possible de reprogrammer les opérations de maintenances : faire des opérations de maintenance de façon quotidienne au lieu d'une fois par semaine.

Bref, il faut communiquer avec le DBA de la solution.
Un bon relationnel avec ce DBA te fera gagner du temps et d’énergie

A+

[ORA-007]

ils sont calculés tous les dimanches soir... Alors que toute la semaine, il y a des gros traitements qui modifient plus de 50% des lignes des plus grosses table... sans recalcul des index !

Peut etre qu'un audit type Statspack le lundi en comparaison avec un du samedi pourrait valider / invalider cette piste.

[mnitu]

Commencez par tracer et analyser. Dans une de ses interventions Mohamed Houri avait posté deux lien qui vous seront utilies pour avancer.

[Marc Musette]

Vérifie que les statistiques sont à jour pour le schéma de ton appli .

Normalement en 10g, un job automatique GATHER_STATS_JOB tourne quotidiennement pour réaliser cette tâche. Vérifie qu'il est bien activé.

[Mohamed.Houri]

Lorsque votre DBA viendra la prochaine fois ausculter la base de données montrez lui alors des faits. Comment? En me basant sur ce que vous avez déjà donné comme information, à votre place je ferai ce qui suit (a) si une requête change de temps d'exécution d'un moment à un autre c'est que son plan d'exécution a changé. Il faut alors prendre le plan d'exécution d'avant et d'après. Et demandez-lui d'analyser avec vous les raisons qui ont amené votre plan d'exécution à changer. Il peut y avoir plusieurs raisons; elles s'y trouveront et (b) l'optimisateur (CBO) est plus sensible aux statistiques dont il dispose qu'il ne l'est pour l'ordre des tables dans la clause FROM. C'est pour cette raison qu'il faut générer un explain plan pour ces requêtes que vous citez et voir pourquoi le CBO choisit NESTED LOOP. A ce sujet, en utilisant le

Code :

1
2
 
dbms_xplan.display_cursor(NULL,NULL, 'ALLSTATS LAST')

vous allez avoir les estimations(E-Rows) faites par le CBO et les calculs réels (A-Rows) ainsi que le nombre de fois que le CBO a exécuté chaque opération (Starts); une différence entre A-Rows et E-Rows/Starts représente un symptôme de statistiques non à jour. Il faudra alors penser à recalculer les statistiques dans ce cas.

L’explain plan, grâce à sa partie predicate, montre souvent aussi pourquoi les indexes ne sont pas utilisés ou ne sont pas assez précis à cause d’une double opération, access + filter, au lieu d’un simple access.

Si par contre, c’est toute l’application qui parfois se met à fonctionner lentement, sans que vous ne sachiez d’où cela provienne, alors le mieux, comme toujours, c’est d’avoir un rapport AWR ou Statspack correspondant à la période où cela n’allait pas très bien et un autre ou cela allait bien.

Un dernier conseil, sur quel base avez-vous pris la décision de recalculer (rebuild je suppose) les indexes ? Si vous avez fait cela sans vraiment avoir une raison valable au préalable, vous pouvez lire cet article de Jonathan Lewis sur l'altération possible de l’efficacité des indexes au cours de leur vie:

http://jonathanlewis.wordpress.com/2...-efficiency-3/

Vous y trouverez certainement plus d’information sur les indexes qui peuvent bénéficier d’une réorganisation. Lorsque je dis réorganisation, cela ne veut pas dire exclusivement un rebuild ; mais cela peut aussi être un coalesce. Surtout pour les indexes qui sont périodiquement vidés sur leur droite et qui continue à être remplis uniquement sur leur gauche comme cela semble être manifestement le cas de votre application.

[pachot]

Bonjour,

Aussi, phénomène que je n'ai pas vu depuis Oracle 7, l'optimiseur est sensible à l'ordre des tables dans la clause FROM :

On pourrait commencer par là. Un explain plan pour les 2 requêtes.

Code :

1
2
3
4
5
6
7
8
EXPLAIN plan FOR  
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.fk
INNER JOIN table3 t3 ON t3.id = t2.fk
INNER JOIN table4 t4 ON t4.id = t1.fk
/
SELECT * FROM TABLE(dbms_xplan.display);

Que le plan change suivant l'ordre, c'est pas normal. Mais qu'il choisisse un full scan où est le problème ?

Cordialement,
Franck.