Régression de perfs suite migration oracle 10g/11g

[devkais]

Bonjour,

Je me permets de vous contacter pour un avis d'Experts en bases de données Oracle.

Je travaille sur une migration oracle 10g vers 11g sur une base de 10 To !
Pour des raisons de gain de temps et de stabilité des plans d’exécution les statistiques sont FIGEES depuis la 8i en 2004 (même lors de la migration précédente 9i/10g).

Les temps de réponses 11g (base de test à volumètrie réelle) présentent une régression par rapport à la 10g !

Il faut remédier à cela sans recalculer les stats en 11g(une vraie contrainte).
Le code de l'application est bourré de hints pour forcer l'optimiseur ...

Faut-il forcer le calcul des stats en 11g après avoir inhibé tous les hints dans le code de l’application ? 3000 hints environ !
Sinon quel est le risque si on garde les anciens stats et on analyse traitement par traitement ?

Merci par avance pour votre aide.

Kais

[Pomalaix]

Envoyé par devkais

Pour des raisons de gain de temps et de stabilité des plans d’exécution les statistiques sont FIGEES depuis la 8i en 2004 (même lors de la migration précédente 9i/10g).
...
Il faut remédier à cela sans recalculer les stats en 11g(une vraie contrainte).
...
Faut-il forcer le calcul des stats en 11g après avoir inhibé tous les hints dans le code de l’application ? 3000 hints environ !

Il y a en 11g différents outils, nouveaux ou non, qui faciliteront vos tests pour sortir de ce que je considère comme une fuite en avant (en plutôt en arrière).

1) désactivation globale des directives d'optimisation (les hints), à titre de test :

Code :

ALTER session SET "_optimizer_ignore_hints" =true;

2) statistiques "privées"
- vous calculez les stats avec l'option PUBLISH=FALSE

Code :

1
2
exec dbms_stats.set_database_prefs('PUBLISH', 'FALSE');
exec dbms_stats.gather...

- vous activez ces statistiques uniquement dans la session de test

Code :

ALTER session SET optimizer_use_pending_statistics=true;

- si elles s'avèrent bénéfiques, vous les publiez pour tout le monde

Code :

EXEC DBMS_STATS.PUBLISH_PENDING_STATS(NULL, NULL);

3) émulation d'un optimiseur de version inférieure, par exemple

Code :

ALTER session SET optimizer_features_enable='8.1.7';

4) fonctionnalité "sql performance analyzer" appartenant à l'option payante Real Application Testing, qui permet de comparer les performances individuelles d'un jeu de requêtes avant et après modification (ici le changement de version)

5) "sql plan management", qui fournit une sorte d'assurance anti régression, grâce à des plans de référence qu'on peut transférer depuis la V10 par exemple


De plus, il se pourrait, d'après ce que vous décrivez, que vous traîniez dans votre INIT.ORA V11 des vieux paramètres qui sont désormais complètement inadaptés.

[Mohamed.Houri]

Envoyé par devkais

Bonjour,

Je me permets de vous contacter pour un avis d'Experts en bases de données Oracle.

Je travaille sur une migration oracle 10g vers 11g sur une base de 10 To !
Pour des raisons de gain de temps et de stabilité des plans d’exécution les statistiques sont FIGEES depuis la 8i en 2004 (même lors de la migration précédente 9i/10g).

Les temps de réponses 11g (base de test à volumètrie réelle) présentent une régression par rapport à la 10g !

Il faut remédier à cela sans recalculer les stats en 11g(une vraie contrainte).
Le code de l'application est bourré de hints pour forcer l'optimiseur ...

Faut-il forcer le calcul des stats en 11g après avoir inhibé tous les hints dans le code de l’application ? 3000 hints environ !
Sinon quel est le risque si on garde les anciens stats et on analyse traitement par traitement ?

Merci par avance pour votre aide.

Kais

Ce qui semble le plus simple et le moins risqué pour vous dans le cas que vous présentez ici est de faire, comme l'a déjà conseillé pomalaix, ceci:

Code :

1
2
 
11g> ALTER session SET optimizer_features_enable='10.2.0.4.0'; -- votre version 10g

