Discussion :

[brandtance]

Bonjour,

Nous développons une application fonctionnant sous Oracle et qui est installée chez plusieurs clients.
Pour plus de 30 bases de données, l'instance Oracle ne consomme pas plus de 800Mo de la mémoire vive. Mais pour une instance, la consommation de mémoire par Oracle monte à plus de 3Go!! J'ai déjà redémarré l'instance, mais elle remontre au bout de quelques heures à 3Go.

Pourquoi une telle différence entre les bases ?

Voici la vue v$sgainfo :

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
select name, bytes/1024/1024 as Mo from v$sgainfo;
 
NAME                                     MO
-------------------------------- ----------
Fixed SGA Size                   1,91465759
Redo Buffers                     14,0820313
Buffer Cache Size                      2672
Shared Pool Size                        336
Large Pool Size                          16
Java Pool Size                           16
Streams Pool Size                         0
Granule Size                             16
Maximum SGA Size                       3056
Startup overhead in Shared Pool          80
Free SGA Memory Available                 0

Les bases de données fonctionnent sous Windows.

D'avance merci,

[brandtance]

En complément, mais je ne sais pas si cela peut vous aider...voici les traces dans l'alert log :

Memory Notification: Library Cache Object loaded into SGA
Heap size 2114K exceeds notification threshold (2048K)
KGL object name :
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), count("VAR52AGEDEBUTCONSO03"), count(distinct "VAR52AGEDEBUTCONSO03"), sum(sys_op_opnsize("VAR52AGEDEBUTCONSO03")), substrb(dump(min("VAR52AGEDEBUTCONSO03"),16,0,32),1,120), substrb(dump(max("VAR52AGEDEBUTCONSO03"),16,0,32),1,120), count("VAR53PRODUIT04"), count(distinct "VAR53PRODUIT04"), sum(sys_op_opnsize("VAR53PRODUIT04")), substrb(dump(min("VAR53PRODUIT04"),16,0,32), 1,

[pifor]

Comment mesurez vous la mémoire utilisée par l'instance ?

On a toujours mémoire instance = SGA + PGA.
Jusqu'en 10G la SGA est de taille fixe. La PGA peut varier.

Pouvez-vous poster le résultat avec SQL*Plus:

Code :

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

1
2
3
4
5
select * from v$version;
show parameter sga
show parameter pga
select * from v$sgastat;
select * from v$pgastat;

[brandtance]

Bonjour,

Merci pour la réponse,

Comment mesurez vous la mémoire utilisée par l'instance ?

Avec le gestionnaire des tâches Windows on se rends compte que le oracle.exe est à plus de 3Go.

Code :

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

1
2
3
4
5
6
7
 
select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Code :

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

1
2
3
4
5
6
7
8
9
 
SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3204448256
sga_target                           big integer 3204448256

Code :

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

1
2
3
4
5
6
 
SQL> show parameter pga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
pga_aggregate_target                 big integer 1062207488

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
SQL> select * from v$pgastat;
 
NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
UNIT                                                                            
------------                                                                    
aggregate PGA target parameter                                   1062207488     
bytes                                                                           
 
aggregate PGA auto target                                         877086720     
bytes                                                                           
 
global memory bound                                               106219520     
bytes                                                                           
 
 
NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
UNIT                                                                            
------------                                                                    
total PGA inuse                                                    87752704     
bytes                                                                           
 
total PGA allocated                                               192100352     
bytes                                                                           
 
maximum PGA allocated                                             785648640     
bytes                                                                           
 
 
NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
UNIT                                                                            
------------                                                                    
total freeable PGA memory                                          70909952     
bytes                                                                           
 
process count                                                            92     
 
 
max processes count                                                     232     
 
 
 
NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
UNIT                                                                            
------------                                                                    
PGA memory freed back to OS                                      2,3981E+13     
bytes                                                                           
 
total PGA used for auto workareas                                         0     
bytes                                                                           
 
maximum PGA used for auto workareas                               431240192     
bytes                                                                           
 
 
NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
UNIT                                                                            
------------                                                                    
total PGA used for manual workareas                                       0     
bytes                                                                           
 
maximum PGA used for manual workareas                                531456     
bytes                                                                           
 
over allocation count                                                     0     
 
 
 
NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
UNIT                                                                            
------------                                                                    
bytes processed                                                  1,8334E+13     
bytes                                                                           
 
extra bytes read/written                                         1,1225E+12     
bytes                                                                           
 
cache hit percentage                                                  94,23     
percent                                                                         
 
 
NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
UNIT                                                                            
------------                                                                    
recompute count (total)                                             2316504     
 
 
 
19 ligne(s) sélectionnée(s).
 
SQL> spool off

La requête sur la vue v$sgastat se trouve dans le fichier texte car elle renvoyait trop de lignes.

D'avance merci,

[pifor]

Vous avez SGA_TARGET quif ait 2,98 Go et la PGA qui varie entre entre 180 Mo et 800 Mo. Il est donc normal que oracle.exe utilise 3 Go.

[brandtance]

Vous avez SGA_TARGET quif ait 2,98 Go et la PGA qui varie entre entre 180 Mo et 800 Mo. Il est donc normal que oracle.exe utilise 3 Go.

Ok, mais si je me trompe pas, je suis en gestion automatique de la mémoire...Comme sur toutes nos bases. Toutes nos bases utilisent la même application. Mais il y a uniquement cette instance qui utilise 3Go.
J'aimerai comprendre pourquoi cette différence...Alors que la gestion est la même.

D'avance merci,

[pifor]

Il se peut qu'il y a plus d'activité sur l'instance en question et qu'il y ait donc plus de mémoire utilisée dans la SGA: vous pouvez essayer de comparer avec:

Code :

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

1
2
 
select sum(bytes) from v$sgastat where name ='free memory';

Si vous positionnez SGA_TARGET à 2,98 Go alors Oracle est libre d'utiliser toute cette mémoire pour la SGA: la gestion automatique concerne uniquement la répartition entre les différentes partie de la SGA (shared pool, buffer cache, etc).

[brandtance]

Si vous positionnez SGA_TARGET à 2,98 Go alors Oracle est libre d'utiliser toute cette mémoire pour la SGA: la gestion automatique concerne uniquement la répartition entre les différentes partie de la SGA (shared pool, buffer cache, etc).

Ok! Donc en fait, il faut que je baisse la valeur SGA_TARGET ?

Sur la base qui utilise 3Go j'ai :

Code :

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

1
2
3
4
5
6
 
SELECT sum(bytes) FROM v$sgastat WHERE name ='free memory';
 
SUM(BYTES)
----------
 131949728

Et sur une autre j'ai :

Code :

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

1
2
3
4
5
6
 
SQL> SELECT sum(bytes) FROM v$sgastat WHERE name ='free memory';
 
SUM(BYTES)
----------
  89370688

Désolé de faire le boulet, mais qu'est ce que ces valeurs signifient ? En tout cas, vraiment merci pour votre aide!!!

[brandtance]

Encore une question : comment Oracle détermine la valeur de SGA_TARGET et SGA_MAXSIZE ?

Merci,

[pifor]

Ok! Donc en fait, il faut que je baisse la valeur SGA_TARGET ?

Oui si vous voulez réduire la taille de la SGA pour diminuer la taille globale de mémoire utilisée par l'instance, en sachant que cela peut influer de façon négative sur les performances.

Désolé de faire le boulet, mais qu'est ce que ces valeurs signifient ? En tout cas, vraiment merci pour votre aide!!!

Elles signifient qu'il y a un peu d'espace non utilisé dans la SGA.

SGA_TARGET et SGA_MAX_SIZE sont des paramètres d'instance:
http://download.oracle.com/docs/cd/B....htm#sthref390. SGA_TARGET peut être diminué sans redémarrer l'instance.

[brandtance]

Elles signifient qu'il y a un peu d'espace non utilisé dans la SGA.

Ok super! Vraiment merci pour toutes ces informations! Très très enrichissante...la gestion de la mémoire Oracle..c'est pas facile!

Bonne continuation!

Ah oui, pour info, j'ai réalisé les modifications et ça fonctionne nickel. En fait plus on a de mémoire vive, plus Oracle va allouer de la mémoire à la SGA ? c'est ça ?

[pifor]

Non la SGA est de taille fixe. C'est la PGA qui peut varier en fonction de l'activité des sessions (tris, jointures, allocation de variables PL/SQL). La PGA varie en fonction de l'activité des sessions et non en fonction de la mémoire disponible sur la machine (bien sûr, s'il n'y a plus de mémoire disponible au niveau machine vous aurez l'erreur ORA-04030).

[brandtance]

Oui oui, ca c'est OK. Ce que je voulais dire c'est que la valeur de la SGA_TARGET est défini comment ? Je sais qu'elle ne varie pas..mais sa valeur initiale ?

[pifor]

C'est un paramètre d'initialisation de l'instance qui est définie dans le fichier d'initialisation SPFILE ou PFILE http://download.oracle.com/docs/cd/B...1.htm#i1124342

[brandtance]

Je vous dérange pas plus longtemps.
Merci pour tout! Je pense avoir bien compris le principe.

[brandtance]

Finalement je reviens....
Après une journée passée sans aucun soucis...je me rends compte que mon instance Oracle dépasse à nouveau le giga...

Est ce que ma PGA n'est pas trop élevée ?
(les données se trouvent plus haut -> show parameter pga)

Merci,

[mongolic]

Vous pouvez exécuter la requête suivante qui vous donnera des indications sur le dimensionnement de la pga

Code :

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

select * from v$pga_target_advice;