oracle三对内存参数间关系之5AMM启动和关闭两种情况下pga自动管

oracle三对内存参数间关系之4 AMM 启动和关闭两种情况下 pga_aggregate_target和workarea_size_policy参数间的关系 引言： 在AMM关闭时（即memory_target=0），如果设置workarea_size_policy='AUTO‘，才是启动了 pga自动管理机制（程序），此时 pga_aggrega

oracle三对内存参数间关系之4AMM启动和关闭两种情况下

pga_aggregate_target和workarea_size_policy参数间的关系

引言：

在AMM关闭时（即memory_target=0），如果设置workarea_size_policy='AUTO‘，才是启动了pga自动管理机制（程序），此时pga_aggregate_target为各个oracle进程的pga之和的最大&＃20540;（确切地说，各个oracle进程的pga之和围绕着pga_aggregate_target上下浮动，如何证明这点？说法来源于PGA自动管理原理深入分析及性能调整），

而pga自动管理机制启动与否不决定于pga_aggregate_target&＃20540;；

在AMM启动时（即memory_target=非0），即使设置workarea_size_policy='AUTO‘，也没有启动pga自动管理机制（程序），此时pga_aggregate_target为各个oracle进程的pga之和的最小&＃20540;而已。

也就是说memory_target=非0时即AMM启动时，无论workarea_size_policy为manual还是auto，pga自动管理机制皆启动不了。

一、

在memory_target=非0即AMM启动的情况下

详见：

AMM启动下,pga_aggregate_target和workarea_size_policy各自无论如何修改,都不会影响对方的&＃20540; 结论：

1、AMM启动下,

pga_aggregate_target和workarea_size_policy各自无论如何修改,都不会影响对方的&＃20540;.

因为AMM启动时,pga自动管理机制处于关闭状态,故而pga_aggregate_target和workarea_size_policy参数间不存在任何关系,是相互独立的变量.

2、memory_target=非0时即AMM启动时，pga_aggregate_target 可以设置为0。

二、

在memory_target=0即AMM关闭的情况下

2.1实例启动前的参数文件里

2.1.1pga_aggregate_target=非0时，无论workarea_size_policy为什么&＃20540;，实例都可以启动成功。

2.1.2当pga_aggregate_target =0，且workarea_size_policy=auto时，实例启动不成功

下面是具体的实验：

（参数文件里workarea_size_policy=auto，memory_target=0，memory_max_target=1400M，pga_aggregate_target 为0，还有，sga_max_size=0，sga_target=0）

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORA-04032: pga_aggregate_targetmust be set before switching to auto mode

ORA-01078: 处理系统参数失败

2.1.3当pga_aggregate_target =0，且workarea_size_policy=MANUAL时，实例启动成功

下面是具体的实验：

（参数文件里memory_target=0，memory_max_target=1400M，pga_aggregate_target 为0，workarea_size_policy=manual，还有，sga_max_size=0，sga_target=0）

可以启动实例

SQL> select name,value from v$parameter where name like '%target%' orname like

'%sga%';

NAME VALUE

------------------------ --------------------------------------------------------

sga_max_size 150994944

pre_page_sga FALSE

lock_sga FALSE

sga_target 0

memory_target 0

memory_max_target 1468006400

archive_lag_target 0

fast_start_io_target 0

fast_start_mttr_target 0

db_flashback_retention_target 1440

pga_aggregate_target 0

parallel_servers_target 32

已选择12行。

注释：

以参数文件里pga_aggregate_target 为0，workarea_size_policy=manual

启动实例时，若是以网络连接方式（sqlplus @ip：服务名）连接到该实例的话，提示ora-12518错误，因为pga内存太小设置。

参见：解决ORA-12518的办法

2.2实例运行期间

2.2.1实例运行期间，pga_aggregate_target 不能允许设置为0

实验：

实例运行期间（参数文件里memory_target=0，memory_max_target=1400M，pga_aggregate_target=100M，workarea_size_policy=manual，还有，sga_max_size=500M，sga_target=0）

SQL>alter system set workarea_size_policy=manual;

系统已更改。

SQL>alter system set pga_aggregate_target=0;

altersystem set pga_aggregate_target=0

*

第 1 行出现错误:

ORA-02097:无法修改参数, 因为指定的&＃20540;无效

ORA-00093:pga_aggregate_target 必须介于 10M 和 4096G-1 之间

SQL>alter system set workarea_size_policy=auto;

系统已更改。

SQL>alter system set pga_aggregate_target=0;

altersystem set pga_aggregate_target=0

*

第 1 行出现错误:

ORA-02097:无法修改参数, 因为指定的&＃20540;无效

ORA-00093:pga_aggregate_target 必须介于 10M 和 4096G-1 之间

上述实验说明，memory_target=0时即AMM关闭时，实例运行期间，无论workarea_size_policy为manual还是auto，pga_aggregate_target 不能允许设置为0。

workarea_size_policy为 MANUAL时

pga_aggregate_target 可以为0也可以为非0，因为此时pga_aggregate_target （对应的pga自动管理机制） 不起作用。

结合2.2.1和2.1.3、2.1.3，可以得出如下结论：

memory_target=0时即AMM关闭时，无论workarea_size_policy为manual还是auto，pga_aggregate_target 都不能允许设置为0。除了实例以workarea_size_policy=manual启动实例时可以在参数文件里pga_aggregate_target 可以设置为0，实例启动后显示的pga_aggregate_target的&＃20540;为0。

而由一大点里的结论2可知，memory_target=非0时即AMM启动时，pga_aggregate_target 可以设置为0。

