Total Memory Sizing (MEMORY_TARGET)

MEMORY_TARGET specifies the Oracle system-wide usable memory, including both SGA and PGA. Prior to Oracle database release 11g, SGA and PGA had to be tuned separately.

If a database is upgraded from Oracle 10g to 11g, MEMORY_TARGET can be determined by simply adding SGA_TARGET and PGA_AGGREGATE_TARGET from the Oracle 10g database.

If a database is upgraded from Oracle 9i to 11g, MEMORY_TARGET can be determined by adding PGA_AGGREGATE_TARGET and all SGA components, including DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, etc.

If a database has not been upgraded from an earlier version, and has no history references, Oracle recommends this parameter to be initially set to 1 to 3 GB, depending on system resources and system limits. After the database has been in use for some time, this parameter can be tuned as follows. (This tuning also applies to the above two upgrade scenarios).

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

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION

180 .5 458 1.344 0

270 .75 367 1.0761 0

360 1 341 1 0

450 1.25 335 .9817 0

540 1.5 335 .9817 0

630 1.75 335 .9817 0

720 2 335 .9817 0

The row with the MEMORY_SIZE_FACTOR of 1 shows the current size of memory, as set by the MEMORY_TARGET initialization parameter, and the amount of DB time required to complete the current workload. In previous and subsequent rows, the results show a number of alternative MEMORY_TARGET sizes. For each alternative size, the database shows the size factor (the multiple of the current size), and the estimated DB time to complete the current workload if the MEMORY_TARGET parameter were changed to the alternative size. Notice that for a total memory size smaller than the current MEMORY_TARGET size (360 in this example), estimated DB time (ESTD_DB_TIME) increases. Notice also that in this example, there is nothing to be gained by increasing total memory size beyond 450MB, because the ESTD_DB_TIME value is not decreasing. Therefore, in this example, the suggested MEMORY_TARGET size is 450 MB.