oracle三对内存参数间关系之4sga

１、SGA_TARGET始终要= SGA_MAX_SIZE 否则 ORA-00823: Specified value ofsga_target greater than sga_max_size AMM启动时（即memory_target=非0） 1、sga_target《memory_target《memory_max_target 如果 sga_targetmemory_target，则会提示： ORA-00838:

１、SGA_TARGET始终要SGA_MAX_SIZE 

否则

ORA-00823: Specified value ofsga_target greater than sga_max_size

AMM启动时（即memory_target=非0）

1、sga_target《memory_target《memory_max_target

如果sga_target>memory_target，则会提示：

ORA-00838:指定的 MEMORY_TARGET 的值太小, 至少应为 XXXX M（该值等于sga_target与pga_aggregate_target之和，

因为MEMORY_TARGET》=sga_target与pga_aggregate_target之和）

 

 

2、sga_max_size《memory_target ，否则

ORA-00851:SGA_MAX_SIZE 780140544 cannot be set to more than MEMORY_TARGET 41943

0400.

AMM关闭时（即memory_target=0）

1、sga_target

ORA-00849:SGA_TARGET 524288000 cannot be set to more than MEMORY_MAX_TARGET 0.

2、sga_max_size可以大于memory_max_target吗？

在参数文件里，即使当sga_target《memory_max_target时，如果sga_max_size>memory_max_target，则启动实例时也不会成功，也会出现提示与sga_target>memory_max_target时相同的提示：ORA-00849:SGA_TARGET 524288000 cannot be set to more than MEMORY_MAX_TARGET 0.

只当参数文件里sga_max_size和memory_max_target都为0时，启动实例后，显示的sga_max_size可以大于memory_max_target。

总之，

AMM启动时（即memory_target=非0），sga_max_size《memory_target：

sga_target《sga_max_size《memory_target

AMM关闭时（即memory_target=0），由于memory_target=0相当于说memory_target这个参数不起作用此时，所以sga_max_size《memory_max_target：
sga_target《sga_max_size《memory_max_target

原本以为

AMM启动时（即memory_target=非0），sga_target只要《memory_target，以及SGA_MAX_SIZE就行，sga_max_size和memory_target间可以无相关关系；AMM关闭时（即memory_target=0），sga_target只要《memory_max_target以及SGA_MAX_SIZE就行，sga_max_size和memory_max_target间可以无相关关系。

事实是，在sga_max_size和memory_max_target（或是memory_target）间是有相关关系的。

估计因为oracle公司设计程序时考虑到如下效率问题吧：

如果在sga_max_size和memory_max_target（或是memory_target）间无相关关系时，

每次修改一次sga_target的值，程序都要进行两次判断sga_target修改后的值是否符合条件：

一是sga_target是否《memory_max_target（或是memory_target），二是sga_target是否SGA_MAX_SIZE

而如果在sga_max_size和memory_max_target（或是memory_target）间有相关关系，即sga_max_size memory_max_target（或是memory_target）时，

每次修改一次sga_target的值，程序只要进行一次判断sga_target修改后的值是否符合条件即可：

sga_target是否SGA_MAX_SIZE.

注释小结：

1、AMM启动时（即memory_target=非0），才会出现

ORA-00851:SGA_MAX_SIZE 780140544 cannot be set to more than MEMORY_TARGET 41943

0400.AMM关闭时（即memory_target=0）不会出现ORA-00851。

AMM启动时（即memory_target=非0）且sga_max_size>memory_target时，才会出现

ORA-00851。

AMM关闭时（即memory_target=0），才会出现ORA-00849:SGA_TARGET 524288000 cannot be set to more than MEMORY_MAX_TARGET 0.无论是sga_target>memory_max_target还是sga_max_size>memory_max_target。当sga_max_size>memory_max_target时，即使此时sga_target也>memory_max_target，ORA-00849里的SGA_TARGET值还是来自sga_max_size。

当memory_target=0时，由于memory_target=0而使memory_target参数不起作用，所以以memory_max_target为标准线，sga_max_size和sga_target围绕memory_max_target，即sga_max_size和sga_target与memory_max_target间有关系存在。

2、

启动实例时

