Discussion :

[genio]

Bonjour... oracle 9i et Oracle APPLI 11i...

j'ai un cas un peu spécial à vous soumettre..

Nous avons un traitement qui passe (via oracle Appli) et qui, suivant le % de stat choisit un chemin différent...

1°) Nous avons refait des stats à 20 % sur un traitement... celui-ci (qui passait en 3 secondes !) devient très pénalisant et bloque, car notre SELECT utilise un mauvais index N1... => OK

Nous cancellons et 5 minutes après, sans qu'il y ait eu de mise à jour de données :

2°) Nous passons les stats à 30 %... le traitement utilise bien le bon index U1 (l'explain en fait foi !) mais le traitement, cette fois-ci bloque sur un programme Oracle application et ne rends pas la main...

Nous re-cancellons et 5 minutes après, sans qu'il y ait eu de mise à jour de données :

3°) Nous repassons les stats à 20 %... le traitement utilise le bon index U1, ne plante plus, et passe en 2 secondes...

Ce qui veut dire qu'un traitement qui est analysé à 20 %, puis à 30 % et ensuite à 20 %, peut,
- se trainer à 20 %
- retrouver le chemin idoine après avoir passé les stats à 30 % (mais bloquer sur un executable Oracle Appli)
- retrouver toute sa bonne logique et sa vigueur, en repassant les stats à 20 % ...

Comment pouvez-vous expliquer ça ?

Merci d'avance pour vos réponses ...

NB : Les statistiques passées sur nos tables GL sont, suivant les sites, vieilles de 2 ans ! Est-ce le cas sur vos sites ?

[LeoAnderson]

est-ce qu'entre 2 calcul estimate, vous effacez les stats ?

quelles sont les commandes utilisées ?

[remi4444]

Etant donné que le choix du plan d'exécution est parfois très très subtil, absolument tout est possible en la matière!

Par exemple il se peut que les stats à 20% soient tellement fausses qu'oracle se trompe à plusieurs endroits et qu'à l'arrivée la conjonction d'erreurs le fasse par hasard retomber sur un plan correct... Et peut etre qu'avec 30% il ne se trompe qu'à moitié ce qui engendre un plan catastrophique....

NB : Les statistiques passées sur nos tables GL sont, suivant les sites, vieilles de 2 ans ! Est-ce le cas sur vos sites ?

Rien de pire que des stats fausses! en particulier s'il y a des histogrammes (stats sur les colonnes)

Personnellement j'aime pas les stats selon pourcentage, car si elles sont tres valables sur les tres grosses table, sur les petites, elles peuvent etre très mauvaises.

On peut faire des stats par ANALYZE avec un nombre fixe de lignes (~100 000) par exemple, ainsi les petites tables seront calculées avec le valeurs exactes.

On peut surtout utiliser DBMS_STATS.AUTO_SAMPLE_SIZE et faire confiance à oracle pour calculer avec le meilleur échantillonage...

http://download-uk.oracle.com/docs/c...s2.htm#1003995

Il faut aussi se méfier des stats de nuit sur des tables de travail qui sont vides au moment du passage de stats et qui son pleines au moment ou des requêtes y accèdent....

[orafrance]

peut-être que le plan n'a pas été recalculé tout simplement. Il est possible si tu lances exactement la même requête et selon le paramètre CURSOR_SHARING qu'Oracle ne passe d'un nouveau calcul du plan

[genio]

Merci pour vos réponses ...

Pour Léo :
1°) Je n'ai pas effacé les stats entre les deux estimations...
2°) Nous envoyons les stats via le pgm Oracle appli 'fndgscst' (Qui fait, si je ne m'abuse, des DBMS_STAT_GATHER sur des shémas)

Pour Fred_D :
1°) Nous sommes en CURSOR_SHARING = EXACT... et effectivement entre le passage de 30 % à 20% (le deuxième donc !), l'explain n'a pas été modifié, il prenait le bon index, et ne se plantait plus...

Pour Rémi444 :
Je sais bien que ne pas effectuer les stats peut être très pénalisant, mais Oracle application ne semble pas l'entendre de la même oreille... en effet, suivant nos sites, les stats ne semblent pas être la 'base' de fiabilité des traitements... en effet, certains sites ont des stats vielles de 2 ans et fonctionne, à priori, de façon correcte...
1°) La solution, pour toi, serait de faire des DBMS_STATS_GATHER à 20 % sur les grosses tables et des DBMS_STATS.AUTO_SAMPLE_SIZE sur les petites ?

C'est pourquoi je demande aux uns et aux autres, quelles sont leurs approches, vis à vis des stats, sur les tables des schémas APPS, GL, GLCA,SYS et APPLSYS de la compta 'Oracle Appli'...

Merci encore pour vos réponses...

[remi4444]

Le CBO basé sur les stats n'est par définition pas une science exacte, si ta base de donnée n'évolue pas, des vielles stats seront encore bonnes. Il se peut aussi que les plan soient pas trop mauvais mais que ce soit un coup de bol...

Sur le principe je pense que la solution soit d'utiliser le DBMS_STATS.AUTO_SAMPLE_SIZE pour tout le monde...

[orafrance]

