Discussion :

[laurentAM]

Bonjour à tous,

Base 10gr2 8Go de données dont 5Go de tables pour 3Go d'indexes.

J'ai deux paramètres dans un environnement OLTP où les requêtes répondent entre 3 secondes (70% de l'activité) et pour les plus grosses (30% de l'activité) 1 minute :

optimizer_index_caching à 0 et optimizer_index_cost_adj à 100.
Je veux les modifier afin d'inverser la sous utilisation des indexes à 80 pour le cache et 20 pour le coût.

1ère question : qu'est-ce que je risque ?

Ensuite la mémoire est gérée en auto par Oracle. 2 Go répartis comme suit :
- 880Mo en shared_pool
- 608Mo en dba_cache_size
- 250Mo en PGA

Les requêtes sont pour la plupart non bindés, le param cursor_sharing est à EXACT, il n'y a pas d'histogramme de calculé sur les indexes, les proc ne sont pas "pinées".

Je propose une action complète :
- pinned des procédures dans la limite de 50% de la shared_pool_reserved_size
- calcul des histogrammes pour les champs indéxés ou utilisés dans les clauses where
- cursor_sharing à SIMILAR
- gestion "manuelle" de la mémoire et du fait :
- baisse de la shared_pool à 400Mo
- augmentation du cache à 800Mo
- création d'un keep à 400Mo
- PGA à 300Mo

2è question : Ces conseils vous paraissent judicieux ou suis-je dans l'erreur la plus totale ?

Sincères salutions

[yke013]

Bonjour,

Ces choix semblent judicieux... si il n'y pas pas de variables bindées il vaut mieux en effet baisser la shared pool et augmenter le buffer cache.

As-tu testé ces optimisations, y a-t-il eu un gain ?

[pachot]

Bonjour,

Désolé mais pour moi la bonne approche du tuning oracle est ... exactement l'opposée de la tienne
Sans avoir défini un objectif de tuning (temps de réponse de certains use cases), ni analysé ce qu'il se passe au niveau de la base dans ces uses cases (timed events, explain plan) tu va modifier une dizaine de paramètres en même temps, en plus des paramètres qui un un impact global sur toute l'activité de la base de données. Même si par chance ça améliore quelque chose, tu ne saura pas pourquoi, ni les effets de bord, ni si l'amélioration va durer dans le temps,...

1ère question : qu'est-ce que je risque ?

Tu risque de changer beaucoup les plans d'exécution. En mieux ou en moins bien.
As-tu identifié beaucoup de plans d'exécution où Oracle choisir un full scan alors que l'accès par index serait plus performant - et ce alors que les stats sont à jour ?

- pinned des procédures dans la limite de 50% de la shared_pool_reserved_size

La shared pool est faite pour garder les procédures fréquemment utilisées, est-tu sûr de faire mieux à la main ?

- calcul des histogrammes pour les champs indéxés ou utilisés dans les clauses where
- cursor_sharing à SIMILAR

Les histogrammes ne sont pas fait pour toutes les colonnes. Seulement, celles où la répartition des données peut amener Oracle à mal estimer les statistiques.
Si il y a des histogrammes partout, SIMILAR ne va pas remplacer beaucoup de de choses. Par contre il introduit toujours des effets de bord.

- gestion "manuelle" de la mémoire et du fait :

- baisse de la shared_pool à 400Mo
- augmentation du cache à 800Mo

As-tu observe quelque chose qui te fait dire que tu as besoin de plus de buffer cache ? Et de toute manière il faudrait faire celà s après avoir fait toutes les modifs précédentes qui vont bouleverser un peu tout.

- création d'un keep à 400Mo

Pourquoi ? Le cache ne fonctionne pas bien ?

- PGA à 300Mo

Seulement 300Mo à partager entre toutes les sessions ? C'est très peu.


Dans tout ce que tu dis, je vois 2 choses qui peuvent être la cause de problèmes de performance actuellement:

1- le manque de bind variables, en OLTP, c'est catastrophique. Attente sur des latch, conso CPU, fragmentation shared pool, ... CURSOR_SHARING, c'est un contournement lorsqu'on ne peut vraiment pas modifier l'appli, mais c'est à tester longuement, beaucoup de conséquences.

=> à mesurer (statspack): latch, CPU, %parse time

2- la PGA: chaque session a besoin d'un peu de place pour faire des tris, et je hash join, sans passer son temps à échanger des données temporaires vers le disque. Si tu as de la mémoire à allouer, c'est ici qu'il faut la mettre à mon avis

=> à mesurer (statspack): workarea en multi-pass ou onepass, events 'direct path temp', utilisation disque tempfiles

Voilà, tu as mon point de vue...

Cordialement,
Franck.

[laurentAM]

Bonjour et merci beaucoup pour ton avis.

Désolé d'avoir tant de temps à répondre mais j'étais en vacances ! :-)

La base de données a déjà été auditée et les conclusions de l'expert sont qu'il y a un problème de requêtage, trop de parsing, trop de full scan sur les tables.

Je souhaite modifier un ensemble de paramètre car à mon avis ils sont assez cohérents et surtout agissent ensembles mais il est vrai que tout faire d'un coup n'est pas une bonne idée.

Les différentes requêtes testées montrent qu'elles tournent plus vite avec des hint qui favorisent l'utilisation d'indexes. Cela réduit le % parse et l'utilisation de la CPU.

La shared_pool a un très fort taux de remontée des données, c'est pour cela que je crois qu'il serait mieux de pinned quelques programmes toujours très utlilsés.

D'après la vue pga_advice l'utilisation de 250Mo est optimal.

Je souhaite créer un keep car beaucoup d'indexes sont sans cessent remonté en mémoire.

La vue V$DB_CACHE_ADVICE montre en effet qu'il n'est pas hyper recommandé daugmenter la mémoire seulement j'ai des taux de hit ratio de la mémoire qui descendent parfois à 50%.

Concernant les bind variable c'est en effet une application et ils ne veulent pas la modifier, ce qui explique que je veuilel modifier le cursor_sharing.

Concernant les histogrammes j'avais lu qu'il fallait les calculer pour les indexes et les champs utilisés dans un clause where, cela n'est pas toujours vrai ? Quels effets de bord avec le cursor_sharing, je pensais que les deux méthodes allaient de paire quand il n'y a pas de bind variable ?

Cordialement,
Laurent