Oracle 11g 数据库内存原理

转自：
1、 https://blog.csdn.net/ly7472712/article/details/116239388
2、 https://www.cnblogs.com/prognani/archive/2012/05/14/2500679.html

Oracle11g 数据库内存管理
-----------------------------------------------------------------------------

一、Oracle数据库内存管理概念：
    内存管理涉及到根据数据库变化的需要为Oracle实例内存结构保持最佳大小,Oracle数据库根据与内存相关的初始化参数的设置来管理内存。
    内存管理有三种基本方式：

    AMM (自动内存管理)
    Automatic memory management:

    指定实例内存的目标大小,数据库实例自动调整到目标内存大小,根据需要在SGA和实例PGA之间重新分配内存
    ASMM (自动共享内存管理)
    Automatic shared memory management:

    这种管理模式是部分自动化的,为SGA设置目标大小,然后可以选择为PGA设置聚合目标大小或单独管理PGA工作区
    Manual memory management(手动内存管理)

    不是设置总内存大小,而是设置许多初始化参数来分别管理SGA和实例PGA的组件
    如果使用数据库配置助手（DBCA）创建数据库并选择基本安装选项，则默认为AMM（自动内存管理）

二、内存管理方式的切换：
    从上面内存分配原则，2G*80%=1.6G,实际数据库内存大小是800M，可以调整的大一些，在生产环境下要注意一定要先create pfile from spfile，备份一下参数文件。
    
    MEMORY_TARGET ：操作系统上 Oracle 所能使用的最大内存值（动态参数），是PAG 和SGA的总和。

1、AMM(自动内存管理)
    默认安装的实例即是 AMM 方式。
    
    memory_target=非0，如果初始化参数 LOCK_SGA ＝ true ，则 AMM 是不可用的
    如果将memory_max_target，memory_target这两个参数设置了非0值，那么就叫启动了内存自动管理AMM，那么oracle就会根据业务类型自动的调整SGA,PGA的大小。
    
    当将AMM开启了SGA,PGA大小就不需要再去管了，即使设置了sga_target和pga_aggregate_target为0,那也是说明sga_target和pga_aggregate_target为自动管理。最小值大于0而已，SGA,PGA大小都会根据业务类型来调整。

    SGA里包括了：share pool，buffer cache，large pool，Java pool，stream pool。这几个缓冲区大小都可以根据SGA自动进行分配，因为启动了AMM管理，最后调整结果如下（SGA,PGA,SGA下面的内存区都自动管理）：
    
    memory_max_target      big integer   1104M
    memory_target      big integer   1104M
    sga_target      big integer   0
    pga_aggregate_target      big integer   0

2、ASMM (自动共享内存管理)
ASMM 自动共享内存管理(Automatic Shared Memory Management)
    memory_target=0 and sga_target=非0

    这是 10g 引入的管理方式，要使用这种方式，需要设置初始化参数 MEMORY_TARGET=0 ，然后显式的指定 SGA_TARGET 的值。

    SQL> alter system set sga_target=1024m scope=both;
    alter system set sga_target=1024m scope=both
    *
    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-00839: SGA_TARGET cannot be modified to the specified value
    SQL> alter system set memory_target=0 scope=both;
    System altered.
    SQL> alter system set sga_target=1024m scope=both;
    System altered.
    SQL> 
    这两个参数的修改是有严格顺序的，如果不遵守倒也没问题--Oracle 会报告错误。
        
    

3、Manual memory management(手动内存管理)

    memory_target=0 and sga_target=0，指定 share_pool_size 、db_cache_size 等 sga 参数
 
    在设置memory_target与memory_max_target 需注意注意以下三点：

    若指定了memory_target而没有指定memory_max_target重启后，memory_max_target和memory_target大小相等；
    若指定了memory_max_target而没有指定memory_target，重启后memory_target=0；
    若设置 memory_max_target < memory_target 时，则重启后报错（ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET）。
    使AMM失效memory_max_target 的值应该设置为空，而不是0，使用alter system reset memory_max_target，
    （ORA-00843 ORA-00849 Trying to Change SGA_TARGET with MEMORY_MAX_TARGET=0 Being Active (Doc ID 1397761.1)）
    SQL> alter system reset memory_max_target;
    SQL> alter system set memory_target=0;

