Oracle 11gR2修改MEMORY_TARGET参数

最新推荐文章于 2022-02-16 10:08:05 发布

weixin_34221036

最新推荐文章于 2022-02-16 10:08:05 发布

阅读量1.8k

点赞数

文章标签：数据库内存管理

原文链接：https://my.oschina.net/siiiso/blog/880864

版权

为什么80%的码农都做不了架构师？>>>

Oracle 11g默认使用AMM(Automatic Memory Management，自动内存管理)功能。在安装数据库过程中，指定Oracle使用内存的百分比，这个取值就作为MEMORY_TARGET和MEMORY_MAX_TARGET的初始取值使用。

Oracle版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

查看MEMORY_TARGET和相关参数值

SQL> show parameter memory 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address	     integer	 0
memory_max_target		     big integer 2496M
memory_target			     big integer 2496M
shared_memory_address		     integer	 0

SQL> show parameter sga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			     boolean	 FALSE
sga_max_size			     big integer 2496M
sga_target			     big integer 0

SQL> show parameter pga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size			     big integer 209700K
pga_aggregate_target		     big integer 0

修改MEMORY_TARGET

说明：通过alter system语句来修改MEMORY_TARGET。需要注意的是，MEMORY_TARGET是个动态参数，而MEMORY_MAX_TARGET是个静态参数。

当修改值<=MEMORY_MAX_TARGET时，可以随意修改MEMORY_TARGET值；当修改值>MEMORY_MAX_TARGET时，必须首先修改MEMORY_MAX_TARGET参数值，重启数据库使静态参数生效后再去修改MEMORY_TARGET值，否则，数据库会报错。

这里只介绍修改值<=MEMORY_MAX_TARGET情况。

SQL> alter system set memory_max_target=26G scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2.7793E+10 bytes
Fixed Size		    2214096 bytes
Variable Size		 2.7380E+10 bytes
Database Buffers	  268435456 bytes
Redo Buffers		  141578240 bytes
Database mounted.
Database opened.

SQL> show parameter memory;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address	     integer	 0
memory_max_target		     big integer 26G
memory_target			     big integer 2560M
shared_memory_address		     integer	 0

SQL> alter system set memory_target=24G;

System altered.

SQL> show parameter memory;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address	     integer	 0
memory_max_target		     big integer 26G
memory_target			     big integer 24G
shared_memory_address		     integer	 0

SQL> show parameter sga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			     boolean	 FALSE
sga_max_size			     big integer 26G
sga_target			     big integer 0

转载于:https://my.oschina.net/siiiso/blog/880864