Si, avec ceci vous arrivez à retrouver une performance acceptable, alors vous avez un répis. Je dis répis car je ne pense pas que vous puissiez naviguer d'une migration à une autre en maitenant toujours l'optimisateur(CBO) de la dernière version. A chaque migration des améliorations sont apportées au CBO (des bugs aussi) et vous devriez déployer des efforts afin d'utiliser la dernière version du CBO.

Utilisez donc ce répis afin de tester abondamment la nouvelle version en environnement de test. A chaque fois que vous être confronté à un problème de performance dans la nouvelle version, appliquer le code ci-dessous, générez les deux plans d'execution (celui avec le nouveau CBO et celui avec l'ancien) et comparez.

[pachot]

Bonjour,

Le code de l'application est bourré de hints pour forcer l'optimiseur ...

Quels types de hints ? Beaucoup changent de comportement d'une version à l'autre. D'autres non.

Sinon quel est le risque si on garde les anciens stats et on analyse traitement par traitement ?

Le problème, c'est que vous allez vous retrouver à tuner des requête en sachant très bien que les stats sont fausses...

Pour des raisons de gain de temps et de stabilité des plans d’exécution les statistiques sont FIGEES depuis la 8i en 2004 (même lors de la migration précédente 9i/10g).

Ce n'est pas la bonne solution pour figer les plans d'exécution...

Je pense que la bonne approche serait d'utiliser les SQL baselines (SQL Plan Management) - nécessite Tuning Pack - pour assurer la stabilité des plans:
1. creer des baselines fixes avec les plans de la 10g: soit avec SQL Tuning Sets à partir de la 10g, soit avec capture en ayant mis optimizer_features_enable à la 10g.
-> là normalement pas de regression car les anciens plans sont utilisés
2. calculer les stats
3. vérifier l'évolution des plans d'exécution. Accepter les plans meilleurs. Et lorsque les nouveaux plans (avec bonnes stats) sont moins bons, réfléchir individuellement à chaque requête.

Biens sûr, c'est tout un projet...

Cordialement,
Franck.

[devkais]

Bonjour ,
La plus part des hints sont :
ORDERED
USE_HASH
USE_NL
NO_MERGE
INDEX
NO_QUERY_TRANSFORMATION
...

Pensez-vous que recalculer les stats 11g est un grand risque dans mon cas ?

Quelles sont les conséquences si on garde la même technique de stats figées 8i sur une base 11g !

Merci.

[pachot]

Non, je ne pense pas que recalculer les stats présente un grand risque. surtout s'il y a des hints pour forcer les plans de toute façon.
De toute façon, en 11g c'est facile de tester les stats avant. voir le post de Pomalaix.

[Mohamed.Houri]

Envoyé par devkais

Pensez-vous que recalculer les stats 11g est un grand risque dans mon cas ?

Quelles sont les conséquences si on garde la même technique de stats figées 8i sur une base 11g !

Dans la version 8i les statistiques ont certainement été calculées en utilisant la commande "ANALYZE". Si vous optez (et je répète que tôt ou tard vous devriez le faire) pour un recalcul des statistiques en 11g vous devez alors utiliser le package DBMS_STATS. Et c'est là où commencent les problèmes. Ces deux méthodes ne fonctionnent tout simplement pas de la même manière et ne produisent pas les mêmes statistiques. j'ai résumé brièvement quelques différences ici dans cette intervention. Ce qui veut dire clairement que vous allez avoir du pain sur la planche. Afin que vous puissiez espérer avoir la même performance en 11g, il va falloir au moins retomber sur les mêmes statistiques en 11g qu'en 8i et en utilisant DBMS_STATS.

A votre place moi j'opterai pour la statégie suivante:

1. Dans le cas où j'ai encore du temps avant le passage en PRODCUTION, j'oublie les statistiques en 8i, je recalcule les statistiques en 11g en utilisant DBMS_STATS et je laisse mon application vivre tout en la surveillant. Il va falloir également tout tester. A chaque problème de performance, j'altererai l'optimisateur (CBO) à la version qui m'a déjà donné statisfaction et j'analyserai les raisons qui m'empêchent de produire en 11g la performance de la version 10g. J'utiliserai aussi les conseils donnés par Pomalaix si j'ai un problème lié aux hints par exemple en les ignorant au niveau global. Il va sans dire que c'est un projet. Une migration c'est un projet qu'il faut prendre au sérieux.

