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之和的最大值(确切地说,各个oracle进程的pga之和围绕着pga_aggregate_target上下浮动,如何证明这点?说法来源于PGA自动管理原理深入分析及性能调整),
而pga自动管理机制启动与否不决定于pga_aggregate_target值;
在AMM启动时(即memory_target=非0),即使设置workarea_size_policy='AUTO‘,也没有启动pga自动管理机制(程序),此时pga_aggregate_target为各个oracle进程的pga之和的最小值而已。
也就是说memory_target=非0时即AMM启动时,无论workarea_size_policy为manual还是auto,pga自动管理机制皆启动不了。
一、
在memory_target=非0即AMM启动的情况下
详见:
AMM启动下,pga_aggregate_target和workarea_size_policy各自无论如何修改,都不会影响对方的值
结论:1、AMM启动下,
pga_aggregate_target和workarea_size_policy各自无论如何修改,都不会影响对方的值.
因为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为什么值,实例都可以启动成功。
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.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:无法修改参数, 因为指定的值无效
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:无法修改参数, 因为指定的值无效
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的值为0。
而由一大点里的结论2可知,memory_target=非0时即AMM启动时,pga_aggregate_target
可以设置为0。
简单地说,就是
memory_target=0即手动管理时,在实例运行期间,pga_aggregate_target不能改为0,而memory_target=非0即自动管理时,在实例运行期间,pga_aggregate_target可以改为0。
【参数A不变时,参数B改变值可否 的例子】
参数文件:
*.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: 无法修改参数, 因为指定的值无效
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改变值,参数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 individual 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 usesSORT_AREA_SIZE
). SpecifyingMANUAL
may result in sub-optimal performance and poor PGA memory utilization.
===========================================================
实例运行时参数间关系的两种模式:
参数A不变时,参数B改变值可否
参数A改变值,参数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.
上一篇: jquery编写日期选择器实例教程
下一篇: 为什么表中插入不了数据呢?