3.手工共享内存管理
    这个又更加原始了一些。因为原始，所以新的初始化参数 SGA_TARGET 与 MEMORY_TARGET 都要设置为 0. 然后手工设定 share_pool_size 、db_cache_size 等 sga 参数。要注意 RESULT_CACHE_SIZE 参数是 11g 新引入的，用来缓存 SQL 结果。

4.自动 PGA　内存管理
    如果使用 AMM ， 则对 PGA 不用操心。如果要做到精细控制而切换到自动 PGA　内存管理模式，需要设定WORKAREA_SIZE_POLICY ＝ AUTO(默认即为 AUTO)，然后需要指定 PGA_AGGREGATE_TARGET 的值。如需要精确控制PGA，则 WORKAREA_SIZE_POLICY ＝ MANUAL .(Thanks vongates)

5.手动 PGA 管理
    前提是 WORKAREA_SIZE_POLICY = manual ,然后分别指定 SORT_AREA_SIZE 等 PGA 相关的参数。估计现在没有人干这个吃力不讨好的事情了。这个模式大可以忽略。

    AMM 的限制
    如果初始化参数 LOCK_SGA ＝ true ，则 AMM 是不可用的。

    相关动态视图
    V$MEMORY_DYNAMIC_COMPONENTS
    V$MEMORY_RESIZE_OPS

 

===============================================================================

ORA-32018 parameter cannot be modified in memory on another instance

http://blog.itpub.net/9240380/viewspace-1804586/

自动内存管理是用两个初始化参数进行配置的：
MEMORY_TARGET：动态控制SGA和PGA时，Oracle总共可以使用的共享内存大小，这个参数是动态的，因此提供给Oracle的内存总量是可以动态增大，也可以动态减小的。它不能超过MEMORY_MAX_TARGET参数设置的大小。默认值是0。
MEMORY_MAX_TARGET：
这个参数定义了MEMORY_TARGET最大可以达到而不用重启实例的值，如果没有设置MEMORY_MAX_TARGET值，默认等于MEMORY_TARGET的值。使用动态内存管理时，SGA_TARGET和PGA_AGGREGATE_TARGET代表它们各自内存区域的最小设置，要让Oracle完全控制内存管理，这两个参数应该设置为0。
     memory_max_target 是一个非动态参数，不能在memory范围动态改变，只能通过指明 scope=spfile这个条件来达到数据库在下次启动后让改变生效的目的。但是memory_target这个参数是可以动态调节的...也就是说不需要重新启动DB，就可以让其生效。
     
     MEMORY_MAX_TARGET 是设定Oracle能占OS多大的内存空间SGA_MAX_SIZE是Oracle SGA 区最大能占多大内存空间.
     10g 的sga_max_size 是动态分配 Shared Pool Size,database buffer cache,large pool,java pool，redo log buffer 大小的，是根据Oracle 运行状况来重新分配SGA 各内存块的大小。PGA在10g中需要单独设定。
     11g MEMORY_MAX_TARGET 参数包含SGA和PGA两部分。
    在手动创建数据库时，只需要在创建数据库之前设置合适的MEMORY_TARGET和MEMORY_MAX_TARGET初始化参数。

===============================================================================
对于PGA_AGGREGATE_TARGET参数的设置，Oracle提供这样一个建议方案
1．对于OLTP系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
2．对于DSS系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%