2. Si je n'ai vraiment pas de temps avant le passage en PRODUCTION, je ne risquerai pas de passer en PRODUCTION sans avoir tester la stratégie que j'aurai adoptée pour cela. Il faut dire qu'à part utiliser le CBO de la version antérieure comme solution temporaire, je ne vois pas quoi faire de mieux.

D'une manière générale, j'ai toujours opté pour "Ne pas recalculer les statistiques si la performance est bonne". Mais là, vous n'avez pas de chance, la performance n'est pas bonne en 11g et une solution fiable serait d'avoir des statistiques qui reflettent la réalité. Le CBO fonctionne comme cela "Garbage in Garbage out". S'il arrive à faire de bonnes estimations sur la base des statistiques dont il dispose il arrivera à produire un plan d'exécution le plus performant possible; sinon c'est aléatoire soit le dynamic sampling fait office d'un bon remplaçant (pas vrai à tous les coups) soit la cardinality feedback entre en scène (et là j'ai un vrai doute que cela fonctionne bien)

[devkais]

Bonjour à tous les Experts,

Ci-joint le paramétrage de la base de test 11g.

Il s’agit d’une base 11g est construite par clonage base 10g + upgrade 10g/11g.

Elle est configurée pour une gestion automatique de la mémoire.

Pensez-vous que tout est OK au niveau paramétrage 11g ?

Sinon j'ai des questions diverses :
- Si contrainte de temps pour calculer les stats , est-il possible de les calculer uniquement sur certaines tables + indexes avec un % d'estimation = 5% par exemple ? Ce % sera-t-il suffisant ?

- Si après recalcul des stats en 11g il arrive une régression quasi-totale des traitements (rien ne marche), faut-il investiguer pendant quelques jours pour analyser requête par requête les 5000 requêtes de l’appli. (Travail de fourmis) ou revenir en arrière sur les anciennes stats et trouver une autre solution ?

- Quelle méthode simple permet de réorganiser efficacement tous les objets de la base (move de tables , rebuild d'index ...) afin de gagner en performances ?

- Faut-il vérifier les lignes chaînées et migrées en 11g ?
- Dois-je modifier un ou plusieurs paramètres cachés de la base dans mon cas de figure ?

- SQL Developer (l’outil gratuit d’Oracle) permet-il d’auditer un peu la base et avoir des informations utiles ?

- Au cours des tests 11g , le DBA serait éventuellement amené à modifier un paramètre de la base 11g , faut-il dans ce cas refaire tous les tests déjà effectués ?

Merci pour votre aide.

Cordialement.

[Pomalaix]

Attention, ne partez pas sans méthode dans des bricolages anarchiques et sans cohérence.

Comme l'ont souligné les copains, une telle migration est un véritable projet (potentiellement plusieurs semaines), ce qui n'a rien à voir avec l'application de 2 ou 3 formules prétendues magiques.
Pour moi, il faut que votre hiérarchie prenne conscience de l'ampleur de ce projet et des compétences requises, et renforce votre équipe en conséquence.

Par exemple, personne ne peut répondre à la question du paramétrage de votre base (sans compter qu'il manque le nom des paramètres dans votre fichier Excel), car on paramètre une base en fonction des données qu'on y met et de l'usage qu'on en fait.
Il n'existe pas de paramétrage tout terrain qui serait valable pour n'importe quelle base 11g.

[devkais]

Bonjour ,

Si un upgrade d'une base 10g de PRODUCTION vers la version 11g provoque une catastrophe au niveau des performances malgré les tests déjà effectués et que la disponibilité de la base de PRODUCTION est une critère très important , annuler un UPGRADE est-il possible ? Si oui quelles sont les contraintes et le risque de cette opération de retour arrière ?
Autres solutions pour éviter ce scénario ?

Merci les Experts.
Cordialement.
Kais

[pachot]

Bonjour,
Oui, il est possible de faire un downgrade si vous gardez le paramètre COMPATIBLE à la 10g.
Il est aussi possible de rester en 11g en n'utilisant que les fonctionnalités 10g du CBO (voir les réponses de Pomalaix et Mohamed). Il est aussi possible de garder les plans d'exécution qu'il y avait en 10g...
Mais si vous avez testé les use-case critiques, il vaut mieux prévoir de régler les quelques problèmes qui vont se poser sur les autres cas. De toute façon il faudra migrer un jour, et c'est déjà cette attitude trop conservative qui a amené cette situation: bloquer les stats, fixer les plans avec des hints.
Par contre, c'est sûr qu'il faut prévoir du budget post-migration pour cela.
Cordialement,
Franck

[devkais]

Bonjour,

Quelques questions techniques ?

1/ Existe-il une technique permettant de surveiller l’activité « volumétrique » des tables en journée ? taux de mise à jour ? Données volatiles ou pas ? Afin de juger à quel moment (après quel traitement de l’application) doit-on calculer les stats ?

La table USER_TAB_MODIFICATIONS n’est pas suffisante …

2/ Peut-on limiter le calcul des stats (durée : quelques minutes) sur une base très volumineuse en utilisant :

- Un pourcentage d’estimation très bas : 5% ?
- Un degré de parallélisation un peu élevé : 7 ? ( 10 CPU) ou laisser Oracle choisir en le degré en AUTO ?
- Intégrer dans l’application des calculs de stats qui se limitent uniquement aux tables modifiées à plus de 10% + leurs indexes.
- Eviter de calculer les histogrammes sur les colonnes distribution uniforme.
- ...

Quelles astuces permettant d’accélérer ce calcul tout en gardant un certain niveau de précision ?

3/ Les stats font figées pour les anciennes tables mais non calculés (LAST_ANALYZED est NULL) pour les tables récemment créées :
Comment réagit l’optimiseur dans ce cas ?

Il fait systématiquement du DYNAMIC_SAMPLING si stats non trouvées ?

4/ SANS vérifier le code du traitement , comment peut-on vérifier si les stats sont calculées par :

• DBMS_STATS
ou
• ANALYZE
ou
• DYNAMIC_SAMPLING

5/ Comment peut-on vérifier si le calcul des stats « système » est activé ou pas ?

Leur importance ? et dans quel cas faut-il les calculer ?

Merci.

[mnitu]

1) En général c’est l’audit mais sur cette problématique la base s’en occupe via le job automatique GATHER_STAT_JOB. Vous devez vous analysez éventuellement les tables temporaires ou de « travail » qui peuvent avoir des variations importantes des données dans la journée.

2) Lisez attentivement la documentation du package DBMS_STATS et chosiez ce que vous en avais besoin. Testez et modifier en fonction de vos besoins.