简单地说，就是

memory_target=0即手动管理时，在实例运行期间，pga_aggregate_target不能改为0，而memory_target=非0即自动管理时，在实例运行期间，pga_aggregate_target可以改为0。

2.2.2实例运行期间，当pga_aggregate_target=0时将workarea_size_policy从manual修改为auto是修改不了的

【参数A不变时，参数B改变&＃20540;可否 的例子】

参数文件：

*.memory_max_target=700M

*.memory_target=0

*.pga_aggregate_target=0

*.sga_max_size=500M

*.sga_target=500M

*.workarea_size_policy='MANUAL'

Microsoft Windows [版本 6.1.7600]

C:\Windows\System32>set oracle_sid=monkey

C:\Windows\System32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 5 09:26:532014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

已连接到空闲例程。

SQL> startuppfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORACLE 例程已经启动。

Total System Global Area 523108352 bytes

Fixed Size 1375704 bytes

Variable Size 159384104 bytes

Database Buffers 356515840 bytes

Redo Buffers 5832704 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameters pga

NAME TYPE VALUE

----------------------------------------------- ------------------------------

pga_aggregate_target big integer 0

SQL> show parameters policy

NAME TYPE VALUE

----------------------------------------------- ------------------------------

parallel_degree_policy string MANUAL

workarea_size_policy string MANUAL

SQL> alter system set workarea_size_policy=auto;

alter system set workarea_size_policy=auto

*

第 1 行出现错误:

ORA-02097: 无法修改参数, 因为指定的&＃20540;无效

ORA-04032: pga_aggregate_target必须在切换到自动模式之前进行设置

之所以出现ORA-04032提示，是因为当workarea_size_policy=auto即自动模式时，参数workarea_size_policy是不允许pga_aggregate_target为0，所以当pga_aggregate_target为0，workarea_size_policy=MANUAL 时想要将workarea_size_policy修改为 auto，则必须先将pga_aggregate_target修改为非0。

结合2.2.2和2.1.2，可以得出结论：

当workarea_size_policy=auto即自动模式时，参数workarea_size_policy是不允许（workarea_size_policy=auto的同时，）pga_aggregate_target为0。

2.2.3 pga_aggregate_target从0改到非0时, workarea_size_policy会从MANUAL变为auto.

（Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting theWORKAREA_SIZE_POLICY parameter toAUTO.）

【参数A改变&＃20540;，参数B是否会跟着变 的例子】

参数文件:

*.memory_max_target=700M

*.memory_target=0

*.pga_aggregate_target=0

*.workarea_size_policy='MANUAL'

*.sga_max_size=500M

*.sga_target=500M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORACLE 例程已经启动。

Total SystemGlobal Area 523108352 bytes

Fixed Size 1375704 bytes

Variable Size 159384104 bytes

Database Buffers 356515840 bytes

Redo Buffers 5832704 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameters policy

NAME TYPE VALUE

----------------------------------------------- ------------------------------

parallel_degree_policy string MANUAL

workarea_size_policy string MANUAL

SQL> show parameters pga

NAME TYPE VALUE

----------------------------------------------- ------------------------------

pga_aggregate_target big integer 0

SQL> show parameters mem

NAME TYPE VALUE

----------------------------------------------- ------------------------------

hi_shared_memory_address integer 0

memory_max_target big integer 700M

memory_target big integer 0

shared_memory_address integer 0

SQL> alter system set pga_aggregate_target=100m;

系统已更改。

SQL> showparameters policy

NAME TYPE VALUE

----------------------------------------------- ------------------------------

parallel_degree_policy string MANUAL

workarea_size_policy string AUTO

SQL>

注释：

由于memory_target=0时即AMM关闭时，实例运行期间，无论workarea_size_policy为manual还是auto，pga_aggregate_target 都不能允许设置为0，所以说oracle官方文档里说的关于“Setting PGA_AGGREGATE_TARGET to 0 automatically sets theWORKAREA_SIZE_POLICY parameter toMANUAL”这种情况就是错误的，不可实现的。

参见：http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams193.htm#CHDBJCDE

附加：

PGA_AGGREGATE_TARGET

Property	Description
Parameter type	Big integer
Syntax	PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value	10 MB or 20% of the size of the SGA, whichever is greater
Modifiable	ALTER SYSTEM
Range of values	Minimum: 10 MB Maximum: 4096 GB - 1
Basic	Yes

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.

Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.

If Automatic Memory Management is enabled (MEMORY_TARGET is set to a positive value) and PGA_AGGREGATE_TARGET is also set to a positive value, the PGA_AGGREGATE_TARGET value acts as the minimum value for the size of the instance PGA.

WORKAREA_SIZE_POLICY

Property	Description
Parameter type	String
Syntax	WORKAREA_SIZE_POLICY = { AUTO | MANUAL }
Default value	AUTO
Modifiable	ALTER SESSION, ALTER SYSTEM

WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.

Values:

• AUTO

  Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set in PGA_AGGREGATE_TARGET, and the requirement of each inpidual operator.

• MANUAL

  The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter corresponding to the operation (for example, a sort uses SORT_AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization.

===========================================================

实例运行时参数间关系的两种模式：

参数A不变时，参数B改变&＃20540;可否

参数A改变&＃20540;，参数B是否会跟着变。

pga_aggregate_target 为0

实例运行期间当pga_aggregate_target=0时将workarea_size_policy从manual修改为auto是修改不了的（在memory_target=0即AMM关闭的情况下）

pga_aggregate_target从0改到非0时, workarea_size_policy会从MANUAL变为auto.