也就是说，对于一个单纯的数据库服务器，通常我们需要保留20%的物理内存给操作系统使用，剩余80%可以分配给Oracle使用。Oracle使用的内存分为两部分SGA和PGA，那么PGA可以占用Oracle消耗总内存的20%（OLTP系统）至50%（DSS系统）。
这只是一个建议设置，更进一步的我们应该根据数据库的具体性能指标来调整和优化PGA的使用。

伴随自动PGA调整新特性的引入,Oracle随之引入了一系列新的视图,V$PGASTAT就是其中的一个。
在V$PGASTAT中有这样一个条目: global memory bound ,该条目记录数据库允许的最高PGA内存使用量,我们可以从不同的PGA参数设置来观察一下Oracle运行的PGA上限.



对于当前操作的RAC数据库实例，一定要添加 sid='jingfa1',
否则会报ORA-32018: parameter cannot be modified in memory on another instance

memory_max_target(它为静态参数，修改完成后需要重启实例)
memory_target    (它为动态参数，不需要重启实例)
 

11G 还有一个新增的参数：
memory_max_target:意思就是oracle 能占系统多大的内存空间，它包含两部分，SGA和PGA，
11g里通过这个参数已经可以把SGA和PGA一起动态的管理了。
   如果把Memory_target设置为一个值的话，有四种情况：
   
  1.sga和pga已经设置了大小，则这两个参数将各自被分配为最小值为他们的目标值即
   Memory_Target=SGA_TARGET + PGA_AGGREGATE_TARGET
   这说明了什么问题呢，说明sga和pga的大小不一定会按设置的来用。要以memory_target为准。
  2.sga设置了大小，pga没有设置大小，那么
   pga=memory_target-sga
  3.sga没有设置大小,pga 设置了大小,那么
   sga=memory_target-pga   
   (注意：这里为了简化，sga和pga分别代表sga_target 和pga_aggregate_target)

1、memory_max_target 就是oracle实例的最大的内存大小
2、memory_target  就是oracle实例实际内存的大小，最大值不能超过 memory_max_target
3、sga_max_size 系统全局区 最大值
4、sga_target 系统全局区 实际值 不能超过 sga_max_size
5、pag_aggregate_target 进程全局区）

注意：
    如果你配置sga_target及pga_aggregate_target为0，
    则完全由ORACLE动态根据数据库负载无限制调整sga及pga的大小
    (这种方式到时内存分配抖动过大，会引发严重的性能问题)；
     但是，如果你配置sga_target及pga_aggregate_target为非0值,则为内存动态调整时，其为最小值，即不能少于这个值（建议在生产环境采用这种方式）

1.0

PGA_AGGREGATE_TARGET-此参数用来指定所有session总计可以使用最大PGA内存。

SQL> show parameter workarea

NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
workarea_size_policy                 string          AUTO

2.0

WORKAREA_SIZE_POLICY-此参数用于开关PGA内存自动管理功能，该参数有两个选项：AUTO 和 MANUAL，当设置为AUTO时，数据库使用Oracle9i提供的自动PGA管理功能，当设置为MANUAL时，则仍然使用Oracle9i前手工管理的方式。


结论
1,memory_max_target及memory_target参数的配置，则会启用全自动内存管理功能
2,memory_target=sga_target+max(pga_aggregate_target+maximum pga allocated)
3,memory_target分配范围必须要在参数memory_max_target
4,memory_max_target参数分配原则，一要考虑数据库负载的持续变化发展以及物理内存的空间是否充足
5,sga_target及pga_aggregate_target若配置为0，则全自动内存管理，
SGA及PGA组件分配完全由ORACLE根据业务负载分配，没有最小值的限制
  否则，若2参数为非0，则SGA及PGA分配不能少于指定的值
6,建议在生产环境采用第5步后者的分配方式，以防内存分配抖动大量出现，引发严重的性能问题
7,数据库启动可以采用SPFILE或者PFILE，二者调整参数稍有不同，一定要注意二者的区别
8,对于RAC数据库，若采用ALTER SYSTEM方式调整开启全自动内存管理方式，且是一个实例一个实例调整，一定要添加选项sid='sid值'，否则会报错
   调整完当前节点，再调整其它节点  