3) Oui et le package dbms_xplan signale quand dynamic sampling a été employé dans sa section Note

4) Si vous n’employez pas la commande analyze et active le job automatique tous les statistiques sont calculée via le package dbms_stats.

5) Pareil dbms_xplan signale si vous utilisez l’ancien modèle de coût basé sur les IO seulement, dans sa section note. Interrogez les table sys.aux_stats$ ou utilisez le package dbms_stats fonction get_system_stats.

Lisez aussi Managing statistiques for optimal query performance

[devkais]

Bonjour,

Puis-je savoir comment fonctionne le job de nuit de calcul des stats dans la 11g ?
- Nom du job ?
- Plages de lancement par défaut ?
- Comment activer / désactiver ce job ?
- Quelle méthode utilisée pour effectuer ce calcul ?
- Avec quelles valeurs des paramètres du DBMS_STATS est-il lancé ?
- Peut-on exploiter ce job pour calculer les stats uniquement sur les objets dont les données sont fortement modifiées en dehors des plages où il est planifié ? Entre 2 traitements de jour par exemple ?

- Dans quels cas ce job n’est adapté ?

- Existe-il des spécificités et subtilités entre ce job de calcul de stats 11g et l’optimiseur 11g ? des contraintes ? des paramètres spécifiques de la base ?

Merci les Experts.

[mnitu]

Vous allez trouver des réponses dans Oracle White Paper—Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer
Lisez aussi la documentation du package DBMS_STATS.

Pour les objets qui subissent des fortes variations dans leur volumétrie se baser sur le dynamic sampling peut être la bonne piste à envisager. Mais commencez par analyser si vraiment ça pose un problème.

[devkais]

Faut-il calculer les stats sur les tables temporaires (GTT) et les tables externes ?
merci

[mnitu]

Ca va être difficile. Si les tables externes et temporaires ne sont pas utilisées dans les jointures alors vous pouvez vivre sans des statistiques à jour. Pour les tables temporaires le dynamic sampling suffit dans ce cas. Sinon vous pouvez vous inspirez de cette article pour des autres solutions a ce type de problème.