Tps réponse 10g vs 8i

**seko** · 04/03/2008, 10h27

Bonjour à tous,

Je suis à la recherche d'une piste pour résoudre une problèmatique devenant de plus en plus urgente ...

Le mois dernier, j'ai migré la base de données décisionnelles de la version 8.1.7 à la version 10.2.0.1 ==> Aucun soucis, comme sur des roulettes, gain de temps sur les interfaces : un bohneur

Mais .... J'ai un soucis ... provenant de certaines requêtes générées par BO.

Exemple : je copie l'ordre sql d'une requête générée par BO et la colle sous pl/sql. J'ai une base en 8i et une en 10g (exactement les mêmes données)

1) Je la lance en version oracle 8i : 25 secondes
2) Je la lance en version oracle 10g : 40 minutes !!!!
3) Mieux encore : je lance la même requête en changeant l'ordre des instructions dans la clause where (du + au - restrictif) : 11 secondes !!!

Le problème est que je ne peux pas intervenenir sur les requêtes générées par BO, il les fait tout seul.

Pquoi un même requete dure plus longtemps sur la 10g que la 8i ?

Pquoi le comportement change si je change l'ordre des instructions alors qu'il est écrit partout que l'ordre n'a plus d'importance depuis la version 9 ??

Je vous remercie d'avance de toutes les pistes que vous pourrez me donner ...

**orafrance** · 04/03/2008, 10h34

Envoyé par seko

3) Mieux encore : je lance la même requête en changeant l'ordre des instructions dans la clause where (du + au - restrictif) : 11 secondes !!!

ça, ça veut dire que tu es en mode RULE (pas de stats calculées). T'as comparé les 2 explain plans ?

**seko** · 04/03/2008, 10h47

Pour le mode, sur ma version 10, optimizer_mode = all_rows
Sur la version 8, je ne sais plus, je vais voir tout de suite.

Concernant l'explain plan, il est effectivement different entre le cas 1 et 2. Par contre, il est exactement le même entre le cas 2 et 3

**remi4444** · 04/03/2008, 11h24

C'est à cause du plan d'exécution qui a surement changé pour ta requête.

Avant tout, passe les stats sur ton schéma et re-teste.

Si le problème persiste, il faut agir sur les paramètres de l'optimiseur. Oracle 10g a quelques différences dans les valeurs par défaut de l'optimiseur. Il active certains trucs qui à mon avis sont buggués comme le le "bitmap conversion"

pour desactiver les fonctions de manère light au niveau de la session (si ça marche, il faudra stabiliser le truc en les mettant dans les paramètres de démarrage):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
alter session set "_b_tree_bitmap_plans"=false 
/* desactive le bitmap conversion */

si le problème persiste, essaye:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
alter session set "_optimizer_sortmerge_join_enabled" = FALSE 
/* desactive le MERGE-JOIN */

Perso, je n'ai jamais eu à utiliser le 2ieme paramètre, mais très souvent le premier. D'ailleurs maintenant, je le mets systématiquement dans mon fichier d'init.

Si tu veux avoir la garantie de stabilité des plans d'éxécution lors de ta migration (tant pis pour les éventuelles améliorations de 10g), tu peux utiliser le paramètre de compatibilité de l'optimiseur OPTIMIZER_FEATURES_ENABLE auquel tu affectes la valeur 8.1.7

**seko** · 04/03/2008, 11h33

J'allais justement dire que je viens de tenter de changer le optimizer_feature_enable='8.1.7' ==> Effectivement, c'est beaucoup mieux

Pour le reste :
Les stats et rebuild d'index sont executés tout les dimanche donc pas de soucis de ce côté.

Je teste de suite les 2 autres options de l'optimiseur .... Merci beaucoup !

**seko** · 04/03/2008, 12h00

Résumé :
- Pas de changement si je change les parametres "_b_tree_bitmap_plans" et/ou "_optimizer_sortmerge_join_enabled"
- Problème résolu si je fais "alter session set optimizer_features_enable = '8.1.7'"

Je laisse la discussion ouverte pour l'instant au cas où quelqu'un aurait une autre idée concernant les parametres de l'optimiseur 10.2.0.1

merci remi4444, on a bien avancé, j'ai au moins une solution de secours que je viens de mettre en prod !

**Michel SALAIS** · 04/03/2008, 13h06

Envoyé par seko

Exemple : je copie l'ordre sql d'une requête générée par BO et la colle sous pl/sql. J'ai une base en 8i et une en 10g (exactement les mêmes données)

