[OPTIMIZER] : différences entre 2 instances identiques

Bonjour à tous

RDBMS 8.1.7.4.0

Soit une première instance de pré-production, rafraichie à partie des datas de la production à un moment t

Soit une seconde instance de production, même release que la première
Statistiques rafraichies tous les soirs sur l'instance de prod

Chaque instance est sur un serveur dédié

OPTIMIZER_MODE=CHOOSE sur les 2 instances
Autres paramétres quasiment identiques

Nous observons des différentes de temps de réponses assez importantes sur toutes les requetes exécutées sur la prod, ce qui nous inquiéte particulièrement.
Les couts sont sur certaines requetes sont 10 * plus importants que sur la pré-prod;

Nombre de sessions simultanées sur la pré-prod : 50
Nombre de sessions simultanées sur la prod : pic à 200 dans la journée
Les plans d'éxécutions sont quasiment identiques.
Les sélectivités des indexs sont identiques.
Les datas sont identiques.

Le seul pb que nous pensons être à l'origine de la différence entre les 2 instances, est l'absence de rebuild d'index sur la prod
Les segments sont plus segmentés, mais ce n'est pas non plus super flagrant.

Nous pensons également que le refresh des stats sur l'instance de la prod a pu corrompre le comportement de l'optimizer. J'ai déjà entendu parler de pbs de calculs de stats sur la 8i.

Pour le reste, on séche.

Quelles pistes vous semblerait-il intéressantes d'introspecter ?

Merci de votre aide

Bonjour,

Code:

---

Les plans d'éxécutions sont quasiment identiques.

---

Il serait important de
1- comparer les plans d'execution qui sont différents,
2- et lorsqu'il y a des différences de temps de réponse alors que les plans d'exécution sont les mêmes, alors comparer les statistiques d'exécution (tkprof avec wait events)

Sinon, comment est fait le refresh ? logique (imp/exp) ou physique (clone) ?

Cordialement,
Franck.

Bonjour franck

merci de t'intéresser au pb que j'ai posé

le refresh est au niveau logique exp/imp au niveau schéma

pour les analyses plus fines, c'est en effet ce que nous avons commencé. Nous avons en effet pas mal de pbs d'attentes au niveau I/O sur la prod

mais là où nous sommes plus inquiet, c'est au niveau d'un simple plan d'exécution que l'on peut faire sans exécuter

sur la prod, les couts sont sensiblement 10* plus élévés
nous constatons également des comportements bizarres comme des accés SORT/MERGE JOIN sur la prod, et des accés bcq plus performants comme des HASH JOIN sur la pré-prod

mystére de l'ouest : même config de l'instance au niveau OPTIMIZER, HASH

D'autant plus que en 8i le plan d'exécution ne devrait pas changer de manière aléatoire (pas de bind variable peeking, pas de pga automatique, ...)

Alors pour creuser cette requête qui donne de si différents plans d'exécution, le meilleur moyen c'est la méthode lourde:

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
EXPLAIN PLAN FOR ...
ALTER SESSION SET EVENTS '10053 trace name context off';

et récupérer la trace dans UDUMP

Ceci pour les 2 instances, et à comparer. Tu verra facilement les paramètres différents, les statistiques différentes.
Et en creusant un peu plus tu verra le choix du plan d'exécution.


Sinon, pour SORT/MERGE JOIN versus HASH JOIN, une raison pourrait êtr que RBO est utilisé (par manque de statistiques par exemple). Mais dans ce cas, tu ne verrais pas le coût lors de l'explain plan.
Parce que c'est rare que SORT/MERGE JOIN paraisse meilleur qu'un HASH JOIN. Les 2 bases, c'est les mêmes charactersets et paramètres NLS_LANG ?
Si tu force le hash join ( hint USE_HASH(table table) ) est-ce que le plan d'exécution change ?

Cordialement,
Franck.

Bonsoir,

Y a t il un réel intérêt à recalculer les stats tous les soirs ?
Parce qu'à priori tu flush les plans d'exécution existant, et re hard-parse tous les matins tes requêtes ce qui n'est pas forcément voulu.

Sinon comment sont exportées les statistiques vers la pré prod ?
via exp/imp, via DBMS_STATS.EXPORT_SCHEMA_STATS ou recalculé après import des données?

Bon ça ne t'aidera pas à résoudre directement ton problème de perfs, mais peut être à stabiliser la solution trouvée.