11g自动内存设置(二)

c：Compute the maximum value between the query result from step 2b and PGA_AGGREGATE_TARGET. Add SGA_TARGET to this value.

计算memory_target 最大值

memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

For example, if SGA_TARGET is 272M and PGA_AGGREGATE_TARGET is 90M as shown above, and if the maximum PGA allocated is determined to be 120M, then MEMORY_TARGET should be at least 392M (272M + 120M).

例如，如果SGA_TARGET is 272M， PGA_AGGREGATE_TARGET is 90M，maximum PGA allocated is 120M，那么MEMORY_TARGET 的最小值为：392M (272M + 120M)。

这只是他的最小值！你也可以将值设置的更大，前提是有足够的物理内存！

3：For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as the MEMORY_TARGET value that you chose in the previous step.

MEMORY_MAX_TARGET 这个参数的设置要大于或者等于MEMORY_TARGET 参数的值。根据网上的一些经验之谈MEMORY_MAX_TARGET = OS_MEMORY*80%这参数的值没有固定公式可参考！要根据实际情况来确定！！

修改参数的脚本：

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;

如果修改了该参数MEMORY_MAX_TARGET 需重启实例！

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

动态视图V$MEMORY_DYNAMIC_COMPONENTS 显示了当前动态内存

组件的大小。包括SGA,PGA的总大小！

The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter.

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 several 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, estimated DB time increases. Notice also that in this example, there is nothing to be gained by increasing total memory size beyond 450MB. However, this situation might change if a complete workload has not yet been run.