SGA_TARGET要小于等于MEMORY_MAX_TARGET  ORA-00849（MEMORY_TARGET=0AMM关闭下，当SGA_MAX_SIZE大于等于MEMORY_MAX_TARGET时就会发生ORA-00849，即使SGA_TARGET小于等于MEMORY_MAX_TARGET也会发生ORA-00849）

 SGA_MAX_SIZE 要小于等于 MEMORY_TARGET   ORA-00851

（当SGA_MAX_SIZE =非0，MEMORY_TARGET=0时例外，因为MEMORY_TARGET=0AMM关闭即MEMORY_TARGET参数不起作用，所以此时SGA_MAX_SIZE 与MEMORY_TARGET无关系）

实例运行时

SGA_MAX_SIZE 可以大于（修改后的）MEMORY_TARGET

 

无论启动实例时，还是实例运行时

MEMORY_TARGET要小于等于MEMORY_MAX_TARGET

（启动实例前，MEMORY_TARGET=非0，MEMORY_MAX_TARGET=0例外）

SGA_TARGET要小于等于SGA_MAX_SIZE

MEMORY_TARGET》=sga_target与pga_aggregate_target之和

 

 

因为无论启动实例时，还是实例运行时

MEMORY_TARGET》=sga_target与pga_aggregate_target之和

所以SGA_TARGET始终要小于等于MEMORY_TARGET

注释：

如果两个参数都是静态或是动态参数，那讨论她两关系时就不用区分实例启动前修改它们值和实例启动后（即实例运行时）修改它们值两个阶段。

如果两个参数，一个是静态，一个是动态参数，那讨论她两关系时就要区分实例启动前修改它们值和实例启动后（即实例运行时）修改它们值两个阶段。同一个层级上的两个参数只讨论实例启动前修改它们值这个阶段。例如，MEMORY_TARGET和MEMORY_MAX_TARGET都是AMM程序里的参数：

MEMORY_TARGET要小于等于MEMORY_MAX_TARGET，

当MEMORY_TARGET=非0，MEMORY_MAX_TARGET=0时，启动实例后MEMORY_MAX_TARGET=MEMORY_TARGET值。

上下层级的两个参数区分实例启动前修改它们值和实例启动后（即实例运行时）修改它们值两个阶段来讨论。例如，SGA_MAX_SIZE和 MEMORY_TARGET

是AMM程序里上下层级上的参数：

 

启动实例时

 

 SGA_MAX_SIZE 要小于等于 MEMORY_TARGET  ORA-00851

实例运行时

SGA_MAX_SIZE 可以大于（修改后的）MEMORY_TARGET

因为

memory_max_target是静态参数，其实例运行期间修改的值在实例运行期间不起作用，该参数只在实例启动时起作用。

附加注释：

实例以workarea_size_policy=manual启动实例时可以在参数文件里pga_aggregate_target  可以设置为0，实例启动后显示的pga_aggregate_target 的值为0。

 

从这个结论，说明有些动态参数也要分实例启动前后讨论。

下面是实验：

memory_target=0

提示ORA-00849: SGA_TARGET 1048576000(是1000M,不是500M) cannot be setto more than MEMORY_MAX_TARGET 943718400.

memory_max_target

 

 

memory_max_target >sga_target时

 

参数:

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

 

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

ORA-00843: Parameter not takingMEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1048576000(是1000M,不是500M) cannot be setto more than MEMORY_MAX_TARGET 943718400.

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

SQL>

 

memory_max_target

sga_max_size> sga_target

 

参数:

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=950M

 

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

ORA-00843: Parameter not takingMEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1048576000(是1000M,不是950M) cannot be setto more than MEMORY_MAX_TARGET 94

3718400.

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

SQL>

sga_max_size

参数:

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=1200M

 

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

ORA-00843: Parameter not takingMEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1048576000(是1000M,不是1200M) cannotbe set to more than MEMORY_MAX_TARGET 943718400.

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

SQL>

说明

同时出现sga_max_sizeORA-00849: SGA_TARGET1048576000(是1000M,不是1200M) cannot be set to more thanMEMORY_MAX_TARGET 943718400.

 

 

memory_max_target > sga_max_size：

 

 

memory_max_target

 

参数:

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=800M

*.sga_target=1000M

 

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

ORA-00823:Specified value of sga_target greater than sga_max_size

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

SQL>

 

