您好，欢迎来到尚佳旅游分享网。
科技 教育 生活 旅游 时尚 美容 美食 健康 体育 游戏 汽车 家电
搜索
您的当前位置：首页oracle三对内存参数间关系之4sga

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 7801404 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 7801404 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 < sga_max_size：

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< sga_target

参数:

*.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_size< sga_target和memory_max_target

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< sga_target

参数:

*.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_size< sga_target和memory_max_target

附加：

当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 < sga_max_size且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 < sga_max_size时，还是提示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 < sga_max_size且memory_target >sga_target时

pga_aggregate_target与sga_target之和< memory_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 < sga_max_size时，优先报错后者的相关错误消息，即ORA-00851:SGA_MAX_SIZE 1048576000 cannot be set to more than MEMORY_TARGET 6291

45600.

memory_ target < sga_max_size且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< sga_target

参数:

*.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< sga_target才会抱错sga_max_size< sga_target的相关提示ora-00823。

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

同时memory_target

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_target

sga_max_size


Copyright © 2019- shangjiatang.cn 版权所有 湘ICP备2022005869号-4

违法及侵权请联系：TEL:199 1889 7713 E-MAIL:2724546146@qq.com

本站由北京市万商天勤律师事务所王兴未律师提供法律服务