sga_max_size 和sga_target (转)

最新推荐文章于 2019-04-03 22:39:05 发布

转载最新推荐文章于 2019-04-03 22:39:05 发布 · 214 阅读

文章标签：

本文介绍了Oracle数据库中SGA参数的使用方法，特别是sga_max_size和sga_target这两个关键参数。sga_max_size定义了SGA的最大限制，而sga_target则用于实现SGA的自动管理。文章通过具体示例展示了这两个参数如何影响数据库的启动和运行。

sga_max_size为实例允许使用的sga上限,一个静态参数,是不能动态修改的.
sga_target为10g推出的sga自动管理的参数,动态参数,可以动态修改.

当oracle初始化启动时,如果sga_max_size的值小于显式的指定的各sga组件值之和,则oracle将会忽略sga_max_size
以下为测试过程
SQL> show parameter db_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size big integer 184M
db_cache_size big integer 100M
SQL> alter system set sga_max_size=96m scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 293601280 bytes
Fixed Size 778816 bytes
Variable Size 95428032 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes
SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M

当oracle初始化启动时,如果sga_max_size小于sga_target时,启动之后,sga_max_size将等于sga_target
以下为测试过程

SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 280M

SQL> alter system set sga_max_size=120m scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 293601280 bytes
Fixed Size 778816 bytes
Variable Size 95428032 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 280M
sga_target big integer 280M
SQL>

启动之后,sga_target就不能大于sga_max_size.以下为测试过程
SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 280M
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 280M
SQL> alter system set sga_target=320m scope=both;
alter system set sga_target=320m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-923231/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9650775/viewspace-923231/