Discussion :

[aline]

Bonjour à tous,

Je cherche à comprendre quelque chose que je n'ai pas reussi à trouver ou comprendre dans la doc.
Normalement les tris sont fait en PGA_AGGREGATE_TARGET puis s'il n'y a pas la place dans le tablespace temporaire. Il est donc interressant d'avoir une PGA elevée.

Je pense avoir cela en mode dedié.
Par contre quand on passe oracle en mode MTS, oracle semble ne plus utiliser la PGA_AGGREGATE_TARGET.

Pour le même select dans les deux configurations, je fais le tris en mémoire et dans le temp respectivement.

Quelqu'un a déjà eu ce problème et à une idée pour sa résolution?

a quoi peut donc bien servir la PGA_AGGREGATE_TARGET avec un shared serveur?

[thomasjcj]

Pour le même select dans les deux configurations, je fais le tris en mémoire et dans le temp respectivement.

a quoi peut donc bien servir la PGA_AGGREGATE_TARGET avec un shared serveur?

Dans une connection en Shared Sever, la PGA n'est pas utilisée. A la place, c'est la UGA dans la Large POOL qui est utlisée comme zone commune pour toutes les sessions en Shared server. Dans le cas d'un tri, ca fonctionne comme pour la PGA, si le tri ne peut se faire en memoire le tablespace temporaire sera utilisé.

[aline]

Pour le même select dans les deux configurations, je fais le tris en mémoire et dans le temp respectivement.

a quoi peut donc bien servir la PGA_AGGREGATE_TARGET avec un shared serveur?

Dans une connection en Shared Sever, la PGA n'est pas utilisée. A la place, c'est la UGA dans la Large POOL qui est utlisée comme zone commune pour toutes les sessions en Shared server.

oui

Dans le cas d'un tri, ca fonctionne comme pour la PGA, si le tri ne peut se faire en memoire le tablespace temporaire sera utilisé.

Je ne suis pas d'accord avec toi. Quand un tri doit se faire, avant de le faire dans le tablespaces temp, il est fait dans la PGA_AGGREGATE_TARGET qui n'est pas la même chose que la pga!
Pour rappel, c'est une zone alloué en mémoire à tout les utilisateurs pour le tri. Elle permet de remplacer avantageusement la sort area size.
Mais cela est vrai en dédié et pas en mts. Et la cette zone ne sert plus à rien!
Et c'est cela que je ne comprend pas!

[thomasjcj]

Quand un tri doit se faire, avant de le faire dans le tablespaces temp, il est fait dans la PGA_AGGREGATE_TARGET qui n'est pas la même chose que la pga!

Il faut essayer de ne pas mélanger les termes.
PGA_AGGREGATE_TARGET est un parametre et la PGA et une zone mémoire. PGA_AGGREGATE_TARGET defini la taille maximale que peut atteindre la PGA totale allouée a toute une Instance. Prenons une connection dediée, un user process va etre crée et une PGA va lui etre allouée. mettons que ce user process traite des requetes avec des gros tri. Oracle va tenter d'aggrandir la PGA allouée au user process dans la limite du prametre PGA_AGGREGATE_TARGET, et en tenant bien sur compte des eventuels autres PGA allouées par d'autres user Process.
Es c'que c'est plus clair :

Tu peux aussi voir la doc Oracle a ce sujet si tu ne l'a pas encore parcourue
http://download-west.oracle.com/docs...emor.htm#14491

[aline]

oui merci, en effet, c'est plus clair maintenant.
Tu as trouvé les mots qui me parlent et qui sont plus explicites que cette bonne vieille doc.
Merci!

[tmenard]

Pour ceux qui tomberait sur ce post une petite précision.
MTS et UGA..

L'UGA c'est la zone mémoire réservé qui contient les paramètres de session.

Cette zone mémoire est incluse dans la PGA en serveur dédié et dans la SGA en serveur MTS.

Contenu PGA pour un serveur dédié
Une zone de tri (Sort area) : Utilisé pour stocker, si nécessaire, les résultats intermédiaires lors du tri.
Une zone UGA (Infos de session) : Contiennent les privilèges utilisateurs pour la session en cours.
Un état de curseur (Cursor State) : Etat de l’étape dans l’exécution des éventuels curseurs de la session.
Un curseur est un pointeur sur la mémoire associé à une requête SQL donnée. C’est une zone de travail utilisé pour stocker le résultat de la requête.
Un espace de pile (Stack Space) : Contient les variables de session et les arrays.

Le tri se fait dans la PGA dans tous les cas mais quand le tri est trop gros il est réalisé dans la large pool (en MTS).

Le tuning PGA, dans le cadre où la PGA_agreggate_target est défini, est simple.. modifiez ce paramètre.
Les paramètres sort_area_size et hash_area_size deviennet obsolète avec ce paramètre.

Si vos tris débordes sur disque, tunner vos requêtes.. et vérifier la pertinence de retourner autant de données..

Et ma PGA initial?
Estimation de la PGA initiale avant recommandation de l'advisor
OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

Sinon pour voir votre PGA et ses besoins:
SELECT
to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(WORK_AREA_SIZE/1024) WSIZE,
trunc(EXPECTED_SIZE/1024) ExpectedSize,
trunc(ACTUAL_MEM_USED/1024) ActualMemUsed,
trunc(MAX_MEM_USED/1024) Mem_Max_Util,
number_passes Pass,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM v$sql_workarea_active
ORDER BY 1,2;

 Si EXPECTED_SIZE < ACTUAL_MEM_USED  Trop
 Si EXPECTED_SIZE > ACTUAL_MEM_USED  Pas assez

