MEMORY_TARGET, MEMORY_MAX_TARGET

Pour calculer les valeurs des paramètres MEMORY_TARGET, MEMORY_MAX_TARGET à partir des valeurs actuelles de SGA_TARGET, PGA_AGGREGATE_TARGET, SGA_MAX_TARGET, utiliser le script suivant:

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 sid=''*'';'||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 sid=''*'';'||chr(10)||

       'Alter system reset sga_target scope=spfile sid=''*'';'||chr(10)||

       'Alter system reset pga_aggregate_target scope=spfile sid=''*'';'||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 sid=''*'';' 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

;

 

Le script génère les commandes à lancer pour positionner les paramètres sus-cités.

Exemple:

SQL> Select '-- Memory Target = '||round(to_char((qry_sga_target.value+
  2          greatest(qry_pga_target.value,qry_pga_alloc.value)))/1024/1024)||' MB'||chr(10)||
  3         'Alter system set memory_target='||
  4    to_char((qry_sga_target.value+greatest(qry_pga_target.value,qry_pga_alloc.value)))||' scope=spfile sid=''*'';'||chr(10)||
  5         '-- Memory Max Target = '||round(to_char((qry_sga_max.value+
  6         greatest(qry_pga_target.value,qry_pga_alloc.value)))/1024/1024)||' MB'||chr(10)||
  7         'Alter system set memory_max_target='||
  8   to_char((qry_sga_max.value+greatest(qry_pga_target.value,qry_pga_alloc.value)))||' scope=spfile sid=''*'';'||chr(10)||
  9         'Alter system reset sga_target scope=spfile sid=''*'';'||chr(10)||
 10         'Alter system reset pga_aggregate_target scope=spfile sid=''*'';'||chr(10)||
 11         '-- SGA_MAX_SIZE ne doit pas etre superieur a MEMORY_TARGET'||chr(10)||
 12         'Alter system set sga_max_size='||to_char((qry_sga_target.value+
             greatest(qry_pga_target.value,qry_pga_alloc.value)))||' scope=spfile sid=''*'';' CMD_SQL
 13   14    from (select value from v$parameter where name='sga_target') qry_sga_target,
 15         (select value from v$parameter where name='pga_aggregate_target') qry_pga_target,
 16         (select value from v$pgastat where name='maximum PGA allocated') qry_pga_alloc,
 17         (select value from v$parameter where name='sga_max_size') qry_sga_max
 18  ;

CMD_SQL
--------------------------------------------------------------------------------
-- Memory Target = 5428 MB
Alter system set memory_target=5691155456 scope=spfile sid='*';
-- Memory Max Target = 6436 MB
Alter system set memory_max_target=6748120064 scope=spfile sid='*';
Alter system reset sga_target scope=spfile sid='*';
Alter system reset pga_aggregate_target scope=spfile sid='*';
-- SGA_MAX_SIZE ne doit pas etre superieur a MEMORY_TARGET
Alter system set sga_max_size=5691155456 scope=spfile sid='*';

Hope it helps...