Discussion :

[seko]

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]

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]

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]

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]

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]

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 !

[orafrance]

C'est bien joli de soigner le symptome mais ça guéri pas la maladie... faudrait trouver pourquoi le CBO se plante plutôt qu'utiliser le CBO 8.1.7

[remi4444]

Si les 2 paramètres que j'ai donné ne change pas, c'est que ça doit encore être une autre fonctionnalité qui est en cause. Pour comprendre, il faut regarder en détail la différence des 2 plans d'exécutions et jouer avec les paramètres cachés de l'optimiseur:

http://www.dba-oracle.com/t_hidden_o...parameters.htm

http://www.centrexcc.com/What%20is%2...209i%20CBO.pdf

Il faut avoir tu temps aussi parceque y'en a des trucs!

Sinon, à mon avis, la maladie c'est que l'optimiseur 10g est un peut trop "intelligent" et prends parfois des initiatives malheureuses sur la base d'obscurs calculs complexes, trop complexes pour être maitrisés par qui que ce soit

[Michel SALAIS]

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]

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]

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]

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 !

[orafrance]

Sinon, à mon avis, la maladie c'est que l'optimiseur 10g est un peut trop "intelligent" et prends parfois des initiatives malheureuses sur la base d'obscurs calculs complexes, trop complexes pour être maitrisés par qui que ce soit

ouais... en partant comme ça évidemment on risque pas de progresser. Les paramètres cachés rendent des services mais ceux tourner vers eux en tout premier lieu ça me parait pour le moins... dangereux.

Si le CBO se plante c'est qu'il a une bonne raison : histogrammes pas bon, stats pas assez précise, index incorrecte, etc...

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 !!!!

Bien sûr, pallier à l'urgence est important mais résoudre le problème avec une solution pérenne l'est d'autant plus... ces problèmes de perf permettront peut-être de mettre en lumière un souci bien plus grave

[remi4444]

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.

[orafrance]

de toujours positionner dans un premier temps ce paramètre dans la version précédent la migration.

Oui, éventuellement dans un 1° temps... et en principe sur la base de test permettant de voir les dérives avant de migrer la base de prod en 10... m'enfin, c'est trop tard donc essayon de trouver le souci

[Michel SALAIS]

C'est bien joli de soigner le symptome mais ça guéri pas la maladie... faudrait trouver pourquoi le CBO se plante plutôt qu'utiliser le CBO 8.1.7

Entièrement d'accord. D'autant plus que même si ça régle le problème d'une requête particulière, il empêche l'utilisaiton de nouvelles fonctionnalités apportées par la 10g.

[Michel SALAIS]

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

Tu peux trouver des informations ici

[remi4444]

Entièrement d'accord. D'autant plus que même si ça régle le problème d'une requête particulière, il empêche l'utilisaiton de nouvelles fonctionnalités apportées par la 10g.

En théorie oui, mais en pratique j'ai pas réussi à trouver une plus-value convaincante ce ces nouvelles fonctionalités. J'y ai par contre rencontré trop souvent des effets très facheux dus à mon avis au fait que plus personne ne sait maitriser cette machinerie trop complexe.

Autant j'ai apprécié les nouveautés de la 9.2.0 (c'est peut etre cette valeur qu'il faut donner à l'optimiseur d'ailleurs ), autant celles de la 10g me laisse perplexe. Ce n'est que mon humple avis, mais dans la 10g, je trouve qu'on commence à subir le syndrôme microsoft, c'est à dire une foule de fonctions plus ou moins utiles qui ont de plus en plus de mal à former une ensemble cohérent. Resultat: une instabilité chronique et une pluie de patchs qui tombent sur le nez des pauvres dba

Si ce fameux paramètre de version d'optimiseur est apparu, ce n'est pas un hasard, c'est bien un aveu que les concepteurs n'arrivent plus à assurer une compatibilité ascendente du CBO. Si ce paramètre n'existait pas, je connais pas mal de clients qui auraient abandonné purement et simplement oracle.

[13thFloor]

seko : dis-nous un peu le delta entre les 2 plans (817 et 10g).
L'un fait du nested loops (817) et l'autre du hash join (10g) ?
Si tel est le cas :
- diminue optimizer_index_cost_adj pour favoriser les index scan (de 100 à 20)

Pour les stats : dbms_stats.dbms_gather_schema_stats avec granularity ALL si tu as des partitions/sous-partitions.

Tes requêtes passent-elles leur temps à faire du tri ? PGA à augmenter ou passage en workarea_size_policy MANUAL avec sort_area_size conséquent (si tu ne veux pas modifier _pga_max_size qui permet d'allouer davantage de mémoire pga pour une session, par défaut 5% de la PGA avec un max à 200 Mo).

[orafrance]

Autant j'ai apprécié les nouveautés de la 9.2.0 (c'est peut etre cette valeur qu'il faut donner à l'optimiseur d'ailleurs ), autant celles de la 10g me laisse perplexe. Ce n'est que mon humple avis, mais dans la 10g, je trouve qu'on commence à subir le syndrôme microsoft, c'est à dire une foule de fonctions plus ou moins utiles qui ont de plus en plus de mal à former une ensemble cohérent. Resultat: une instabilité chronique et une pluie de patchs qui tombent sur le nez des pauvres dba

La 10g introduit un nouvel manière d'administrer la base en se concentrant d'avantage sur la collecte d'information à destination du noyau plutôt qu'aux traitements des carences de celui-ci. Ainsi, le CBO est globalement meilleur pourvu que les "règles de bonne conduite" soient respectées : calcul des stats sur les objets et calcul des stats systèmes en sont les pilliers

Si ce fameux paramètre de version d'optimiseur est apparu, ce n'est pas un hasard, c'est bien un aveu que les concepteurs n'arrivent plus à assurer une compatibilité ascendente du CBO. Si ce paramètre n'existait pas, je connais pas mal de clients qui auraient abandonné purement et simplement oracle.

C'est tout simplement pour assurer la compatibilité descendante ce qui permet au client ayant une 8i de bénéficier du support d'Oracle sans avoir à faire un gros travail de migration. C'est purement commercial et probablement mis en oeuvre sous la pression des clients. Mais l'objectif est bien de mener une étude d'impact complète pour ensuite pouvoir migrer complétement en 10g