Automatic Memory Management -MEMORY_TARGET

Depuis la version 10g, oracle a sorti la notion de «Automatic Shared Memory Management» qui consiste à positionner le paramètre SGA_TARGET à une valeur différente de 0 pour que les différentes composantes du SGA soit gérées de façon automatique.

Avec la version 11g, oracle est allé un peu plus loin en introduisant la notion de «Automatic Memory Management». Vous constaterez que seul le petit mot «Shared» a sauté. Cependant, l'impact est assez majeur.

Cette nouvelle notion introduit les paramètres MEMORY_TARGET et MEMORY_MAX_TARGET.
Je ne détaillerai pas trop ces notions car on peut trouver toute sorte d'information dans la documentation oracle ou sur «My Oracle Support». D'ailleurs, deux notes assez intéressantes parlent très bien de ce sujet:
11g MEMORY_TARGET Parameter Dependency [ID 452512.1]
Automatic Memory Management(AMM) on 11g [ID 443746.1]

L'objectif de cet article est de proposer un script pour générer de façon automatique un autre script qui contiendra les requêtes sql à exécuter pour positionner les valeurs des paramètres.

Ce script m'a été proposé par mon collègue Éric Cloutier (il ne faut pas recréer la roue, on avance plus vite en utilisant ce que les autres ont déjà fait: leçon à retenir).

Ce script ira chercher les valeurs actuelles des paramètres SGA_TARGET et PGA_AGGREGATE_TARGET pour construire les valeurs des paramètres MEMORY_TARGET, MEMORY_MAX_TARGET et SGA_MAX_SIZE.

Ensuite il enlèvera les valeurs des paramètres SGA_TARGET et PGA_AGGREGATE_TARGET.

Voici le corps du script:


set heading off
set pagesize 0
set linesize 150
set echo off

spool rslt_memory_target.sql
select 'spool rslt_memory_target.log' from dual;
Select '-- Memory Target = 'round(to_char((qry_sga_target.value+
greatest(qry_pga_target.value,qry_pga_alloc.value)))/1024/1024)' MB'chr(10)
'Alter system set memory_target='
to_char((qry_sga_target.value+greatest(qry_pga_target.value,qry_pga_alloc.value)))' scope=spfile;'chr(10)
'-- Memory Max Target = 'round(to_char((qry_sga_max.value+
greatest(qry_pga_target.value,qry_pga_alloc.value)))/1024/1024)' MB'chr(10)
'Alter system set memory_max_target='
to_char((qry_sga_max.value+greatest(qry_pga_target.value,qry_pga_alloc.value)))' scope=spfile;'chr(10)
'Alter system reset sga_target scope=spfile;'chr(10)
'Alter system reset pga_aggregate_target scope=spfile;'chr(10)
'-- SGA_MAX_SIZE ne doit pas etre superieur a MEMORY_TARGET'chr(10)
'Alter system set sga_max_size='to_char((qry_sga_target.value+
greatest(qry_pga_target.value,qry_pga_alloc.value)))' scope=spfile;' CMD_SQL
from (select value from v$parameter where name='sga_target') qry_sga_target,
(select value from v$parameter where name='pga_aggregate_target') qry_pga_target,
(select value from v$pgastat where name='maximum PGA allocated') qry_pga_alloc,
(select value from v$parameter where name='sga_max_size') qry_sga_max
;
select 'spool off' from dual;
spool off

Une fois exécuté, il va générer un script appelé «rslt_memory_target.sql» (chacun est libre de le nommer comme il veut):
--- Les valeurs ci-dessous sont les valeurs de ma bd de test.

spool rslt_memory_target.log
-- Memory Target = 3775 MB
Alter system set memory_target=3958383616 scope=spfile;
-- Memory Max Target = 3775 MB
Alter system set memory_max_target=3958383616 scope=spfile;
Alter system reset sga_target scope=spfile;
Alter system reset pga_aggregate_target scope=spfile;
-- SGA_MAX_SIZE ne doit pas etre superieur a MEMORY_TARGET
Alter system set sga_max_size=3958383616 scope=spfile;
spool off

Note:
Dans cet exemple, tous les paramètres ont la même valeur. Si vous le désirez, vous pouvez les adapter tout en sachant que:
- Le SGA_MAX_SIZE ne doit pas etre supérieur à MEMORY_TARGET
- Le MEMORY_TARGET ne doit pas être supérieur au MEMORY_MAX_TARGET

Vous pouvez donc exécuter le scipt «rslt_memory_target.sql» pour prendre ces valeurs en compte.

Certains de ces paramètres ne pouvant pas être modifiés de façon dynamique (d'où l'utilisation du scope=spfile au lieu de scope=both), il faut redémarrer la bd:

Avec SQL:
sqlplus / as sysdba
SQL> shutdown immediate
...
SQL> startup

Avec Oracle Restart ou RAC (nous parlerons de ces notions dans des articles futurs):
srvctl stop database -d $ORACLE_SID [-o immediate]
srvctl start database -d $ORACLE_SID

NOTE IMPORTANTE:

Tout ce qui a été décrit ci-dessus n'est valable que si vous utilisez un SPFILE.
Si pour une raison quelconque, vous utilisez un PFILE (ce que je ne conseille pas, car il est mieux d'utiliser le spfile pour bénéficier des flexibilités qu'il ofrre):

- Positionner les paramètre dans le fichier PFILE:

memory_target=3958383616
memory_max_target=3958383616
sga_target=0
pga_aggregate_target=0
sga_max_size=3958383616

- Arrêter la bd
sqlplus / as sysdba
SQL> shutdown immediate

- Démarrer la bd avec le pfile:
SQL> startup pfile=initXXX.ora

N'hésitez surtout pas si vous avez des commentaires, remarques et suggestions.