9,本文测试环境为RAC 2节点11.2.0.3数据库，基于ASM存储的SPFILE,并在另一篇文章测试基于PFILE调整全自动内存管理的RAC数据库   





----------------------------------------------------------------------------------
如果是RAC数据库，还要启动其它的节点

alter database mount;
alter database open;

set linesize 400
set pagesize 350
col TABLESPACE_NAME for a20;        
col FILE_NAME for a50;
col size for a15;
col NAME for a15;
col TYPE for a15;
col VALUE for a35;


>>>>>>>>单实例修改SGA：>>>>>>>>

修改sga_target

SQL>alter system set sga_target=436M;

修改sga_max_size
SQL> alter system set sga_max_size=436M scope=spfile;

4、重启数据库使其生效：


>>>>>>>>RAC修改SGA：>>>>>>>>

根据 pfile 启动 数据库：

[root@rac2 dbs]# pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[root@rac2 dbs]# ll
total 18316
-rw-rw---- 1 oracle oinstall     1544 Sep  5 14:57 hc_baydb2.dat
-rw-r--r-- 1 oracle oinstall     1463 Jun 20 11:16 initbaydb2.ora
-rw-r--r-- 1 oracle oinstall     1482 Sep  5 11:45 initbaydb2.ora.new
-rw-r--r-- 1 oracle oinstall     1478 Sep  5 11:34 initbaydb2.ora.old
-rw-r--r-- 1 oracle oinstall     2851 Apr 17 14:43 init.ora
-rw-r----- 1 oracle oinstall     1536 Apr 17 16:02 orapwbaydb2
-rw-r----- 1 oracle oinstall 18726912 Jun 20 11:31 snapcf_baydb2.f


startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initbaydb1.ora';

startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initbaydb2.ora';

tail -200f /u01/app/oracle/diag/rdbms/baydb/baydb2/trace/alert_baydb2.log 

----------------------------------------------------------------------
【本地测试过程】：

1.0————创建spfile
create spfile  from pfile;

2.0————默认用spfile 启动数据库
shutdown immediate
startup

3.0————修改sga_max_size 、sga_target  
注意，重启前一定先完成上述两部操作，且sga_target不得大于sga_max_size，一般保持两者相等
否则可能导致数据库无法启动。~!!!!!!!!!!!

sqlplus / as sysdba

show parameter pfile;
show parameter sga;
show parameter pga;
show parameter mem;

SELECT * FROM V$SGAINFO; 
select * from v$sga; 

# 查看 pga 
show parameters area_size 
SELECT * FROM v$pgastat; 


ALTER SYSTEM SET sga_max_size = 4096M COMMENT='internally adjusted' SCOPE=SPFILE SID='baydb1';
System altered.

ALTER SYSTEM SET sga_target = 4096M SCOPE=SPFILE SID='baydb1';
System altered.


4.0————重启数据库后发现报错：
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 4294967296 cannot be set to more than MEMORY_TARGET 788529152.

5.0————再次重做1.0 -- 3.0 哈哈~~ 被坑了吧

shutdown immediate
startup


memory_max_target                    big integer     4G
memory_target                        big integer     4G

create spfile  from pfile;


alter system set memory_max_target=4096M  scope=spfile;

alter system set pga_aggregate_target=1536m scope=both  SID='baydb1'; 



show parameter cursor;
alter system set open_cursors = 2000 scope = spfile; 

show parameter session;
select count(1) from V$SESSION;
show parameter process;
select count(1) from V$PROCESS;

alter system set processes=1500 scope=spfile SID='baydb1';
alter system set sessions=2000  scope=spfile SID='baydb1';

shutdown immediate
startup


、