Gestion de mémoire PGA [Résolu]

korian · 08/02/2011, 16h14

Bonjour,

Suite à un traitement bloqué, j'ai investigué par divers moyens pour me rendre compte que la PGA de mon serveur semble être (très?) sous dimensionnée.

mais je ne suis pas sur de mon diagnostic et j'aurais besoin d'un regard extérieur svp

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS FOR Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

La plateforme est un RAC avec 2 instances.
La base soutient essentiellement une application OLTP web mais des traitements par lot tournent aussi chaque soir (c'est le cas du traitement qui a bloqué).
C'est la production (que je n'ai pas mis en oeuvre) et je n'ai pas de plateforme de dev équivalente. Mais on y croit

J'ai d'abord utilisé l'oem pour identifié la session. Sur la page "de propriétés", le Evénement d'attente en cours était direct path read temp.
Les I/O représentaient 2/3 du temps écoulés.

En lisant la doc oracle :
Oracle® Database Performance Tuning Guide
10g Release 2 (10.2)
Part Number B14211-03
7 Memory Configuration and Use

... on parle de workarea et de mémoire PGA, liées à cette "erreur".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

Donc c'est le paramètre suivant qui est utilisé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SYS@SQL> SHOW PARAMETER pga_aggregate_target;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 24M

Et non ceux-là si le paramètre avait été MANUAL (correct ?)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SYS@SQL> SHOW PARAMETER area_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hash_area_size                       integer     200000000
sort_area_size                       integer     100000000

Plus de détail sur ma PGA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SYS@SQL> SELECT * FROM V$PGASTAT;
 
NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                     25165824 bytes
aggregate PGA auto target                                           4194304 bytes
global memory bound                                                  380928 bytes
total PGA inuse                                                   217719808 bytes
total PGA allocated                                               420397056 bytes
maximum PGA allocated                                             537795584 bytes
total freeable PGA memory                                          34340864 bytes
process count                                                            71
max processes count                                                      75
PGA memory freed back TO OS                                      1107230720 bytes
total PGA used FOR auto workareas                                   7840768 bytes
maximum PGA used FOR auto workareas                                20276224 bytes
total PGA used FOR manual workareas                                       0 bytes
maximum PGA used FOR manual workareas                               1075200 bytes
over allocation count                                                 17680
bytes processed                                                  9940713472 bytes
extra bytes READ/written                                         2.0878E+10 bytes
cache hit percentage                                                  32.25 percent
recompute count (total)                                               17680
 
19 rows selected.

Toujours dans cette page, on demande de visualiser V$PGA_TARGET_ADVICE pour y déterminer plus facilement la valeur de la PGA mais mon résultat est très différent de l'exemple et je ne le comprends pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SYS@SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;
  2    3    4
 TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
        12             29                  172
        18             29                  172
        24             33                  172
        29             33                  172
        34             33                  172
        38             33                  172
        43             33                  172
        48             33                  172
        72             33                  172
        96             34                   84
       144             34                   62
       192             34                   45
 
12 rows selected.

Une autre requête est conseillée, où la dernière colonne devrait être à 0. Ce n'est pas le cas chez moi donc ça m'inquiète encore plus.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SYS@SQL> SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
  2         onepass_count, round(onepass_count*100/total, 2) onepass_perc,
       multipass_count, round(multipass_count*100/total, 2) multipass_perc
  3    4  FROM
  5   (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
  6           sum(OPTIMAL_EXECUTIONS) optimal_count,
  7           sum(ONEPASS_EXECUTIONS) onepass_count,
         sum(MULTIPASSES_EXECUTIONS) multipass_count
  8    9      FROM v$sql_workarea_histogram
   WHERE low_optimal_size > 64*1024);
 10
OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC
------------- ------------ ------------- ------------ --------------- --------------
         4555        94.88           118         2.46             128           2.67

Pour finir je mets les paramètres SGA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SHOW parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             BOOLEAN     FALSE
pre_page_sga                         BOOLEAN     FALSE
sga_max_size                         big integer 2000M
sga_target                           big integer 1904M

