ITPub博客

首页 > 数据库 > Oracle > How to Enable Automatic Memory Management in 12c

How to Enable Automatic Memory Management in 12c

翻译 Oracle 作者:wanghanjun 时间:2015-12-31 10:39:10 0 删除 编辑

http://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN11200

If you did not enable automatic memory management upon database creation (either by selecting the proper options in DBCA or by setting the appropriate initialization parameters for the CREATE DATABASE SQL statement), then you can enable it at a later time. Enabling automatic memory management involves a shutdown and restart of the database.
如果你之前在创建数据库的时候并没有开启自动内存管理(即没有选择那个选项又没有在CREATE DATABASE的脚本中配置过),那么你可以在创建数据库之后再开启.只不过需要通过重启数据库来生效.

To enable automatic memory management
如果启动自动内存管理

  1. Start SQL*Plus and connect to the Oracle Database instance with the SYSDBA administrative privilege.

    See "Connecting to the Database with SQL*Plus" and "Database Administrator Authentication" for instructions.
    打开sqlplus连接数据库,以sysdba的身份登陆                          

  2. Calculate the minimum value for MEMORY_TARGET as follows:
    通过如下的步骤来计算Memory_Target

    1. Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET in megabytes by entering the following SQL*Plus commands:
      使用下面的命令来获得当前SGA_TARGET和PGA_aggregate_target的值                                                 

      SHOW PARAMETER SGA_TARGET
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- --------------------------
      sga_target                           big integer 272M
      
      SHOW PARAMETER PGA_AGGREGATE_TARGET
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- --------------------------
      pga_aggregate_target                 big integer 90M
      

      See "Enabling Automatic Shared Memory Management" for information about setting the SGA_TARGET parameter if it is not set.

    2. Run the following query to determine the maximum instance PGA allocated in megabytes since the database was started:
      使用下面的命令从PGA的统计视图中获得从数据库启动以后到现在PGA所分配的最大值,单位是MB      

      SELECT VALUE/1048576 FROM V$PGASTAT WHERE NAME='maximum PGA allocated';
      
    3. Compute the maximum value between the query result from step 2b and PGA_AGGREGATE_TARGET. Add SGA_TARGET to this value.
      使用以下公式计算 MEMORY_TARGET                   

      MEMORY_TARGET = SGA_TARGET + MAX(PGA_AGGREGATE_TARGET, MAXIMUM PGA ALLOCATED)
  3. Choose the value for MEMORY_TARGET that you want to use.
使用这个你计算以后的值

This can be the minimum value that you computed in step 2, or you can choose to use a larger value if you have enough physical memory available
在Step 2的值是最小值,或者你也可以选择比较大的值如果物理内存够的

For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as the MEMORY_TARGET value that you chose in the previous step.
对于MEMORY_MAX_TARGET这个初始化参数,确定一个最大的值你想分配给数据对于可以预见的将来.就是SGA和PGA的值得最大值.  

Do one of the following:

If you started your Oracle Database instance with a server parameter file, which is the default if you created the database with the Database Configuration Assistant (DBCA),
使用其中的一个方法,如果你启动数据库实例通过参数文件的话,这个参数文件时你在使用DBCA创建数据库的时候创建的.
enter the following command:

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
where n is the value that you computed in Step 4.
The SCOPE = SPFILE clause sets the value only in the server parameter file, and not for the running instance.
You must include this
SCOPE clause because MEMORY_MAX_TARGET is not a dynamic initialization parameter.
(这上面几句就不用翻译了,但是有一点需要注意的是MEMORY_MAX_TARGET不是动态参数,需要重启数据库才可以生效 )

If you started your instance with a text initialization parameter file, manually edit the file so that it contains the following statements:

memory_max_target = nM
memory_target = mM
where n is the value that you determined in Step 4, and m is the value that you determined in step 3.
Note:
In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, then the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
在一个文本文件的初始化参数文件里,如果你没有MEMORY_MAX_TARGET但是你指定了MEMORY_TARGET,那么数据库会自动将MEMORY_TARGET的值设置成为MEMORY_MAX_TARGET.如果你忽略了MEMORY_TARGET但是却指定了 MEMORY_MAX_TARGET,那么MEMORY_TARGET也会设置为0,那么在启动以后,MEMORY_TARGET就可以动态配置了,但是要注意不要超过MEMORY_MAX_TARGET的值.                                            

Shut down and restart the database.

See Chapter 3, "Starting Up and Shutting Down" for instructions.

If you started your Oracle Database instance with a server parameter file, enter the following commands:

ALTER SYSTEM SET MEMORY_TARGET = nM;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
ALTER SYSTEM SET SGA_TARGET = 0;
where n is the value that you determined in step 3.
Note:
With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA. By setting both of these to zero as shown, there are no minimums, and the SGA and instance PGA can grow as needed as long as their sum is less than or equal to the MEMORY_TARGET setting. The sizing of SQL work areas remains automatic.
You can omit the statements that set the SGA_TARGET and PGA_AGGREGATE_TARGET parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.
当设置了MEMORY_TARGET的值以后,SGA_TARGET变成SGA的最小值,PGA_AGGGREGATE_TARGET也是一样.但是如果都设置为0,就是没有最小值,则SGA和PGA会动态增长直到MEORY_TARGET的值.SQL的工作区域也会自动调节.你可以忽略SGA_TARGET和PGA_AGGREGATE_TAGET的值或者指定他们的值.在这个案例里面,我们都设置为0                                                                                                                                

In addition, you can use the PGA_AGGREGATE_LIMIT initialization parameter to set an instance-wide hard limit for PGA memory. You can set PGA_AGGREGATE_LIMIT whether or not you use automatic memory management. See "Using Automatic PGA Memory Management".
此外,你可以设置PGA_AGGREGATE_LIMIT这个初始化参数来强制设置PGA的大小,PGA_AGGREGATE_LIMIT的优先级要高于 MEMORY_TARGET                                                                                


                                                                               

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

请登录后发表评论 登录
全部评论

注册时间:2014-05-28

  • 博文量
    42
  • 访问量
    32080