Monitoring Memory Resizing Operations with Memory_target Parameter.

To enable memory target

SQL> alter system set memory_max_target=5G scope=spfile;

SQL> alter system set memory_target=4G scope=spfile;

Turn off the SGA_TARGET and the PGA_AGGREGATE_TARGET
parameters by issuing the following ALTER SYSTEM commands:

SQL> alter system set sga_target = 0;

SQL> alter system set pga_aggregate_target = 0;

SQL>shutdown immediate;

SQL>startup


Under an automatic memory management mode, you can view the current allocations of
memory inany instance by querying the V$MEMORY_DYNAMIC_COMPONENTS
view. Querying this view provides vitalinformation to help you tune the MEMORY_TARGET parameter. Here’s how you execute a query against thisview:

SQL> select * from v$memory_target_advice order by memory_size;

Your current memory allocation is shown by the row with the MEMORY_SIZE_FACTOR value of 1 . The MEMORY_SIZE_FACTOR column shows alternate sizes of theMEMORY_TARGET
parameteras a multiple of the current MEMORY_TARGET parameter value. The
ESTD_DB_TIME column shows the timeOracle estimates it will need to complete the current workload with a specific MEMORY_TARGET value. Thus, the query results show you how much faster the database can process its work by varying the value of the
MEMORY_TARGET parameter.