problème de perfs sur une base 10g

[cyclone_yas]

Bonjour à tous,

je ne suis pas DBA, je suis un simple utilisateur d'une base qui représente d'enormes faiblesses et de problèmes de perfs.
je pense que la base est mal paramétrée, je sollicite votre aide.

je scinder ma question en deux :

1- Est ce normale de recalculer, toutes les semaines, les stats sur une base ?
je précise que la nature des données qui s'y rajoute toutes les semaine est identique, et que la taille n'est pas exorbitante.

2- le simple fait de rajouter une colonne (à null) dans une table fait qu'une requete sur cette table passe de 0.2 à 3 secondes.
le choix du plan d'execution est extrêmement sensible aux modifications des tables, d'ou l'obligation de recalcul des stats tous les weekend
à votre avis, quel paramètre de la base est à vérifier ?


Je précise une dernière chose, le re-calcul des stats est indispensable après chaque MEP comportant des modifications dans la base, cela contraint les MEP à être passées les weekend !!

version d'oracle : 10.2.04

Mille merci

[Mohamed.Houri]

En général, le CBO a besoin d’avoir des statistiques le plus à jour possible afin de générer un chemin d’exécution rapide et efficace. Il est donc tout-à-fait logique que lorsque les données changent, le CBO doit être avertit de ce changement via un calcul des statistiques. Mais attention, il y aussi d’autres avis d’experts qui sont, pour ainsi dire, moins enclins à rendre le calcul des statistiques systématique avec le changement des données ; ils disent ceci ''tant que la performance est bonne il est inutile de calculer les statistiques''

Je pense que nous devrions nous situer entre les deux. A savoir avoir une stratégie qui indique le moment du calcul des statistiques. Ceci dit, il y a quand même quelques règles à connaitre quand il s’agit du calcul des statistiques

1. Dans un environnement OLTP, ne pas calculer les statistiques avec histogram sauf si c’est vraiment nécessaire
2. Bien surveiller les indexes volumineux, ceux basés sur des séquences ou ceux basé sur une date ; i.e les indexes ayant des données uniquement sur leur droite et qui sont vides sur leur gauche
3. Bien surveiller les indexes basés sur un Flag (Y/N)
4. Bien faire attention au changement des basses et hautes valeurs (low/high value) qui peuvent induire le CBO en erreur.

Maintenant, je reviens à vous en vous posant la question suivante : est ce le calcul des statistiques qui détériore la performance ou bien l’ajout d’une colonne dans une table ?
Si le problème de performance n’est pas dû au calcul des statistiques, alors la question ne se pose pas pour vous. Si au contraire à chaque fois que vous calculez les statistiques vous avez des problèmes de performance, alors, dans ce cas, la première des choses à faire c’est (a) de remettre les anciennes statistiques qu’Oracle enregistre et sauve avant de calculer les nouvelles (b) c’est l’occasion pour vous d’investiguer afin de voir pourquoi les nouvelles statistiques génèrent un chemin (explain plan) différent.

Lorsque vous ajoutez cette colonne dans une table, est ce que vous l’ajoutez aussi dans certains indexes qui existent déjà ? Ceci a une grande importance en effet. Car si vous avez un index sur une colonne et que vous lui ajoutiez une deuxième colonne ceci peut avoir comme conséquence que le clustering factor de cet index se détériore faisant en sorte que la désirabilité de cet index par le CBO se détériore et il ne sera plus utilisé. J’ai traduit en français le chapitre 5 du livre de Jonathan Lewis (Cost Based Oracle Fundamentals) que vous pouvez télécharger gratuitement du site Apress et dans lequel vous allez tout trouver sur le clustering factor.

http://jonathanlewis.wordpress.com/2...tering_factor/

[cyclone_yas]

Merci infiniment Mohammed pour votre réponse.
Maintenant j'ai des pistes pour investiguer.

Le problème est flagrant ! ce qui me fait penser qu'il s'agit bien d'un souci au niveau du paramétrage de la base
En fait, la modification d'une table entraine systématiquement la dégradation des perfs, y a que le recalcule des stats qui remédie à cela.

la colonne que je viens de rajouter n'a pas à être indexée ni à faire partie d'un indexe
le simple ajout de cette colonne, dégrade obligatoirement les perfs

est ce qu'il y a un paramètre à revoir, une option à mettre à jour qui permettront de remettre à jour ses stats automatiquement ?

encore merci pour l'excellent document

[pachot]

Bonjour,

C'est difficile d'être catégorique sur le calcul de stats.
Soit on ne les calcule pas assez souvent, et les plans d'exécution ne sont plus adaptés à la réalité des données: requêtes pas optimales.
soit on les calcule souvent et les plans changent souvent: instabilité.

Mon avis:
- si les performances sont acceptables (par rapport au besoin) alors ne rien calculer pour ne rien changer (le mieux est parfois l'ennemi du bien)
- si les performances se dégradent et que l'analyse montre que c'est dû à une augmentation du volume de données, et que les plans d'exécution ne sont pas les meilleurs pour ce nouveau volume, alors les recalculer et être attentif à toute dégradation.
- si on ne veut pas faire cette analyze, alors s'appuyer sur le job auto et gérer les exceptions lorsqu'on rencontre des problèmes.

1- Est ce normale de recalculer, toutes les semaines, les stats sur une base ?
je précise que la nature des données qui s'y rajoute toutes les semaine est identique, et que la taille n'est pas exorbitante.

Donc ni anormal, ni normal. En tout cas, si les volumes restent identiques et que la taille n'est pas énorme, le recalcul de stats ne devrait pas tou bouleverser. Sinon, il faut investiguer pourquoi.

2- le simple fait de rajouter une colonne (à null) dans une table fait qu'une requete sur cette table passe de 0.2 à 3 secondes.

Je ne vois aucune raison à celà: rajouter une colonne sans valeur ne change vraiement pas grand chose, ni aux données, ni aux stats.

Par contre, le recalcul de stat aussi bien que des alter tables vont invalider le curseur (et le plan d'exécution qui avait été déterminé par l'optimizeur) et la prochaine exécution va ré-optimiser la requête. Et il fait ça en fonction des valeurs qui sont passées à la première exécution (bind variable peeking). Ce qui veut dire:
- si les données ont des répartitions très inégales par rapport aux valeurs qui sont passées (par exemple, une appli qui gère aussi bien des clients qui passent une commande par and que des clients qui passent 10000 commandes par jours)
- et si les requêtes en question utilisent des bind variables
- et qu'il y a des histogrammes sur les colonnes en question

alors, chaque fois que le requête est ré-optimisée, elle peut choisir un plan complètement différent.
Les solutions peuvent être:
- soit ne pas faire des requêtes trop génériques avec trop de variables, mais traiter de manière différente les cas différents
- soit ne pas avoir d'histogrammes si les colonnes ont des prédicats par bind variables

C'est juste une idée d'après les symptômes décrits. a confirmer avant de faire quelque chose...

Cordialement,
Franck