求教关于如何手动设置PGA

yingyifeng

之前一直不清楚PGA手动是如何设置的，今天自己测试了一下，发现了很多的问题，MOS上也没找到相关的！想请教一下基本的设置步骤，是不是只要设置workarea_size_policy为manual就行了，pga_aggregate_target好像不能设置为0，相关的参数怎么调整。

yingyifeng

Liu实在是太细致了！太感激了！

Maclean Liu(刘相兵

Question:
不过sort_area_retained_size这个参数需要设置么？如果是手动管理的话？因为之前是自动管理的，这个值为0

Answer:

一般不需要；sort_area_retained_size 会参考 sort_area_size的大小；The default value as reflected in the V$PARAMETER dynamic performance view is 0. However, if you do not explicitly set this parameter, Oracle actually uses the value of the SORT_AREA_SIZE parameter.

Maclean Liu(刘相兵

example 例子：


alter session set workarea_size_policy=MANUAL;      ==> session级别 workarea_size_policy=MANUAL 手动管理PGA
alter session set workarea_size_policy=MANUAL;

REM set sort_area_size to 700M or 1.6 * table_size
REM 10g bug need to set sort_area_size twice
REM remember large sort area size doesn't mean better performance
REM sometimes you should reduce below setting,and then sort may benefit from disk sort
REM and attention to avoid PGA swap

alter session set sort_area_size=734003200;
alter session set sort_area_size=734003200;

==> session级别指定sort_area_size 排序空间 ， 注意10g存在bug  需要2次设置 sort_area_size 才能生效！


Reference: http://www.oracledatabase12g.com ... create-rebuild.html

Maclean Liu(刘相兵

ODM data:

The automatic SQL execution memory management feature is enabled by setting the
parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of
PGA_AGGREGATE_TARGET in the initialization file. These two parameters can also be
set dynamically using the ALTER SYSTEM command. In the absence of either of these
parameters, the database will revert to manual PGA management mode. In Oracle9i
Release 2, an advisory for PGA_AGGREGATE_TARGET was introduced. Just like in Buffer
Cache Advisory, the PGA Advisory will suggest the appropriate size for PGA memory
and thus make PGA tuning an even simpler task.