Au début de la page oracle, il est conseillé de dimensionner la PGA sur un OLTP comme MEM_SERVEUR x 80 % x 20 % (et 80 % pour SGA). A priori même si le serveur n'est pas dédié uniquement à Oracle, entre 3 et 4Go de RAM peuvent lui être attribués facilement.

Est ce un bon calcul ?

Merci d'avance pour votre aide.

ojo77 · 08/02/2011, 17h11

En effet la PGA est très sous dimentionnée.

Le conseiller (advisor) donne le résultat suivant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
        12             29                  172
        18             29                  172
        24             33                  172
        29             33                  172
        34             33                  172
        38             33                  172
        43             33                  172
        48             33                  172
        72             33                  172
        96             34                   84
       144             34                   62
       192             34                   45

La première colonne donne la taille pour laquelle l'estimation est faite, la deuxième le nombre de bloc qu'il estimera trouver en mémoire pour cette taille de PGA et la seconde le nombre de bloc qu'il estime avoir besoin de lire pour chaque transaction, pour cette taille de PGA.

Ce qui est recherché est d'obtenir un nombre le plus près possible de 100 dans la deuxième colonne sans avoir à allouer trop d'espace mémoire.

Il faut donc augmenter très fortement la valeur de la PGA (500 Mo pour commencer c'est possible ? ). Laisser tourner l'application et revérifier le conseiller afin d'arriver à la meilleur configuration possible.

Heaven93 · 08/02/2011, 18h45

et suite à l'augmentation de la PGA, collecter les stats avec un awr sur la période de batch et voir la partie des advisors à la fin pour redimensionner la PGA si nécessaire.

Pomalaix · 09/02/2011, 00h40

Votre PGA est effectivement très sous-dimensionnée : 24 Mo officiellement alloués, plus de 500 Mo consommés en pointe (maximum PGA allocated 537 795 584 bytes).
A telle enseigne que V$PGA_TARGET_ADVICE, qui fait des estimations à hauteur de 8 fois la taille actuelle de PGA_AGGREGATE_TARGET, est incapable de vous fournir une valeur cible qui induirait une amélioration notable : même à 192 Mo, les choses ne changeraient guère, car il en faut beaucoup plus.

Il me semble qu'il y a une contradiction entre le "cache hit pourcentage" désastreux de 32% d'après V$PGASTAT, et le taux de de près de 95% de "optimal_perc" que vous ramenez de v$sql_workarea_histogram.
A mon avis, la condition "WHERE low_optimal_size > 64*1024" n'est pas pertinente. Il faudrait la supprimer, et je pense que vous retrouverez là aussi un "optimal_perc" très faible.

Pomalaix · 09/02/2011, 00h44

Citation:

Envoyé par ojo77

La première colonne donne la taille pour laquelle l'estimation est faite, la deuxième le nombre de bloc qu'il estimera trouver en mémoire pour cette taille de PGA et la seconde le nombre de bloc qu'il estime avoir besoin de lire pour chaque transaction, pour cette taille de PGA.

Une petite fatigue ? Parce que là, c'est plutôt approximatif, voire fantaisiste comme interprétation !

korian · 09/02/2011, 09h33

Merci beaucoup pour votre aide

@Pomalaix : sans la condition where. Effectivement le résultat est plus probant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SYS@SQL> SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
  2         onepass_count, round(onepass_count*100/total, 2) onepass_perc,
  3         multipass_count, round(multipass_count*100/total, 2) multipass_perc
  4  FROM   (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
  5         sum(OPTIMAL_EXECUTIONS) optimal_count,
  6         sum(ONEPASS_EXECUTIONS) onepass_count,
       sum(MULTIPASSES_EXECUTIONS) multipass_count
  7    8  FROM v$sql_workarea_histogram);
 
OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC
------------- ------------ ------------- ------------ --------------- --------------
        35451        99.78            50          .14              29            .08

Je vais augmenter dès à présent la PGA et je vous tiendrais au courant des changements occasionnés.

La plateforme est constituée de deux serveurs Sun Fire V490 avec 8Go de RAM chacun. Y tournent : Solaris, Solr/Lucene (indexation) et Oracle.
Solr ne consomme pas plus d'1 Go (et encore je suis large).

Je ne comprends pas pourquoi la SGA a été limitée à 2Go (J'hérite d'une solution développée par un industriel, ma société ayant recupéré la maintenance et j'ai été "bombardé" à l'administration Oracle sans formation pointue, ça pique

).
Est ce qu'il pourrait y avoir une raison de ne pas exploiter plus la RAM disponible ?
A priori vu les données, je serais susceptible de passer à une SGA de 9,6 Go (80% de 12 Go [Solaris = 20% de 16Go + 1 Go pour Solr]) et une PGA de 2,4Go.
Est ce raisonnable ?

Pomalaix · 09/02/2011, 10h16

Citation:

Envoyé par korian

@Pomalaix : sans la condition where. Effectivement le résultat est plus probant.

Justement, à mon sens, non ! Je m'attendais à ce que OPTIMAL_PERC descende dans les 30%, ce qui aurait confirmé le sous-dimensionnement, s'il en était besoin.
Là je suis surpris que vous ayez un excellent taux.

Pour la SGA, ne vous précipitez pas : la PGA ne fait pas partie de la SGA.
En dehors de la RAM nécessaire pour l'OS, les produits tiers et la SGA Oracle, il faut en laisser suffisamment pour la PGA, dont la taille maximum, en 10g, n'est pas maîtrisable.

Si vous voulez creuser la question de la SGA, il existe des vues similaires à V$PGA_TARGET_ADVICE :
V$SGA_TARGET_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAMS_POOL_ADVICE.
V$SGA_TARGET_ADVICE permet de trouver le bon dimensionnement pour SGA_TARGET.

korian · 09/02/2011, 11h04

Citation:

Envoyé par Pomalaix

Justement, à mon sens, non ! Je m'attendais à ce que OPTIMAL_PERC descende dans les 30%, ce qui aurait confirmé le sous-dimensionnement, s'il en était besoin.
Là je suis surpris que vous ayez un excellent taux.

Peut être que l'explication tient au fait que la base a été redémarrée hier soir et que l'essentiel des requêtes depuis a été fait par des accès web (A priori moins consommatrices de ressources que les batchs).

Sinon merci pour les infos sur la SGA. Je vais étudier ça plus en détail.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SYS@SQL> SELECT * FROM v$sga_target_advice ORDER BY 2;
 
  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       476             .25        78106              1.8708            30786074
       952              .5        52847              1.2658            27408157
      1428             .75        46134               1.105            26085161
      1904               1        41750                   1            25152021
      2380            1.25        39207               .9391            24676648
      2856             1.5        37308               .8936            24249063
      3332            1.75        35997               .8622            24022695
      3808               2        35997               .8622            24022695

Donc si je comprends bien : si je passe à 3,3Go, j'obtiendrais de meilleures performances, de l'ordre de 15% en schématisant, mais que ça ne sert à rien d'allouer plus car ensuite la progression stagne.
Correct ?

Pomalaix · 09/02/2011, 11h07

Citation:

Envoyé par korian

Donc si je comprends bien : si je passe à 3,3Go, j'obtiendrais de meilleures performances, de l'ordre de 15% en schématisant, mais que ça ne sert à rien d'allouer plus car ensuite la progression stagne.
Correct ?

Exact !
Mais il faut s'assurer d'avoir fait cette "mesure" à un moment où la base subissait une charge représentative, bien sûr.

korian · 09/02/2011, 11h32

Je viens de relancer la même requête et les résultats sont similaires.

Actuellement, la base est à dominante OLTP en journée et traitement par lot la nuit. Vous me conseillez donc d'effectuer cette mesure pendant la nuit aussi avant de prendre la décision ? Ok, je posterais les résultats demain.

Ce double test doit aussi être mené sur la SGA, je suppose ?

korian · 10/02/2011, 09h41

Bonne nouvelle

J'ai modifié la PGA à 1Go et le traitement quotidien est passé de 5h à 3h. Spectaculaire !

Maintenant, j'ai aussi interrogé les stats de la PGA et de la SGA de la soirée à ce matin.

Pour la SGA, quelque soit l'heure, les résultats sont comparables à ceux postés précédemment : il n'y a aucun gain de performances entre 3,3Go et 3,8Go.

Pour la PGA, je ne comprends pas certains résultats, la requête est toujours :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;

J'ai essayé de réorganiser les résultats pour faciliter la lecture

Pour 1Go (valeur actuelle)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2011.02.09.16.38	PGA	1024	51	0
2011.02.10.01.05	PGA	1024	97	0
2011.02.10.01.35	PGA	1024	97	0
2011.02.10.02.05	PGA	1024	97	0
2011.02.10.02.35	PGA	1024	97	0
2011.02.10.03.05	PGA	1024	40	0
2011.02.10.03.35	PGA	1024	39	0
2011.02.10.04.05	PGA	1024	39	0
2011.02.10.04.35	PGA	1024	38	0
2011.02.10.05.05	PGA	1024	38	0
2011.02.10.05.35	PGA	1024	38	0
2011.02.10.06.05	PGA	1024	38	0
2011.02.10.06.35	PGA	1024	38	0
2011.02.10.07.05	PGA	1024	37	0
2011.02.10.07.35	PGA	1024	37	0
2011.02.10.08.54	PGA	1024	37	0

Pour 128Mo

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
2011.02.10.02.05	PGA	128	83	41
2011.02.10.02.35	PGA	128	83	42
2011.02.10.03.05	PGA	128	34	50
2011.02.10.03.35	PGA	128	33	56
2011.02.10.04.05	PGA	128	32	63
2011.02.10.04.35	PGA	128	31	69
2011.02.10.05.05	PGA	128	31	76
2011.02.10.05.35	PGA	128	31	76
2011.02.10.06.05	PGA	128	31	76
2011.02.10.06.35	PGA	128	31	76
2011.02.10.07.05	PGA	128	30	83
2011.02.10.07.35	PGA	128	30	83
2011.02.10.08.54	PGA	128	30	86

Pour 8Go

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2011.02.09.16.38	PGA	8192	79	0
2011.02.10.01.05	PGA	8192	100	0
2011.02.10.01.35	PGA	8192	100	0
2011.02.10.02.05	PGA	8192	100	0
2011.02.10.02.35	PGA	8192	100	0
2011.02.10.03.05	PGA	8192	68	0
2011.02.10.03.35	PGA	8192	67	0
2011.02.10.04.05	PGA	8192	66	0
2011.02.10.04.35	PGA	8192	65	0
2011.02.10.05.05	PGA	8192	65	0
2011.02.10.05.35	PGA	8192	65	0
2011.02.10.06.05	PGA	8192	65	0
2011.02.10.06.35	PGA	8192	65	0
2011.02.10.07.05	PGA	8192	64	0
2011.02.10.07.35	PGA	8192	64	0
2011.02.10.08.54	PGA	8192	64	0

Pour 1,2Go

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2011.02.10.01.05	PGA	1229	100	0
2011.02.10.01.35	PGA	1229	100	0
2011.02.10.02.05	PGA	1229	100	0
2011.02.10.02.35	PGA	1229	100	0
2011.02.10.03.05	PGA	1229	68	0
2011.02.10.03.35	PGA	1229	67	0
2011.02.10.04.05	PGA	1229	66	0
2011.02.10.04.35	PGA	1229	65	0
2011.02.10.05.05	PGA	1229	65	0
2011.02.10.05.35	PGA	1229	65	0
2011.02.10.06.05	PGA	1229	65	0
2011.02.10.06.35	PGA	1229	65	0
2011.02.10.07.05	PGA	1229	64	0
2011.02.10.07.35	PGA	1229	64	0
2011.02.10.08.54	PGA	1229	64	0

Dès 1Go, ESTD_OVERALLOC_COUNT est toujours à 0 mais je trouve que ESTD_PGA_CACHE_HIT_PERCENTAGE reste quand même bas à 8Go (mais je n'ai pas assez d'expérience pour savoir si c'est normal).
A noter qu'à partir de 1,2Go jusqu'à 8Go, il n'y a pas d'augmentation de performances.

Qu'en pensez vous ?

Et encore merci pour vos conseils.

korian · 03/03/2011, 15h00

Je vais clore le message même si je n'ai pas obtenu de réponse à ma dernière question :
ESTD_PGA_CACHE_HIT_PERCENTAGE n'est pas proche de 100% (<66%) en journée (que la mémoire soit de 1,2Go ou 8Go). Sur quel critère/paramètre puis-je jouer pour l'augmenter comme pour la nuit (~100%) ?

Merci beaucoup pour votre aide et vos réponses

root_nizar · 03/03/2011, 17h09

Permet moi de ré-ouvrir le débat :D

Le calcul du paramétre cache hit percentage se fait comme suit:
BP*100/(BP+EBP)= 103*100/(103+1)= 99,03%
BP :(bytes processed, BP) et le nombre total d’octets traités
EBP :extra bytes read/written qui représente le nombre d’octets lus/écrits sur ces segments .

Tu peux me dire aprés l'augmentation de ta valeur PGA, il est à combien le paramétre bytes processed qui représente le nombre total d’octets traités par les opérations SQL (exemple :tris)

Il faut aussi vérifier si il y'a un tri sur
un volume de données important est exceptionnel

Cdt

korian · 04/03/2011, 08h23

Merci pour ton intérêt

Par contre j'avoue que je ne comprends pas tout ce que tu me dis

. Donc à moi de poser des questions

Pour les EBP, de quels segments tu parles ? A priori, un segment est une unité d'organisation logique, pas une unité de mémoire.

Je ne trouve aucun paramètre avec bytes ou processed dans leur nom. Peux tu me donner le nom exact stp ?

Je ne comprends pas la dernière phrase. Je dois vérifier s'il y a un tri sur un volume de données important ? ou est ce que le tri est exceptionnel ?

root_nizar · 04/03/2011, 11h20

Avec la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM V$PGASTAT

tu as le paramètre extra bytes READ/written (EBP) et bytes processed et c'est avec ces paramètres que tu calcules le cache hit percentage.

En fait il faut voir s'il y a des requêtes avec des grandes opérations de tri.

Peux-tu me donner le résultat de la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT to_number(decode(SID,65535,NULL, SID)) SID,
2 > operation_type OPERATION,
3 > trunc(EXPECTED_SIZE/1024) ESIZE,
4 > trunc(ACTUAL_MEM_USED/1024) MEM,
5 > trunc(MAX_MEM_USED/1024) “MAX MEM”,
6 > NUMBER_PASSES PASS,
7 > trunc(TEMPSEG_SIZE/1024) TSIZE
8 > FROM V$SQL_WORKAREA_ACTIVE
9 > ORDER BY 1, 2;

Cette vue permet ainsi d’observer l’ensemble des zones de travail actives pour déterminer si ces zones débordent sur des segments temporaires.

Cordialement.

korian · 04/03/2011, 11h52

Ok c'est plus clair.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SYS@SQL> SELECT * FROM V$PGASTAT;
 
NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                   1073741824 bytes
aggregate PGA auto target                                         805552128 bytes
global memory bound                                               107366400 bytes
total PGA inuse                                                   178778112 bytes
total PGA allocated                                               352995328 bytes
maximum PGA allocated                                             522891264 bytes
total freeable PGA memory                                          42008576 bytes
process count                                                            67
max processes count                                                      72
PGA memory freed back TO OS                                      3188260864 bytes
total PGA used FOR auto workareas                                         0 bytes
maximum PGA used FOR auto workareas                               187516928 bytes
total PGA used FOR manual workareas                                       0 bytes
maximum PGA used FOR manual workareas                                537600 bytes
over allocation count                                                     0
bytes processed                                                  2.3457E+10 bytes
extra bytes READ/written                                         7.1870E+10 bytes
cache hit percentage                                                   24.6 percent
recompute count (total)                                               19133
 
19 rows selected.

Résultat de la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SYS@SQL> SELECT   to_number(decode(SID,65535,NULL, SID)) SID,
  2           operation_type OPERATION,
  3           trunc(EXPECTED_SIZE/1024) ESIZE,
  4           trunc(ACTUAL_MEM_USED/1024) MEM,
  5           trunc(MAX_MEM_USED/1024) "MAX MEM",
  6           NUMBER_PASSES PASS,
  7           trunc(TEMPSEG_SIZE/1024) TSIZE
  8  FROM     V$SQL_WORKAREA_ACTIVE
  9  ORDER BY 1, 2;
 
no rows selected
 
SYS@SQL>

root_nizar · 04/03/2011, 15h28

Il n'y a aucune zone de travail en cours c'est bizarre vu que t'as dit que t'as ESTD_PGA_CACHE_HIT_PERCENTAGE n'est pas proche de 100% (<66%).

Je voudrais s'il te plaît le résultat de cette requête pour voir le nombre de zones de travail exécutées avec un dimensionnement mémoire optimale :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
2 > (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
3 > OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
4 > FROM V$SQL_WORKAREA_HISTOGRAM
5 > WHERE TOTAL_EXECUTIONS != 0;

korian · 07/03/2011, 09h02

Je remets le calcul du ESTD_PGA_CACHE_HIT_PERCENTAGE réalisé ce matin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SYS@SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM   V$PGA_TARGET_ADVICE;  2    3    4
 
 TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
       128             31                   78
       256             37                    0
       512             38                    0
       768             38                    0
      1024             38                    0
      1229             65                    0
      1434             65                    0
      1638             65                    0
      1843             65                    0
      2048             65                    0
      3072             65                    0
      4096             65                    0
      6144             65                    0
      8192             65                    0
 
14 rows selected.

Et le résultat de ta requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SYS@SQL> SELECT   LOW_OPTIMAL_SIZE/1024 low_kb,
         (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
         OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM     V$SQL_WORKAREA_HISTOGRAM
WHERE    TOTAL_EXECUTIONS != 0;  2    3    4    5
 
    LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
---------- ---------- ------------------ ------------------ ----------------------
         2          4             134682                  0                      0
        64        128                887                  0                      0
       128        256               1379                  0                      0
       256        512                122                  0                      0
       512       1024               1805                  0                      0
      1024       2048                 27                  0                      0
      2048       4096                 19                  0                      0
      4096       8192                 25                  0                      0
      8192      16384                 10                  0                      0
     16384      32768                  7                  4                      0
     32768      65536                  4                  0                      0
     65536     131072                  2                  1                      0
    524288    1048576                  0                  7                      1
   1048576    2097152                  0                  0                     14
 
14 rows selected.

root_nizar · 07/03/2011, 11h34

SI tu augmente la taille de la mémoire à 1,2 tu vas avoir 65 % du paramétre CACHE_HIT_PERC (pas mal).
Comme je l'ai mentionné la derniére fois il y'a un calcul qui se fait pour calculer le CACHE_HIT_PERC, et c'est la taille des opérations des tris qui influence le calcul.

Commence par optimiser toutes les requétes de tri durant la journée .

Cdt

root_nizar · 07/03/2011, 13h30

Re
Je voulais juste savoir ta mémoire RAM .
Juste pour info est ce que t'as utilisé cette méthode pour calculer PGA et SGA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
&#56256;&#56510; PGA = (total mémoire * 80%) * 20%
&#56256;&#56510; SGA = (total mémoire * 80%) – PGA

Merci

09/02/2011, 00h40	#4
Pomalaix Rédacteur Inscription : décembre 2002 Messages : 2 385 Détails du profil Informations personnelles : Localisation : France, Var (Provence Alpes Côte d'Azur) Informations forums : Inscription : décembre 2002 Messages : 2 385 Points : 3 261 Points : 3 261	Votre PGA est effectivement très sous-dimensionnée : 24 Mo officiellement alloués, plus de 500 Mo consommés en pointe (maximum PGA allocated 537 795 584 bytes). A telle enseigne que V$PGA_TARGET_ADVICE, qui fait des estimations à hauteur de 8 fois la taille actuelle de PGA_AGGREGATE_TARGET, est incapable de vous fournir une valeur cible qui induirait une amélioration notable : même à 192 Mo, les choses ne changeraient guère, car il en faut beaucoup plus. Il me semble qu'il y a une contradiction entre le "cache hit pourcentage" désastreux de 32% d'après V$PGASTAT, et le taux de de près de 95% de "optimal_perc" que vous ramenez de v$sql_workarea_histogram. A mon avis, la condition "WHERE low_optimal_size > 64*1024" n'est pas pertinente. Il faudrait la supprimer, et je pense que vous retrouverez là aussi un "optimal_perc" très faible. __________________ Consultant / formateur Oracle indépendant Certifié OCP 10g et 11g, sécurité 11g
	20

03/03/2011, 17h09	#13
root_nizar Membre du Club Inscription : octobre 2008 Messages : 59 Détails du profil Informations forums : Inscription : octobre 2008 Messages : 59 Points : 65 Points : 65	Bonjour Permet moi de ré-ouvrir le débat :D Le calcul du paramétre cache hit percentage se fait comme suit: BP100/(BP+EBP)= 103100/(103+1)= 99,03% BP :(bytes processed, BP) et le nombre total d’octets traités EBP :extra bytes read/written qui représente le nombre d’octets lus/écrits sur ces segments . Tu peux me dire aprés l'augmentation de ta valeur PGA, il est à combien le paramétre bytes processed qui représente le nombre total d’octets traités par les opérations SQL (exemple :tris) Il faut aussi vérifier si il y'a un tri sur un volume de données important est exceptionnel Cdt
	00

07/03/2011, 11h34	#19
root_nizar Membre du Club Inscription : octobre 2008 Messages : 59 Détails du profil Informations forums : Inscription : octobre 2008 Messages : 59 Points : 65 Points : 65	Bonjour SI tu augmente la taille de la mémoire à 1,2 tu vas avoir 65 % du paramétre CACHE_HIT_PERC (pas mal). Comme je l'ai mentionné la derniére fois il y'a un calcul qui se fait pour calculer le CACHE_HIT_PERC, et c'est la taille des opérations des tris qui influence le calcul. Commence par optimiser toutes les requétes de tri durant la journée . Cdt
	00

08/02/2011, 18h45	#3
Heaven93 Membre éprouvé Administrateur de base de données Inscription : novembre 2007 Messages : 341 Détails du profil Informations personnelles : Sexe : Localisation : France Informations professionnelles : Activité : Administrateur de base de données Informations forums : Inscription : novembre 2007 Messages : 341 Points : 478 Points : 478	et suite à l'augmentation de la PGA, collecter les stats avec un awr sur la période de batch et voir la partie des advisors à la fin pour redimensionner la PGA si nécessaire.
	00

09/02/2011, 11h32	#10
korian Nouveau Membre du Club Inscription : juillet 2007 Messages : 87 Détails du profil Informations forums : Inscription : juillet 2007 Messages : 87 Points : 29 Points : 29	Je viens de relancer la même requête et les résultats sont similaires. Actuellement, la base est à dominante OLTP en journée et traitement par lot la nuit. Vous me conseillez donc d'effectuer cette mesure pendant la nuit aussi avant de prendre la décision ? Ok, je posterais les résultats demain. Ce double test doit aussi être mené sur la SGA, je suppose ?
	00

03/03/2011, 15h00	#12
korian Nouveau Membre du Club Inscription : juillet 2007 Messages : 87 Détails du profil Informations forums : Inscription : juillet 2007 Messages : 87 Points : 29 Points : 29	Je vais clore le message même si je n'ai pas obtenu de réponse à ma dernière question : ESTD_PGA_CACHE_HIT_PERCENTAGE n'est pas proche de 100% (<66%) en journée (que la mémoire soit de 1,2Go ou 8Go). Sur quel critère/paramètre puis-je jouer pour l'augmenter comme pour la nuit (~100%) ? Merci beaucoup pour votre aide et vos réponses
	00

04/03/2011, 08h23	#14
korian Nouveau Membre du Club Inscription : juillet 2007 Messages : 87 Détails du profil Informations forums : Inscription : juillet 2007 Messages : 87 Points : 29 Points : 29	Merci pour ton intérêt Par contre j'avoue que je ne comprends pas tout ce que tu me dis . Donc à moi de poser des questions Pour les EBP, de quels segments tu parles ? A priori, un segment est une unité d'organisation logique, pas une unité de mémoire. Je ne trouve aucun paramètre avec bytes ou processed dans leur nom. Peux tu me donner le nom exact stp ? Je ne comprends pas la dernière phrase. Je dois vérifier s'il y a un tri sur un volume de données important ? ou est ce que le tri est exceptionnel ?
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email