1) Je la lance en version oracle 8i : 25 secondes
2) Je la lance en version oracle 10g : 40 minutes !!!!
3) Mieux encore : je lance la même requête en changeant l'ordre des instructions dans la clause where (du + au - restrictif) : 11 secondes !!!

Le problème est que je ne peux pas intervenenir sur les requêtes générées par BO, il les fait tout seul.

Pquoi un même requete dure plus longtemps sur la 10g que la 8i ?

Pquoi le comportement change si je change l'ordre des instructions alors qu'il est écrit partout que l'ordre n'a plus d'importance depuis la version 9 ??

En ce qui concerne l'ordre des critères dans la clause "where", c'est la collecte des stats système et l'activation de cpu consting qui rend l'ordre sans importance. Dans 10g la prise en compte du cpu dans les coûts est automatiquement activée mais il reste la collecte des stats système

**seko** · 04/03/2008, 14h02

orafrance : oui c'est vrai. C'est bien pour cela que je n'ai pas clos la discussion. Le passage de l'optimiseur en version 8.1.7 m'a permis d'éteindre le feu sur une situation urgente (il s'agit d'une base décisionnelle et toutes les stats mensuelles plantent). ==> Notre service commercial me remercie et, par conséquent vous remercit aussi !!!!

remi4444 : Merci pour ces ressources, je vais analyser tout ca et prendre le temps de bien cibler le soucis

Michel SALAIS : Puis je avoir plus d'info sur la collecte des stats systèmes ? Comment vérifier sur ma base ?

**pifor** · 04/03/2008, 14h19

Avant de modifier des paramètres non documentés et non supportés, je pense qu'il vaut mieux utiliser des fonctionnalités documentées et supportées comme les paramètres documentés et supportés, la modification du mode de calcul de statistiques, l'utilisation des stored outlines, voire l'utilisation des hints.

Comme le dit D. Burleson:

WARNING! Using undocumented parameters without the consent of Oracle can make your system un-supported and you may be on your own if you experience data corruption. Don't say I didn't warn you!

Je signale aussi un article intéressant et récent sur OTN sur les modifications de l'optimiseur entre Oracle 9 et Oracle 10.

Les statistiques système (ou CPU costing) sont aussi documentées dans le Performance and Tuning Guide.

**seko** · 04/03/2008, 14h26

Tout a fait d'accord !

Peut-être même qu'il y aurait des paramètres a sortir complétement de l'analyse au vue des symptômes rencontrés.

Deja peut etre faudrait-il que je cherche
- sur les paramètres étant a la fois sur la 10.2.0.1 et la 8.1.7 et dont la valeur a changé.
- sur les paramètres nouveaux depuis post 8.1.7

Je regarde les avis du Dr Burleson !

**remi4444** · 04/03/2008, 14h54

Tout à fait, il faut faire les choses dans l'ordre, c'est à dire commencer par analyser les différences entre les 2 plans d'exécutions, essayer de comprendre ce qui se passe et tenter de résoudre par des moyens le plus propres possibles.

Personellement j'en suis venu à désactiver systématiquement paramètre "_b_tree_bitmap_plans" par expérience et après avoir identifié à de nombreuses reprises un incroyable choix de l'optimiseur 10g sur certaines jointures dans certaines conditions. La fonction en question qui consiste à mettre dans un tableau bitmap des rowid existe depuis l'optimiseur 8i mais était curieusement désactivée par défaut aussi bien en 8i qu'en 9i, j'en ai donc conclus que c'était pas si grave de continuer à la desactiver en 10g

Si ce changement de paramètre n'a rien amélioré de ton coté alors tu dois être tombé sur un autre problème qui permet peut etre une solution plus simple.

Ceci dit, je trouve tout à fait normal de jouer sur le paramètre optimizer_feature_enable qui a été mis à disposition précisément pour assurer une stabilité dans les calculs de plan d'exécutions lors des migrations. Sur des bases de gros volumes et/ou on fait des requêtes complexes, c'est même une règle qu'il faut se fixer à mon avis de toujours positionner dans un premier temps ce paramètre dans la version précédent la migration. Un calcul de plan d'exécution est loin d'être une science exacte, l'optimiseur peut facilement subir un "effet papillon" et pondre un plan d'exécution catastrophique. C'est pourquoi tout changement d'optimiseur est une opération trés délicate qui mérite un processus sérieux de qualificication de toutes les requêtes en terme de performance, en particulier le passage de 9i à 10g.