j'avais pas percuté que c'était Oracle Appli. Alors dans ce cas, il faut lancer le programme Gather Schema Statistics (je croyais te l'avoir déjà dit ). Recherche dans Metalink, il y a une note très compléte sur la collecte des stats dans OA.

Attention, aussi à la date de dernière analyse. Les objets dont les stats sont les plus récente sont privilégiés dans le plan d'exécution... donc en analysant une seule table tu donnes déjà une indication à Oracle.

Si tu nous donnais un exemple concret ce serait quand même bien plus simple

[genio]

Merci à tous pour vos réponses...

En fait, notre big problème est que nous avons 6 sites OraAPPL (1 site OracleGL par entité géographique)... après l'installation des OraAPPL, très peu de site ont effectué les stats (nous avons un site dont les stats sont vielles de 4 ans et qui ne posent pas de gros problème... bizarre hein... )... certains sites fonctionnent parfaitement et d'autres rament grave... suivant la volumétrie des tables LINE et HEADER (entre autres !)...

1°) Je voudrait arriver à fédérer tout ça :
- Soit en prenant un site de référence et en balançant les stats de ce site sur tous les autres...
- Soit en 'figeant' les chemins d'accès une bonne fois pour toute, sur tous les sites (Mais nous somes en sql dynamique alors !)

Qu'en pensez-vous ?


2°) Est-il possible comme en DB2, de bidouiller des tables SYS (ou SYSTEM) pour modifier le nombre de rows des colonnes, afin que l'optimiseur les prenne en compte (Attention, je ne parle pas des DBMS_STAT.import et export SHEMA !)

Merci encore à vous !

PS pour Fred_D : J'ai bien lancé les stats comme tu me l'as dit, avec les Gather trucmuche...

[remi4444]

certains sites fonctionnent parfaitement et d'autres rament grave... suivant la volumétrie des tables LINE et HEADER (entre autres !)...

- Tu as l'air de dire que les données sont différentes selon les sites non ? si c'est ça, il est possible que certains plans d'exécution soient mauvais mais que ce soit plus ou moins grave en fonction de la volumétrie de certaines tables. Il se peut aussi qu'il n'y ai pas de "bon" plan d'execution possible par une mauvaise écriture de requête ou une absence d'index. Je veux dire par là que malgrès les apparances, les sites sont peut etre parfaitement cohérents entre eux.

- Je n'aime pas quand on dit "un site va vite" ou "un site rame" parcequ'en matiere d'optimisation, ça se joue souvent sur simplement une ou deux requêtes qui prennent à elles seules 90% des ressources, et il suffit bien souvent de résoudre le problème pour ces rares requêtes pour tout débloquer. J'ai vu des dizaines de fois de la perte de temps et d'energie considérable à essayer "d'optimiser une base" par des paramètre généraux alors qu'il aurait simplement suffit de s'interresser de manière plus ciblée et précise aux requêtes longues. C'est en fait assez facile de repérer les requêtes qui posent problème puisque ce sont celles qui restent le plus longtemps actives dans les sessions, donc tres facile à capturer par Toad ou autre...

- De toutes façons si on constate une grosse différence de plan sur une requête selon l'échantillonage des stats, c'est que le CBO a eu à choisir entre plusieurs mauvais plans, donc cette situation instable est le révélateur qu'il y a des requêtes à analyser de près.

[orafrance]

j'ai du mal à voir comment tu peux espérer mutualiser le paramètrage alors que les environnements vivent des vies différentes

[genio]

En fait, il y a deux traitements qui, selon l'instance geographique, passent correctement où non... J'ai bien vu le problème et je sais même où ça cloche... suivant les volumétries de deux tables, l'optimiseur se sert d'un bon index où d'un mauvais (et cela, pour les deux traitements)...
Ce que je voudrais faire, c'est prendre les stats d'une instance qui fonctionne bien, et les implémenter sur une instance qui merdouille... j'ai essayé ça hier et, ça fonctionne ...
Mais je trouve ça assez dangereux du fait que les stats de l'instance qui fonctionne n'ont pas été faites depuis belle lurette, et qu'il suffit qu'un malencontreux administrateur passe les sats sur une des deux bases pour que ça ne soit plus ric-rac !
De plus, ça veut dire qu'il suffit qu'un batch crée de la volumétrie un soir, pour que peut-être, le lendemain, les chemins d'accès soeint modifiés... bref, je ne trouve pas ça tip-top !

C'est pourquoi je voudrais figer les stats sur toutes mes instances, à partir des stats d'une instance qui fonctionne bien...

Ce qui m'étonne c'est quand même qu'oracle Appli déconseille les stats ...

J'ai une question à poser : Comment faire pour relancer le 'posting Process' ? J'ai essayer de terminer et relancer le JEFB_SUBMIT mais, apparemment, ma modif d'un index temporaire n,'a pas éé pris en compte après la relance... Le Jefb_SUBMIT est-il le 'Posting process' ?

Merci pour vos réponses !

[remi4444]

Si tu as une instabilité sur un plan d'exécution c'est que l'optimiseur considère que 2 solutions sont quasiement équivalente alors que pour une requête précise il y a des particularité qui font qu'il y a un bon chemin. Dans ce cas là, soit il faut pousser les stats plus loin en faisant des histogrammes sur les colonnes litigieuses, soit il faut mettre des HINT sur la requête en question afin de figer le plan dans le sens que tu veux.

[orafrance]

Ce qui m'étonne c'est quand même qu'oracle Appli déconseille les stats ...

pas du tout... les stats sont INTERDITES (mode RULE obligatoire) avant la 11... mais depuis la 11 aucun problème bien au contraire