总之，memory_参数和sga_参数间的关系出错（即memory_max_target memory_target

；memory_ target memory_target）都是优先与sga_max_size

 

memory_max_target >sga_target时

sga_max_size> sga_target

 

参数:

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=800M

*.sga_target=700M

 

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

启动实例成功

sga_max_size

参数:

*.memory_max_target=900M

*.memory_target=0

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=700M

*.sga_target=800M

 

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

ORA-00823: Specified value of sga_targetgreater than sga_max_size

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

SQL>

说明

同时出现sga_max_sizeORA-00849: SGA_TARGET1048576000(是1000M,不是1200M) cannot be set to more thanMEMORY_MAX_TARGET 943718400.

 

 

 

 

附加：

当memory_target=0时，由于memory_target=0而使memory_target参数不起作用，所以以memory_max_target为标准线，sga_max_size和sga_target围绕memory_max_target，即sga_max_size和sga_target与memory_max_target间有关系存在。

 

memory_target=非0

提示ORA-00851: SGA_MAX_SIZE1048576000 cannot be set to more than MEMORY_TARGET 629145600.

 

以memory_max_target为标准线，sga_max_size和sga_target围绕此

 

memory_max_target 且memory_target>sga_target时

 

 

 

参数:

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

 

 

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

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

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1048576000 cannot be set to more thanMEMORY_TARGET 6291

45600.

注释：

memory_max_target 时，还是提示memory_ target和 sga_max_size间的关系，说明memory_target=非0时是memory_target和 sga_max_size间有关系。

 

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

SQL>

 

 

 

以memory_ target为标准线，sga_max_size和sga_target围绕此。

 

memory_target 且memory_target >sga_target时

 

pga_aggregate_target与sga_target之和

 

参数:

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

 

 

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

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

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1048576000 cannot be set to more thanMEMORY_TARGET 6291

45600.

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

SQL>

pga_aggregate_target与sga_target之和>memory_target

参数:

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=200M

*.workarea_size_policy='AUTO'

*.sga_max_size=1000M

*.sga_target=500M

 

 

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

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

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 1048576000 cannot be set to more thanMEMORY_TARGET 6291

45600.

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

SQL>

 

参数:

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=200M

*.workarea_size_policy='AUTO'

*.sga_max_size=600M

*.sga_target=500M

 

 

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

ORA-00838: Specifiedvalue of MEMORY_TARGET is too small, needs to be at least 7

00M

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

SQL>

说明

 

同时出现pga_aggregate_target与sga_target之和>memory_target和memory_ target 时，优先报错后者的相关错误消息，即ORA-00851:SGA_MAX_SIZE 1048576000 cannot be set to more than MEMORY_TARGET 6291

45600.

 

 

memory_ target 且memory_target

sga_max_size> sga_target

参数:

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=800M

*.sga_target=700M

 

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

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 838860800 cannot beset to more than MEMORY_TARGET 62914

5600.

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

SQL>

sga_max_size

 

参数:

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=700M

*.sga_target=800M

 

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

ORA-00844: Parameter not takingMEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 734003200 cannot beset to more than MEMORY_TARGET 62914

5600.

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

SQL>

只有当memory_ target 〉 sga_max_size且memory_target 〉sga_target时，sga_max_size

 

memory_ target 〉 sga_max_size且memory_target

 

参数:

*.memory_max_target=900M

*.memory_target=600M

*.pga_aggregate_target=100M

*.workarea_size_policy='AUTO'

*.sga_max_size=500M

*.sga_target=700M

 

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

ORA-00838: Specified value of MEMORY_TARGETis too small, needs to be at least 800M

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

SQL>

memory_ target 〉 sga_max_size且

memory_target sga_target时,推知sga_target〉 sga_max_size，也就是说

 

同时memory_target sga_target，sga_target〉 sga_max_size时，先抱错前者的ORA-00838，非后者。

 

 

MEMORY_TARGET

sga_max_size时

 

 

参数:

*.memory_max_target=900M

*.memory_target=700M

*.pga_aggregate_target=0

*.workarea_size_policy='MANUAL'

*.sga_max_size=600M

*.sga_target=800M

 

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

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to beat least 8

12M(粒度)

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

SQL>

当

同时出现MEMORY_TARGET

sga_max_size