Sort
-- Localisation des tris
SELECT name, value
FROM v$sysstat
WHERE name like 'sort%'
UNION
-- % de Tri sur Disque
SELECT 'disk sort percent', TRUNC(a.value/(a.value+b.value)*100,2)
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'sorts (disk)'
AND b.name = 'sorts (memory)'
UNION
-- Lignes par tri
SELECT 'rows per sort', TRUNC(c.value/(a.value+b.value))
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.name = 'sorts (disk)'
AND b.name = 'sorts (memory)'
AND c.name = 'sorts (rows)';

 Le ratio doit être < 5% si OLTP sinon agrandir la SORT_AREA_SIZE
 Le ratio doit être < 1% si OLTP sinon agrandir la SORT_AREA_SIZE

Hash
SELECT s.sid, u.segtype, blocks
FROM v$sort_usage u, v$session s
WHERE u.session_addr = s.saddr;
SID SEGTYPE BLOCKS
---------- --------- ----------
8 HASH 13311
22 SORT 128
40 HASH 13311
44 HASH 13311


 Si on a beaucoup de has join, augmenter hash_area_size.
 Si on a beaucoup de sort join, augmenter sort_area_size.

[orafrance]

 Le ratio doit être < 5% si OLTP sinon agrandir la SORT_AREA_SIZE
 Le ratio doit être < 1% si OLTP sinon agrandir la SORT_AREA_SIZE

<1% pour le DWH j'imagine ?

C'est très complet

[PpPool]

pour un premier post

on attend avec impatience tes nouvelles interventions

[tmenard]

<1% pour le DWH j'imagine ?

==> oui... Malgré les relectures y'a quelques fautes d'orthographes et ça..


C'est très complet

C'est moi qui vous remercie. Ça fait toujours plaisir. ^^

[tmenard]

Petite précision qui interressera Aline et d'autres sur la PGA_Agregate_target.

As of now, the rule is that a single (serial) SQL operator cannot consume more than 5% of pga_aggregate_target and a single SQL operator executed in parallel (e.g. parallel create index) cannot consume more than 30% of the PGA memory

Donc en PGA_Agregate_target, si on a 1 Go on a de rééllement utilisable pour l'utilisateur 5% de 1Go en mode non parallélisé ==> Soit 52Mo
Et en mode parallélisé 30% par degré de parallélisation ==> Soit pour 4 process // ==> 78 Mo

Dû à un paramètre caché d'oracle _pga_max_size pour le non // et _smm_px_max_size en //.

ATTENTION: La modification de ces paramètres est à proscrire sans l'avis d'Oracle et de toute manière vous perdriez votre support en cas de modification (valabble pour toutes les modifications de paramètre caché).
De plus la modification conduit (inexpliqué aujourd'hui..) parfois à des erreurs ORA-04300 même avec de la mémoire disponible.

==> Donc pour les DATAWAREHOUSE, il faut tester avec les paramètres standards sans mode auto et la parallélisation.

La requête suivante (Collecter de manière régulière) peut aider à déterminer les types de SORT effectuer en base (HASH/join) pour définir les besoins (et donc désengorger le tablespace temporaire):

SELECT s.sid, u.segtype, blocks*p.value / 1024 / 1024 "Taille en MB"
FROM v$sort_usage u, v$session s,v$parameter p
WHERE u.session_addr = s.saddr
and p.name='db_block_size';

De plus en Version 9i la PGA_Agregate_target est utilisée pour les serveur dédié MAIS pas pour les serveurs MTS (où les paramètres *_AREA_SIZE seront utilisés à la place).
En version 10g la PGA_Agregate_target est utilisé en mode Dédié ou MTS.

Mais rappelons-le, il faut limiter voir proscrire les sorts....dans les requêtes SQL.

[13thFloor]

Les règles de calcul de la pga par session :
http://christianbilien.wordpress.com..._pga_max-size/

Et pour les amateurs (trices) éclairé(e)s, jetez un œil au post qu'avait fait laurent schneider au support oracle concernant la pga.

[farenheiit]

PGA_AGGREGATE_TARGET defini la taille maximale que peut atteindre la PGA totale allouée a toute une Instance.

En fait PGA_AGGREGATE_TARGET ne correspond pas vraiment à la taille maximale que peut atteindre la PGA. S'il en a besoin Oracle peut allouer plus de mémoire que ce qui est défini dans ce paramètre.

Christian Antognigni le prouve dans le chapitre 5 de son livre troubleshooting Oracle Performance:

It is important to understand that the value of the initialization parameter pga_aggregate_
target is not a hard limit but is rather a target. As a result, if too small a value is specified, the
database engine is free to allocate more memory than the value specified by the initialization parameter pga_aggregate_target. To illustrate this behavior, I executed a query requiring about
60MB of the PGA, with an increasing number of concurrent users (from 1 to 50). The initialization
parameter pga_aggregate_target was set to 1GB. This means at most 17 users (1GB/60MB) should
be able to get the PGA necessary to execute the whole statement in memory. Figure 5-5 shows
the results of the test. As you can see, the system PGA increased up to 1.6GB, which is higher
than the configured value. As expected, the system PGA increases proportionally with the number
of users only up to 17 concurrent users. With more than 17 users, the system started reducing
the amount of the PGA provided to each user.
Figure

[pifor]

Voir aussi le paragraphe Automatic PGA Memory Allocation de Tom Kyte dans Oracle Magazine en 2007 et qui fait rappelle la distinction entre la tunable PGA et la untunable PGA.