ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT

有幸遇到这个错误了, 12c的PGA_AGGREGATE_LIMIT 限制PGA功能还是有用的:

Exception in thread “main” java.sql.SQLException: ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

 

oracle@localhost:/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace$ oerr ora 4036
04036, 00000, “PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT”
// *Cause: Private memory across the instance exceeded the limit specified
// in the PGA_AGGREGATE_LIMIT initialization parameter. The largest
// sessions using Program Global Area (PGA) memory were interrupted
// to get under the limit.
// *Action: Increase the PGA_AGGREGATE_LIMIT initialization parameter or reduce
// memory usage.

 

 
pga_aggregate_limit
limit of aggregate PGA memory consumed by the instance
 

Script:收集自动SGA内存管理ASMM诊断信息

以下脚本可以用于收集自动SGA(sga_target>0)内存管理ASMM下的实例诊断信息:

 

 
spool auto_sga_diag.log 

set line 190 pagesize 1400

 SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY"
    FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"
            FROM v$sga_dynamic_components,
                 (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"
                    FROM v$process) a
           WHERE component IN ('shared pool',
                               'large pool',
                               'java pool',
                               'streams pool',
                               'DEFAULT buffer cache')) a,
         (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;

select component,
       current_size / 1024 / 1024 "CURRENT_SIZE",
       min_size / 1024 / 1024 "MIN_SIZE",
       user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
       last_oper_type "TYPE"
  from v$sga_dynamic_components;

select component, granule_size / 1024 / 1024 "GRANULE_SIZE(Mb)"
  from v$sga_dynamic_components;

col component for a25
col status format a10 head "Status"
col initial_size for 999,999,999,999 head "Initial"
col parameter for a25 heading "Parameter"
col final_size for 999,999,999,999 head "Final"
col changed head "Changed At"
col low format 999,999,999,999 head "Lowest"
col high format 999,999,999,999 head "Highest"
col lowMB format 999,999 head "MBytes"
col highMB format 999,999 head "MBytes"

SELECT component,
       parameter,
       initial_size,
       final_size,
       status,
       to_char(end_time, 'mm/dd/yyyy hh24:mi:ss') changed
  FROM v$sga_resize_ops
 ORDER BY component;

SELECT component,
       min(final_size) low,
       (min(final_size / 1024 / 1024)) lowMB,
       max(final_size) high,
       (max(final_size / 1024 / 1024)) highMB
  FROM v$sga_resize_ops
 GROUP BY component
 ORDER BY component;

SELECT name, bytes FROM v$sgainfo;

select * from v$sgastat order by bytes asc;

select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"
  from v$sgastat
 where pool is null
union
select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"
  from v$sgastat
 where pool is not null
 group by pool;

 select * from V$SGA_CURRENT_RESIZE_OPS;

 select * from v$sga_target_advice;

show parameter size
show parameter statistics        

spool off;

RAC中增大db_cache_size引发的ORA-04031错误

几个礼拜前, 有一套10.2.0.2 的 二节点RAC 数据库因为增大db_cache_size , 引发其中一个实例发生著名的ORA-04031 错误,日志如下:

 

Errors in file /oracle/oracle/admin/maclean/udump/u1_ora_13757.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 1048 bytes of shared memory
("shared pool","select name,online$,contents...","Typecheck","kgghteInit")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 1048 bytes of shared memory
("shared pool","select name,online$,contents...","Typecheck","seg:kggfaAllocSeg")
Thu Oct 13 08:25:05 2011
Log from www.askmaclean.com  & www.askmaclean.com
Errors in file /oracle/oracle/admin/maclean/udump/u1_ora_1444.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory
("shared pool","select name,online$,contents...","Typecheck","kgghtInit")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory
("shared pool","select name,online$,contents...","Typecheck","kgghtInit")

 

以上错误出现的同时实例出现大量的row cache lock字典缓存和cursor:pin S wait on X等待事件,说明共享池中的row cache字典缓存和SQL area 执行计划因为Free Memory不足而被不断换出,导致硬解析增多并SQL解析性能下降,进一步造成了应用程序挂起,赶到现场后对该ORA-04031错误进行了分析。

SGA中的内存池包含不同大小的内存块。当数据库启动时,会有一块大的内存被分配并使用Free list的空闲列表追踪。随着时间推移,这些内存被不断分配和释放,内存块(chunk)被按照其大小在不同的Fress list中移动,当SGA里任何一个内存池出现不能满足内部分配一整块连续的内存块请求时,就可能出现ORA-04031错误。实际使用中造成ORA-04031错误的原因可能是Oracle软件bug、产品缺陷、应用程序设计不当、Oracle内存参数设置不当。

 

这里出现ORA-04031错误的内存池是shared pool即共享池,为了搞清楚ORA-04031错误发生的实际原因,我们通过AWR报告分析共享池的使用情况。

 

以下是 ORA-04031 问题发生前一天AWR报告中的共享池内存使用情况:

 

Pool Name Begin MB End MB % Diff
large free memory 112.00 112.00 0.00
shared ASH buffers 25.60 25.60 0.00
shared CCursor 19.44 20.16 3.70
shared Checkpoint queue 5.87 5.87 0.00
shared PCursor 10.57 11.14 5.38
shared event statistics per sess 7.72 7.72 0.00
shared free memory 32.99 33.00 0.02
shared gcs resources 78.75 78.75 0.00
shared gcs shadows 49.61 49.61 0.00
shared ges big msg buffers 15.03 15.03 0.00
shared ges reserved msg buffers 7.86 7.86 0.00
shared ges resource 5.28 5.28 0.00
shared kglsim heap 16.63 16.63 0.00
shared kglsim object batch 25.63 25.63 0.00
shared library cache 21.32 22.01 3.23
shared row cache 7.13 7.13 0.00
shared sql area 64.06 61.55 -3.91
streams free memory 64.00 64.00 0.00
buffer_cache 3,936.00 3,936.00 0.00
fixed_sga 2.08 2.08 0.00
log_buffer 3.09 3.09 0.00

 

以下是 ORA-04031 问题发生时AWR报告中的共享池内存使用情况:

 

Pool Name Begin MB End MB % Diff
large free memory 112.00 112.00 0.00
shared ASH buffers 25.60 25.60 0.00
shared Checkpoint queue 5.87 5.87 0.00
shared KCL name table 9.00 9.00 0.00
shared event statistics per sess 7.72 7.72 0.00
shared free memory 25.56 25.52 -0.12
shared gcs resources 143.39 143.39 0.00
shared gcs shadows 90.33 90.33 0.00
shared ges big msg buffers 15.03 15.03 0.00
shared ges reserved msg buffers 7.86 7.86 0.00
shared library cache 7.59 7.65 0.80
shared row cache 7.13 7.13 0.00
shared sql area 8.70 7.35 -15.57
streams free memory 64.00 64.00 0.00
buffer_cache 7,168.00 7,168.00 0.00
fixed_sga 2.09 2.09 0.00
log_buffer 3.09 3.09 0.00

 

红色部分标注了2个报告中差异最大的地方,在问题发生时共享池中gcs resources和gcs shadows 2种资源对比前一天增长了169M。 gcs资源在共享池中享有较高的优先级, 而普通的SQL语句或执行计划享有较低的优先级,因为gcs资源所占用空间的大量膨胀,导致在没有调大共享池大小的情况下sql area和row cache内存资源被换出进而引发SQL解析性能下降和ORA-04031问题。

 

gcs resources和gcs shadow资源均是Oracle RAC中特有的全局缓存服务资源,这些资源负责处理RAC中的全局buffer cache。 同时这些资源所占用共享池的空间视乎Oracle实例所使用高速缓存的大小而决定,Metalink文档说明了该问题:

 

“The ‘gcs resources’ and ‘gcs shadows’ structures are used for handling buffer caches in RAC, so their memory usages are depending on buffer cache size. We can use V$RESOURCE_LIMIT to monitor them.”

 

当实例高速缓存buffer cache被大小时gcs资源所占用的空间也相应增长,具体算法如下:

 

‘gcs_resources’ = initial_allocation * 120 bytes = “_gcs_resources parameter” * 120 bytes
‘gcs_shadows’ = initial_allocation * 72 bytes = “_gcs_shadow_locks parameter” * 72 bytes

select * from v$resource_limit where resource_name like '%gcs%';

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- ---
gcs_resources 507772 514607 976083 976083
gcs_shadows 133862 139927 976083 976083

 

我们可以通过现有的v$resource_limit视图中的INITIAL_ALLOCATION估算Buffer cache增加后的INITIAL_ALLOCATION数量,例如我们准备将db_cache_size从10g增加到20g,那么可以通过下列公式算出有必要增加的共享池大小:

 

add_to_shared_pool_size= 140 * Buffer_cache增加的兆数 * 192 bytes * 1.6

= 140 * 10* 1024 * 192 * 1.6 = 440401920 = 420M

 

问题总结

 

由于RAC环境中Oracle 使用共享池中的gcs resource/shadow 资源管理 全局缓存 , 当实例的Buffer Cache总量增加时gcs resource/shadow 这些资源的数目也会相应上升 , 这导致共享池中可用的剩余空间大幅下降,又因为 gcs 全局缓存资源在共享池中享有较高的优先级( perm ,且在10.2中 gcs资源不能和其他如row cache或library cache 共享一个Extent的内存区间) , 引发了大量的row/dictionary cache字典缓存和SQL执行计划被换出共享池, 引发大量的解析等待: cursor pin s on x 和 row cache lock ,  最终还是 没有避免ORA-04031 的错误被触发。 这里要补充一句, 因为这套10g 的系统没有 启用ASMM(Automatic Shared Memory Management) 特性 且 共享池本身设置地较小(shared_pool_size=512MB) , 都是导致该ORA-04031 错误较为显性地被触发的因素 。

 

<深入了解ASMM>中我介绍了ASMM的一些优点, 这里再罗列一下:

手动管理SGA的缺点在于:

  • 个别组件如shared pool、default buffer pool的大小存在最优值,但组件之间无法交换内存
  • 在9i中就提供了多种内存建议(advisor),但都要求人工手动干预
  • 无法适应工作负载存在变化的环境
  • 往往会导致内存浪费,没有用到实处
  • 若设置不当,引发著名的ORA-04031错误的可能性大大提高

ASMM自动管理SGA的优势在于:

  • 全自动的共享内存管理
  • 无需再配置每一个内存组件大小参数
  • 仅使用一个参数sga_target驱动
  • 有效利用所有可用的内存,极大程度上减少内存浪费
  • 对比MSMM其内存管理模式:
    • 更加动态
    • 更加灵活
    • 并具备适应性
  • 易于使用
  • 一定程度上增强了性能,因为内存分配更为合理了
  • 当某个组件急需更多内存时可以有效提供,因此可以一定程度避免ORA-04031的发生

 

解决方案

 

可以通过以下2种方式避免该RAC环境中特有的由增大Buffer_Cache导致GCS资源空间膨胀造成的ORA-04031问题:

  1. 在增加Buffer_Cache的同时估算所需相应增长的shared pool共享池大小
  2. 使用10g的SGA自动管理内存ASMM方式可以一定程度上避免ORA-04031错误的发生,但是自动管理方式存在实际使用时存在一些缺点,建议在启用ASMM:SGA_Target的同时,设置 shared_pool_size和 db_cache_size的最小大小,以最大可能避免因resize造成的问题; 同时建议设置_enabled_shared_pool_duration=false,禁用shared pool duration特性,也可以一定程度上减少ORA-04031发生的概率。

_enabled_shared_pool_duration:该参数控制是否启用10g中特有的shared pool duration特性,当我们设置sga_target为0时该参数为false;同时在10.2.0.5前若cursor_space_for_time设置为true时该参数也为false,不过在10.2.0.5以后cursor_space_for_time参数被废弃

11.2.0.3 实例启动现在提供Large Pages Information大内存页信息了

刚才发现在目前最新的11.2.0.3版本中实例instance startup时alert.log 中会提供Large Pages Information 大内存页的信息了:

 

Starting ORACLE instance (normal)
****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 16 MB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
  Total Shared Global Region size is 1202 MB. For optimal performance,
  prior to the next instance restart increase the number
  of unused Large Pages by atleast 601 2048 KB Large Pages (1202 MB)
  system wide to get 100% of the Shared
  Global Region allocated with Large pages
***********************************************************

Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options.
ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_3
System name:Linux
Node name:vrh2.oracle.com
Release:2.6.18-274.el5
Version:#1 SMP Mon Jul 25 13:17:49 EDT 2011
Machine:x86_64
Using parameter settings in server-side spfile /s01/orabase/product/11.2.0/dbhome_3/dbs/spfileEMREP.ora

 

该Large Pages Information还附有配置Large Page的RECOMMENDATION设置,当SGA_Target=1202MB时,推荐配置601个2048K页。
注意Large Page不能和11g AMM 自动内存管理一起使用,否则AMM将失去其意义。

Slide:深入了解Oracle自动内存管理ASMM by Maclean Liu

Know GCS AND GES structure size in shared pool

RAC环境中共享池很大一部分被gcs和ges资源所占用,一般来说这些资源对象都是永久的(perm)的,所以我们无法期待LRU或flush shared_pool操作能够清理这些资源。

在使用大缓存(large buffer cache)的RAC实例环境中,查询v$sgastat内存动态性能视图时总是能发现’gcs resources’、’gcs shadows’、’ ges resource’、’ges enqueues ‘这些组件占用了共享池中的大量内存,为了避免shared pool出现著名的ORA-04031错误,Oracle推荐在RAC环境中设置较大的shared_pool_size初始化参数,此外显示地设置较大的GCS和GES资源结构的初始化分配数(INITIAL_ALLOCATION)也有利于避免ORA-4031。

这些控制GES和GCS资源结构初始化分配数量的参数主要包括:

  • _gcs_resources  number of gcs resources to be allocated GCS Resources Number of GCS resource structures determined by
    _gcs_resources parameter
    Stored in segmented array
    Externalized in X$KJBR
    Number of free GCS resource structures in X$KJBRFX
  • _gcs_shadow_locks number of pcm shadow locks to be allocated GCS Enqueues (Shadows/Clients) Number of GCS enqueue structures determined by  _gcs_shadow_locks parameter Stored in segmented array
    Externalized in X$KJBL
    Number of free GCS enqueue structures in X$KJBLFX
  • _lm_ress number of resources configured for cluster database LM_RESS controls the number of resources that can be locked by each lock manager instance. These resources include lock resources allocated for DML, DDL (data dictionary locks), data dictionary, and library cache locks plus the file and log management locks. Stored in heap
    Externalized in X$KJIRFT
  • _lm_locks number of enqueues configured for cluster database Stored in segmented array
    Externalized in X$KJILKFT

为了更好地在RAC环境中设置shared_pool_size共享池的大小(手动设置该参数并不会disable AMM or ASMM),我们很有必要评估大量初始化分配的全局资源本身将占用shared pool多大的空间。

我们可以通过v$resource_limit视图了解这些GES、GCS全局资源的分配情况:

SQL> select * from v$version;

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com



SQL> select * from v$resource_limit where resource_name in ('gcs_resources', 'gcs_shadows','ges_ress','ges_locks'); 

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION        LIMIT_VALUE
------------------------------ ------------------- --------------- ------------------------- ------------------
ges_ress                                      7223            7486    1000000                 UNLIMITED
ges_locks                                     4944            5027    1000000                 UNLIMITED
gcs_resources                                 4021            4021     114466                    114466
gcs_shadows                                   3925            3925     114466                    114466

可以通过v$sgastat视图了解这些全局资源占用了多少空间:

select *
  from v$sgastat
 where name in
       ('ges resource ', 'ges enqueues', 'gcs resources', 'gcs shadows');

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  gcs resources                16483232
shared pool  gcs shadows                  11904560
shared pool  ges enqueues                 47809680
shared pool  ges resource                288405768

单个gcs_resources结构大约占用120 bytes
单个gcs_shadows 结构大约占用72 bytes
单个ges_resource 结构大约占用288 bytes

我们可以使用一下初步估算GES、GCS资源结构将至少占用多大的共享池资源:

‘gcs_resources’ = initial_allocation * 120 bytes = “_gcs_resources parameter” * 120 bytes
‘gcs_shadows’ = initial_allocation * 72 bytes = “_gcs_shadow_locks parameter” * 72 bytes
‘ges_resource’= initial_allocation * 288 bytes = “_lm_ress parameter ” * 288 bytes

注意这里计算出的仅仅是理论的最小值,实际值因为内存分配的机制所以必然会远大于计算值

如上例中 gcs resources = 114466 * 120 =13735920 << 实际值的16483232
gcs_shadows = 114466 * 72 = 8241552 << 实际值的11904560
ges_resource = 1000000 * 288 = 288000000 < 实际的288405768

一般来说我们将计算值 * 160% 后可以得出一个较为客观的估算值。

注意以上公式只是为我们在RAC环境中调优共享池的大小提供参考的依据。当我们观察v$resource_limit视图并认为需要提高GES、GSC资源的初始化分配数目时,可以参照上述方式估算出必要的shared_pool_size或sga_target大小。

PL/SQL Virtual Machine Memory Usage

PL/SQL Program Units即PL/SQL程序单元,常被叫做”library units”或lib-units.

参考以下模块类型:

  • package spec
  • package body
  • top-level function or procedure
  • type spec
  • type body
  • trigger
  • anonymous blocks.

PL/SQL 虚拟机的内存使用主要体现在4个方面:

  • PGA
    • PL/SQL stack call,用于保存本地变量和其他一些状态结构
    • NCOMP生成的动态链接库文件
  • CGA
    • 二级内存(secondary memory),分配的堆和大的可收缩本地变量如大的strings、Lob或collections
  • UGA
    • 程度单元的实例(library-unit instantiations),如package global variables, DL0/ DL1 dependency vectors, display frame等
  • SGA
    共享池中的MCODE子堆

KGL – Kernel Generic Library Manager
该layer管理会话间需要共享的资源,如PL/SQL MCODE,Diana,Source,SQL cursor,SQL Plan)

KGI – Kernel Generic Instantiation Layer.
该layer管理特定会话非共享的资源,如实例化的包含了包全局变量状态信息的PL/SQL程序单元

KOH/KGH 该layer用以管理heap service堆服务

KGL_Entry_PLSQL_UNIT

 

PLSQL MCODE Heap的属性

  • machine dependent binary format for a compiled PL/SQL library-unit.
  • to execute code in a lib-unit, its MCODE heap must be loaded in memory.
  • MCODE is loaded in SGA and is “pinned” for CALL duration.
  • once unpinned, the heap may be aged; hence, may need to get re-loaded.
  • important to page large data structures in SGA.

MCODE Heap: Subcomponents

  • EntryPoint Piece (PL_UEP)
  • Code Segment or Byte Code Piece (PL_UCP)
  • Constant Pool:
    • Data Segment (PL_UKP)
    • Handle Segment (PL_UHS)
  • SQL Strings Table (PL_USP)

PL/SQL Instantiations

  • When a lib-unit is first referenced by a program (session) an instantiation of the lib unit is created.
  • PL/SQL relies on KGI for inst obj mgmt.
  • A PL/SQL lib-unit instantiation consists of:
    • PLIO struct (the handle of the PL/SQL inst obj)
    • Static Frame
    • Secondary (Heap) Memory for package globals
  • PLIO Struct
    • first portion of PLIO struct is the KGIOB struct (kgi’s portion of the object handle)
    • points to the static frame struct (PLIOST)
    • also contains other book-keeping info (such as memory duration of instantiation’s work area, etc.)
  • Static Frame:
    • represents that part of instantiation’s work area whose size is compile-time determined.
    • the root of the static frame is PLIOST struct which leads the following sub-pieces:
      • depends-on array to global variable vectors (DL0)
      • depends-on array to other instantiations (DL1)
      • Display Frame (DPF)
      • global variable vector for this unit (GF)
      • primary memory for global variables.
  • Secondary Memory for package globals
    • used to allocate data types that are stored out-of-line (heap allocated) e.g., collections, large strings, large records, LOBs, datetime types, etc.

Structure of a PLSQL Instantiation Object
Memory Model In PLSQL Instantiation

Data Pump failed with ORA-04031/ORA-4030?

在10g中引入了数据泵Data Pump导入导出工具,DataPump的工作流如下图:

data_pump_workflow

我们在使用Data Pump工具时经常会遇到著名的ORA-04031/ORA-04030错误,主要影响DataPump的内存组件有PGA和SGA中的共享池Shared Pool、流池Streams Pool。Expdp/Impdp对shared Pool的开销主要体现在其运行过程中需要调用一系列的包体PACKGE BODY,它们包括:

PACKAGE_NAME                             TYPE                 SHARABLE_MEM
---------------------------------------- -------------------- ------------
SYS.KUPM$MCP                             PACKAGE BODY               425448
SYS.KUPW$WORKER                          PACKAGE BODY               386000
SYS.DBMS_METADATA_INT                    PACKAGE BODY               325856
SYS.DBMS_REPCAT_UTL                      PACKAGE BODY               269064
SYS.DBMS_METADATA                        PACKAGE BODY               226624
SYS.DBMS_DATAPUMP                        PACKAGE BODY               192888
SYS.DBMS_PRVTAQIS                        PACKAGE BODY               147288
SYS.DBMS_PRVTAQIM                        PACKAGE BODY               142680
SYS.KUPF$FILE                            PACKAGE BODY               142008
SYS.DBMS_METADATA_UTIL                   PACKAGE BODY               115224
SYS.KUPD$DATA                            PACKAGE BODY               109400
SYS.DBMS_LOGREP_EXP                      PACKAGE BODY               102648
SYS.DBMS_SCHED_MAIN_EXPORT               PACKAGE BODY                86816
SYS.DBMS_SYS_SQL                         PACKAGE BODY                72280
SYS.DBMS_AW                              PACKAGE BODY                68128
SYS.DBMS_SQL                             PACKAGE BODY                68064
SYS.DBMS_DM_EXP_INTERNAL                 PACKAGE BODY                57040
SYS.DBMS_AW_EXP                          PACKAGE BODY                52256
SYS.KUPC$QUE_INT                         PACKAGE BODY                52088
SYS.DBMS_CUBE_EXP                        PACKAGE BODY                48432
SYS.KUPV$FT_INT                          PACKAGE BODY                47184
SYS.DBMS_LOGREP_UTIL                     PACKAGE BODY                45856
SYS.DBMS_CDC_EXPDP                       PACKAGE BODY                44616
SYS.DBMS_EXPORT_EXTENSION                PACKAGE BODY                38728
SYS.DBMS_CDC_UTILITY                     PACKAGE BODY                37712
SYS.KUPV$FT                              PACKAGE BODY                34536
SYS.DBMS_DM_MODEL_EXP                    PACKAGE BODY                33904
SYS.DBMS_AQ                              PACKAGE BODY                33512
SYS.DBMS_IJOB                            PACKAGE BODY                33488
SYS.DBMS_AQ_SYS_EXP_INTERNAL             PACKAGE BODY                29464
SYS.DBMS_FILE_GROUP_EXP                  PACKAGE BODY                29440
SYS.KUPD$DATA_INT                        PACKAGE BODY                29424
SYS.DBMS_RULE_EXP_RL_INTERNAL            PACKAGE BODY                29400
SYS.KUPP$PROC                            PACKAGE BODY                25368
SYS.DBMS_AQ_IMPORT_INTERNAL              PACKAGE BODY                25352
SYS.DBMS_AQADM                           PACKAGE BODY                25320
SYS.DBMS_ODCI                            PACKAGE BODY                21200
SYS.UTL_XML                              PACKAGE BODY                21200
SYS.DBMS_CDC_DPUTIL                      PACKAGE BODY                21192
SYS.DBMS_STREAMS_DATAPUMP_UTIL           PACKAGE BODY                21144
SYS.KUPF$FILE_INT                        PACKAGE BODY                17104
SYS.DBMS_SESSION                         PACKAGE BODY                17048
SYS.DBMS_AQ_SYS_EXP_ACTIONS              PACKAGE BODY                17048
SYS.DBMS_STREAMS_DATAPUMP                PACKAGE BODY                17032
SYS.KUPC$QUEUE_INT                       PACKAGE BODY                17032
SYS.DBMS_RULE_ADM                        PACKAGE BODY                17032
SYS.DBMS_LOCK                            PACKAGE BODY                17032
SYS.DBMS_RULEADM_INTERNAL                PACKAGE BODY                12952
SYS.DBMS_TRANSFORM_EXIMP_INTERNAL        PACKAGE BODY                12952
SYS.DBMS_REFRESH_EXP_SITES               PACKAGE BODY                12952
SYS.DBMS_REPCAT_RGT_EXP                  PACKAGE BODY                12936
SYS.UTL_RAW                              PACKAGE BODY                12936
SYS.DBMS_FLASHBACK                       PACKAGE BODY                12936
SYS.DBMS_TRANSFORM_EXIMP                 PACKAGE BODY                12936
SYS.DBMS_SCHED_JOB_EXPORT                PACKAGE BODY                12936
SYS.DBMS_REFRESH_EXP_LWM                 PACKAGE BODY                12936
SYS.DBMS_SCHED_EXPORT_CALLOUTS           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_QUEUE_TABLES             PACKAGE BODY                 8856
SYS.DBMS_DEFER_IMPORT_INTERNAL           PACKAGE BODY                 8856
SYS.DBMS_INTERNAL_SAFE_SCN               PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_INDEX_TABLES             PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_HISTORY_TABLES           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SIGNATURE_TABLES         PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_DEQUEUELOG_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SUBSCRIBER_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_CMT_TIME_TABLES          PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_TIMEMGR_TABLES           PACKAGE BODY                 8856
SYS.DBMS_RULE_EXP_RULES                  PACKAGE BODY                 8840
SYS.DBMS_RULE_EXP_UTLI                   PACKAGE BODY                 8840
SYS.DBMS_AQADM_INV                       PACKAGE BODY                 8840
SYS.DBMS_ZHELP_IR                        PACKAGE BODY                 8840
SYS.KUPC$QUEUE                           PACKAGE BODY                 8840
SYS.DBMS_AQ_EXP_ZECURITY                 PACKAGE BODY                 8840
SYS.KUPU$UTILITIES_INT                   PACKAGE BODY                 8840
SYS.KUPU$UTILITIES                       PACKAGE BODY                 8840
SYS.DBMS_REPCAT_EXP                      PACKAGE BODY                 8840
SYS.DBMS_CDC_EXPVDP                      PACKAGE BODY                 8840
total<<10MB

DataPump内部利用高级队列 Advanced Queue技术,所以也会用到流池Streams Pool,与之相关的Streams Pool组件包括:

POOL         NAME                            BYTES
------------ -------------------------- ----------
streams pool sob_kgqmrec                     19584
streams pool Sender info                     17616
streams pool recov_kgqbtctx                  11904
streams pool kwqbcqini:spilledovermsgs        3168
streams pool kgqbt_alloc_block                2096
streams pool recov_kgqmsub                    1608
streams pool kwqbsinfy:bqg                    1232
streams pool recov_kgqmctx                    1104
streams pool kwqbsinfy:mpr                    1088
streams pool kwqbsinfy:sta                     768
streams pool kgqmsub                           584
streams pool fixed allocation callback         448
streams pool kwqbsinfy:cco                     376
streams pool image handles                     288
streams pool kwqbsinfy:bms                     256
streams pool name_kgqmsub                      256
streams pool spilled:kwqbl                     256
streams pool deqtree_kgqmctx                   144
streams pool substree_kgqmctx                  144
streams pool kgqmdm_fl_1                       144
streams pool time manager index                144
streams pool msgtree_kgqmctx                   144

当Streams Pool分配过小同样可能引发Expdp/Impdp因ORA-04031 (“streams pool”, …)错误而意外终止,详见<EXPDP Fails With ORA-04031 (“streams pool”, …) [ID 457724.1]>

此外DataPump还可能从Large Pool中分配PX msg pool作为并行进程通信池,但是这种内存开销很小。

DataPump对PGA的消耗主要体现在koh-kghu sessi sub-heap上,已知的Bug 10404544(ORA – 4030 DURING EXPDP)、7681160(EXPDP FAILS WITH ORA-4030 WHEN SELECT FROM SYS.KU$_PROCACT_SCHEMA_VIEW)说明该子堆sub-heap(和另一个sub-heap kxs-heap-w)在10.2.0.4上使用expdp时可能引发内存泄露memory-leak。我们来具体看一下DataPump Manager DM00的PGA使用情况:

SQL> oradebug setospid 5278;
Oracle pid: 51, Unix process pid: 5278, image: oracle@rh2.oracle.com (DM00)
SQL> oradebug dump heapdump 536870917;
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc
SQL> select pga_alloc_mem / 1024 / 1024, pga_used_mem / 1024 / 1024, pname
2    from v$process
3   where addr = '00000000DCBC51F8'
4  /
PGA_ALLOC_MEM/1024/1024 PGA_USED_MEM/1024/1024 PNAME
----------------------- ---------------------- -----
8.06555557             7.25846481 DM00
[oracle@rh2 ~]$ egrep "HEAP DUMP heap name|Total heap size|Permanent space" 
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc
HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568
HEAP DUMP heap name="pga heap"  desc=0xb7c8ba0
Total heap size    =  2353064
Permanent space    =   654544
HEAP DUMP heap name="KFK_IO_SUBHEAP"  desc=0x7fae4fdfaa98
Total heap size    =   730640
Permanent space    =       80
HEAP DUMP heap name="koh-kghu call "  desc=0x7fae4fb650d0
Total heap size    =   246080
Permanent space    =       80
HEAP DUMP heap name="diag pga"  desc=0x7fae500347e0
Total heap size    =    70816
Permanent space    =    13472
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fdd15c8
Total heap size    =    68064
Permanent space    =     1336
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb50ee0
Total heap size    =    12272
Permanent space    =     2096
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb66c00
Total heap size    =    11784
Permanent space    =     1856
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc14fc0
Total heap size    =     9816
Permanent space    =     1392
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc10068
Total heap size    =     9664
Permanent space    =     1240
HEAP DUMP heap name="PLS PGA hp"  desc=0x7fae4fdeeab8
Total heap size    =    46784
Permanent space    =       80
HEAP DUMP heap name="top call heap"  desc=0xb7ce3c0
Total heap size    =   458584
Permanent space    =     1920
HEAP DUMP heap name="callheap"  desc=0xb7cd578
Total heap size    =   343104
Permanent space    =    65536
HEAP DUMP heap name="callheap"  desc=0xb7cd4c0
Total heap size    =    21616
Permanent space    =      968
HEAP DUMP heap name="kti call subhe"  desc=0x7fae4fdee018
Total heap size    =    20584
Permanent space    =       80
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd108
Total heap size    =     8952
Permanent space    =      200
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd030
Total heap size    =     2072
Permanent space    =     1672
HEAP DUMP heap name="top uga heap"  desc=0xb7ce5e0
Total heap size    =  4520496
Permanent space    =       80
HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568

一般遇到这类memory leak的问题,Oracle内部会使用一个名叫heap.awk的dump分析工具(类似于ass.awk)来找出问题子堆(problematic sub-heap),我们可以使用图形化的免费工具Membai来替代heap.awk。

heap_dump_analysis

总结:

DataPump工具Expdp/Impdp需要从PGA和SGA的Shared Pool、Streams Pool和Large Pool分配必要的内存。为了避免Expdp/Impdp出现ORA-04031/ORA-04030错误,我们有必要在自动管理模式下设置合理的pga_aggregate_target和sga_target(抑或者memory_target)内存初始化参数,如果使用手动的SGA管理的话,那么有必要保证shared_pool_size的设置适宜,对于Streams Pool和Large Pool一般设置为150MB大小。

深入了解ASMM

每一个Oracle的初学者在入门阶段都会接触到SGA/PGA的知识,如果是从10g开始学习那么会多或少会对ASMM有所了解,从使用的角度来说ASMM的出现极大地简化了Oracle内存初始化参数的设置,在ASMM的使用上高级DBA和初学者不会有太大的差别;很多人因此而认为ASMM极大程度地减少了数据库对于专业DBA的依赖:如果我们有一个足够智能的DB,那么为什么还要花费金钱雇佣DBA呢?这似乎是时下一种十分流行的想法。当然这种想法我个人是不能苟同的,ASMM一定程度上带来了便利,更大程度上它是一个黑盒,黑盒里面藏了很多秘密,这些秘密带来比手动管理更多的不确定性;在10g release 1和10.2的早期版本中ASMM扮演的角色有点像一个闯祸精,另一个让用户对ASMM很不待见的原因是ASMM直接拖慢了startup的速度。一个个人观点是ASMM也好AMM也罢,都要求产品数据库DBA掌握更多SGA/PGA相关的知识才能成功”驾驭”这些”有智力”的家伙,有点夸张的说这个时候的DBA很像一个chemist(需要和一大堆以1个或2个下划线开头的奇怪参数打交道)。

为了不辱使命我们真的有必要了解一下ASMM的基本知识,显然这并不是一件容易的事情……

Oracle的SGA基本内存组件从9i开始并没有非常大的变化,他们包括:

  • Buffer Cache 我们常说的数据库高速缓存,虽然我一直不明白要冠以高速之名
    • Default Pool                  默认的缓冲池,大小由DB_CACHE_SIZE决定
    • Keep Pool                     持久的缓冲池,大小由DB_KEEP_CACHE_SIZE决定
    • Non standard pool         非标准块标准池,大小由DB_nK_cache_size决定
    • Recycle pool                 回收池,大小由db_recycle_cache_size决定
  • Shared Pool 共享池,大小由shared_pool_size决定
    • Library cache   俗称的库缓存
    • Row cache      行缓存,也叫字典缓存
  • Java Pool         java池,大小由Java_pool_size决定
  • Large Pool       大池,大小由Large_pool_size决定
  • Fixed SGA       固定的SGA区域,包含了Oracle内部的数据结构,一般被存放在第一个granule中

在9i中尚未引入ASMM,唯一的选择是手动管理的SGA,有时候也叫做MSMM。在9i中除去buffer cache的大小可以手动修改外,其余组件都无法动态修改。因为缺乏一种动态管理的机制,所以在9i中如果有某个内存区域有急用,也无法从其他有空闲内存的组件中捐献一些来解燃眉之急。

手动管理SGA的缺点在于:

  • 个别组件如shared pool、default buffer pool的大小存在最优值,但组件之间无法交换内存
  • 在9i中就提供了多种内存建议(advisor),但都要求人工手动干预
  • 无法适应工作负载存在变化的环境
  • 往往会导致内存浪费,没有用到实处
  • 若设置不当,引发著名的ORA-04031错误的可能性大大提高

ASMM的优势在于:

  • 全自动的共享内存管理
  • 无需再配置每一个内存组件大小参数
  • 仅使用一个参数sga_target驱动
  • 有效利用所有可用的内存,极大程度上减少内存浪费
  • 对比MSMM其内存管理模式:
    • 更加动态
    • 更加灵活
    • 并具备适应性
  • 易于使用
  • 一定程度上增强了性能,因为内存分配更为合理了
  • 当某个组件急需更多内存时可以有效提供,因此可以一定程度避免ORA-04031的发生

ASMM主要可以囊括为三个部分:
1.由一个新参数sga_target驱动的管理模式
2.内存交换的模型,包括了:内存组件(memory component),内存代理(memory broker)和内存交换机制(memory mechanism)
3.内存建议(memory advisor)

ASMM下一部分参数是自动设置的(Automatically set),这些参数包括:shared_pool_size、db_cache_size、java_pool_size、large_pool _size和streams_pool_size;而另外一些是需要手动设置的静态参数,包括了:db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size、log_buffer以及固定SGA内存结构等,如果以上没有手动设置这些参数的话,除去log_buffer和fixed sga其他内存组件一般默认大小为零。

通过ASMM用户仅需要设置一个sga_target参数,其他参数都将由ASMM自行内部控制。但如果用户依旧设置了如db_cache_size、java_pool_size等参数,那么这些参数被认为是相关内存组件的最小限制,同时每个内存组件的大小也存在一个最大上限(内部的)。在实例启动阶段,Oracle会将必要的内存颗粒(granule,当SGA<1GB时granule大小为4M,当SGA>1GB时granule大小为16M)分配给内存组件,多余没有分配出去的全都分配给buffer cache,之后随着系统的不断活跃更多的内存颗粒(granule)将被分配给急需内存的组件。我们可以动态地修改sga_target参数,前提是所在的系统平台支持动态地共享内存(dism,主流平台都支持)。使用ASMM的一个必要条件是初始化参数statistics_level必须设置为typical或ALL,如果设置为BASIC那么MMON后台进程(Memory Monitor is a background process that gathers memory statistics (snapshots) stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds)将无法有效分析内存的使用的统计信息从而驱动ASMM的自动调优,实际上我们不能同时设置sga_target为非零值和statistics_level为BASIC:

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 2000M
sga_target                           big integer 2000M
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 2000M
SQL> alter system set statistics_level=BASIC;
alter system set statistics_level=BASIC
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

如果使用了server parameter file即spfile的话,ASMM会在实例shutdown之前将当前实际的内存组件大小(Oracle认为这是最优的,但实际上可能不是)保存到spfile中,如果你使用strings命令打印过spfile的内容的可以发现一些以双下划线开头的参数,如:

G10R2.__db_cache_size=973078528
G10R2.__java_pool_size=16777216
G10R2.__large_pool_size=16777216
G10R2.__shared_pool_size=1006632960
G10R2.__streams_pool_size=67108864

这些在spfile保存的组件大小会在下次启动时被沿用,以达到将已经实践得出的”最佳值”记住的目的,这样下次就不用从头再”学习”了。

在ASMM的内存交换模型中存在三类组件

  1. 可调优组件(tunable):可调优组件是那些大小可以随之变化且若过小仅损害少量性能。buffer cache就是一个经典的例子,cache过小的情况下应用程序仍能正常运行,但带来的代价是更多的 IO。注意可调优组件同时存在一个下限。举例来说共享池由库缓存Library cache和其他一些 subheap子堆组成,那么共享池就存在一个下限要保证至少能放下某个一个时刻并行打开的游标(open cursors)以及负担其他共享池client的运行时内存分配需求。类似的buffer cache也存在一个下限,该下限至少要大于并行被pin住的buffer的总和(虽然这部分很小)
  2. 不可调组件(Un-tunable):不可调组件是那些存在最小下限的内存组件,这个最小下限足够让应用程序正常运行,超过这个上限并不会带来额外的性能收益。在这类组件中large pool是一个典型。
  3. 固定大小组件(Fixed Size):自动调优框架之外的组件,一般为固定大小。这些组件的大小仅在手动调整时改变。例如非标准块大小的高速缓冲池

 

内存交换模型中内存大小调整的申请(memory resize request)存在三种不同的模式,它们分别是:

立即内存申请(Immediate Request):这种申请模式一般出现在ASMM管理的某个自动调优组件在无法分配到连续可用内存块(chunk)时,为了避免出现OUT-OF-MEMORY(ORA-04031)的错误,系统尝试从其他候选组件中转移一个内存颗粒(granule)过来。需要注意的是当前可能没有可用的全空granule,这时就会出现不完整的转移,在此情形下系统会开始从候选组件中清理内存颗粒以满足内存申请,并将一个granule不完整地转移给急需内存的组件。

延迟内存申请(Deferred Request):这种申请一般出现在系统认为存在一种更为合理的内存分配时,考虑在组件之间转移一个或多个granule。用以判定最佳内存分配的依据是MMON进程所提供的统计信息delta.

手动内存申请(Manual Request):这种申请仅发生在用户使用alter system命令手动调整内存组件的大小时。在这种模式下仅允许使用空的内存颗粒参与大小调整。如果当时没有足够的空granule,那么针对组件grow操作会返回ORA-4033错误,而针对组件shrink操作会返回ORA-4034错误。

当ASMM被启用后,内存代理(Memory Broker)定期执行上图所示的活动。以上操作都处于延迟内存申请模式下(Deferred)。其目的是通过自动化的作业来调优自动调优组件(auto-tunable component)的大小以适应不断改变的工作负载,将内存分配到最需要它们的地方。MMON辅助进程会在后台不断将统计信息和内存建议定期地捕获到循环使用的内存中,并计算不同时期缓存信息之间的差值;MMON还会基于Memory Broker的策略分析这些差值并估算出长期和短期内的趋势。最后MMON基于以上分析生成一些内存组件的大小调整决议并将相应的申请发送到一个系统申请队列中(resize request system queue)。MMAN后台进程(Memory Manager is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases)会定期扫描系统申请队列并执行内存转移。

在10gR1中Shared Pool的shrink收缩操作存在一些缺陷,造成缺陷的原因是在该版本中Buffer Cache还没有能力共享使用一个granule,这是因为Buffer Cache的granule的尾部由granule header和Metadata(可能是buffer header或者RAC中的Lock Elements)拼接组成,在其尾部不容许存在空洞。另一个原因是当时的shared pool允许不同生命周期duration(以后会介绍)的chunk存放在同一个granule中,这造成共享池无法完全释放granule。到10gR2中通过对Buffer Cache Granule结构的修改允许在granule header和buffer及Metadata(buffer header或LE)存在缝隙,同时shared pool中不同duration的chunk将不在共享同一个granule,通过以上改进buffer cache与shared pool间的内存交换变得可行。此外在10gr2中streams pool也开始支持内存交换(实际根据不同的streams pool duration存在限制)

当某个组件扮演捐献者(Donor,下面的trace中会看到)角色时,它可能将一个不完整granule转移给buffer cache,那么在该granule被使用前需要完成以下步骤:

  1. 首先在该granule的尾部生成新的granule header
  2. 针对剩下的chunk判定在该granule中是否还有未使用的buffer header
  3. 如果有,那么将chunk中的内存转换为一个数据块。否则将之转换为一个metadata块
  4. 重复以2-4步骤,直到该granule被转换完

接着我们来了解一下内存转移的基本原理,当将buffer cache中的granule转移给shared pool时,将按照以下步骤:

  1. MMAN后台进程找到一块属于buffer cache的合适granule
  2. MMAN将看中的granule置入quiesce列表中(Moving 1 granule from inuse to quiesce list of DEFAULT buffer cache for an immediate req)
  3. DBWR将负责写出置入quiesced列表中granule里面的脏buffer(dirty buffer)
  4. MMAN将为shared pool调用消费回调函数(consume callback),granule中free的chunk都会被shared pool消费(consume)掉,并对共享池新的内存分配可用。从这里开始该granule变成一个shared granule共享内存颗粒,注意不要认为这个时候该granule的空间全部属于共享池了,实际上有部分pin住的buffer及其Metadata(上述的buffer header和LE)的空间仍被buffer cache占用着
  5. 最终该granule将完整的转移给shared pool,这时此granule不再是一个shared共享的

实际使用中ASMM受到众多隐藏参数的影响,其中比较主要的参数有:

  1. _enabled_shared_pool_duration:该参数控制是否启用10g中特有的shared pool duration特性,当我们设置sga_target为0时该参数为false;同时在10.2.0.5前若cursor_space_for_time设置为true时该参数也为false,不过在10.2.0.5以后cursor_space_for_time参数被废弃
  2. _memory_broker_shrink_heaps:若该参数设置为0那么Oracle不会去收缩shared pool或java pool,当该参数大于0,会在shrink request失败后等待该参数指定秒数时间后再次申请
  3. _memory_management_tracing: 该参数控制针对MMON和MMAN后台进程包括内存建议(advisor)和内存代理(Memory Broker)决议的相关trace的级别;针对ORA-04031的诊断可以设置为36,设置为8启用针对启动期间组件大小的trace,设置为23启动针对Memory Broker decision的跟踪,设置为32将转储cache resize的细节;该参数的具体级别如下:
Level Contents
0x01 Enables statistics tracing
0x02 Enables policy tracing
0x04 Enables transfer of granules tracing
0x08 Enables startup tracing
0x10 Enables tuning tracing
0x20 Enables cache tracing

 

接下来我们通过设置_memory_management_tracing隐藏参数和DUMP_TRANSFER_OPS转储来实地了解一次完整的内存转移,和不完整的内存转移。以下演示的完整trace文件可以从这里下载mman_tracetransfer_ops_dump

SQL> alter system set "_memory_management_tracing"=63;
System altered
Operation make shared pool grow and buffer cache shrink!!!..............

以下为一个完整granule转移的过程,包括了对default buffer pool的resize操作:

AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0
/* 这里的0xdc9c2628是前台进程的addr */
AUTO SGA: IMMEDIATE, FG request 0xdc9c2628
/* 这里可以看到前台进程的Immediate立即申请  */
AUTO SGA: Receiver of memory is shared pool, size=16, state=3, flg=0
/* 此次申请的收益人是shared pool,共享池,其大小为16个granule,处于grow状态 */
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=106, state=4, flg=0
/* 此处的捐献者是Default buffer cache,高速缓存,其大小为106个granule,处于shrink状态 */
AUTO SGA: Memory requested=3896, remaining=3896
/* 这里immeidate request所要求的空间是3896 bytes */
AUTO SGA: Memory received=0, minreq=3896, gransz=16777216
/* 这里没有free的granule,所以received为0,gransz为granule的大小 */
AUTO SGA: Request 0xdc9c2628 status is INACTIVE
/* 因为没有空的内存颗粒,先将申请置于inactive状态 */
AUTO SGA: Init bef rsz for request 0xdc9c2628
/* 为相关申请初始化before-process大小调整  */
AUTO SGA: Set rq dc9c2628 status to PENDING
/* 将request置于pending状态 */
AUTO SGA: 0xca000000 rem=3896, rcvd=16777216, 105, 16777216, 17
/* 返回起始地址为0xca000000的16M大小granule */
AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, a
AUTO SGA: Resize done for pool DEFAULT, 8192
/* 完成对default pool的resize */
AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fff917964a0
AUTO SGA: Receiver of memory is shared pool, size=17, state=0, flg=0
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=105, state=0, flg=0
AUTO SGA: Memory requested=3896, remaining=0
AUTO SGA: Memory received=16777216, minreq=3896, gransz=16777216
AUTO SGA: Request 0x7fff917964a0 status is COMPLETE
/* shared pool成功收到16M的granule */
AUTO SGA: activated granule 0xca000000 of shared pool

以下为一个partial granule不完全内存颗粒的转移过程trace:

AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0
AUTO SGA: IMMEDIATE, FG request 0xdc9c2628
AUTO SGA: Receiver of memory is shared pool, size=82, state=3, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=36, state=4, flg=1
/* 此处的受益者仍为shared pool,而捐献者是default buffer cache */
AUTO SGA: Memory requested=4120, remaining=4120
AUTO SGA: Memory received=0, minreq=4120, gransz=16777216
AUTO SGA: Request 0xdc9c2628 status is INACTIVE
AUTO SGA: Init bef rsz for request 0xdc9c2628
AUTO SGA: Set rq dc9c2628 status to PENDING
AUTO SGA: Moving granule 0x93000000 of DEFAULT buffer cache to activate list
AUTO SGA: Moving 1 granule 0x8c000000 from inuse to quiesce list of DEFAULT buffer cache for an immediate req
/* 以上将buffer cache中起始地址为0x8c000000的granule从使用中列表inuse list,
移动到静默列表quiesce list中 */
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: activated granule 0x93000000 of DEFAULT buffer cache
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
/ * 等待dbwr写出0x8c000000 granule中所有的dirty buffer */
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
.........................................
AUTO SGA: Rcv shared pool consuming 8192 from 0x8c000000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 90112 from 0x8c002000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 24576 from 0x8c01a000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 65536 from 0x8c022000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 131072 from 0x8c034000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 286720 from 0x8c056000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 98304 from 0x8c09e000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 106496 from 0x8c0b8000 in granule 0x8c000000; owner is DEFAULT buffer cache
.....................
/* 以上shared pool开始消费0x8c000000 granule中的chunk,
但此granule的owner暂时仍为default buffer cache */
AUTO SGA: Imm xfer 0x8c000000 from quiesce list of DEFAULT buffer cache to partial inuse list of shared pool
/* 以上将0x8c000000 granule从default buffer cache的静默列表转移到shared pool的不完整inuse list */
AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, 20a
AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fffe9bcd0e0
AUTO SGA: Receiver of memory is shared pool, size=83, state=0, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=35, state=0, flg=1
AUTO SGA: Memory requested=4120, remaining=0
AUTO SGA: Memory received=14934016, minreq=4120, gransz=16777216
AUTO SGA: Request 0x7fffe9bcd0e0 status is COMPLETE
/* 以上一个partial transfer完成 */

对应于以上partial transfer我们可以通过DUMP_TRANSFER_OPS来了解该0x8c000000 partial granule的实际使用情况,如:

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump DUMP_TRANSFER_OPS 1;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_21482.trc
=======================trace content==============================
GRANULE SIZE is 16777216
COMPONENT NAME : shared pool
Number of granules in partially inuse list (listid 4) is 23
Granule addr is 0x8c000000 Granule owner is DEFAULT buffer cache
/* 该0x8c000000 granule在shared pool的partially inuse list,
但这里它的owner仍为default buffer cache */
Granule 0x8c000000 dump from owner perspective
gptr = 0x8c000000, num buf hdrs = 1989, num buffers = 156, ghdr = 0x8cffe000
/ * 可以看到该granule的granule header地址位于0x8cffe000,
其中共有156个buffer block,1989个buffer header */
/* 以下granule中具体的内容,实际既包含了buffer cache也有shared pool chunk */
BH:0x8cf76018 BA:(nil) st:11 flg:20000
BH:0x8cf76128 BA:(nil) st:11 flg:20000
BH:0x8cf76238 BA:(nil) st:11 flg:20000
BH:0x8cf76348 BA:(nil) st:11 flg:20000
BH:0x8cf76458 BA:(nil) st:11 flg:20000
BH:0x8cf76568 BA:(nil) st:11 flg:20000
BH:0x8cf76678 BA:(nil) st:11 flg:20000
BH:0x8cf76788 BA:(nil) st:11 flg:20000
BH:0x8cf76898 BA:(nil) st:11 flg:20000
BH:0x8cf769a8 BA:(nil) st:11 flg:20000
BH:0x8cf76ab8 BA:(nil) st:11 flg:20000
BH:0x8cf76bc8 BA:(nil) st:11 flg:20000
BH:0x8cf76cd8 BA:0x8c018000 st:1 flg:622202
...............
Address 0x8cf30000 to 0x8cf74000 not in cache
Address 0x8cf74000 to 0x8d000000 in cache
Granule 0x8c000000 dump from receivers perspective
Dumping layout
Address 0x8c000000 to 0x8c018000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c018000 to 0x8c01a000 not in this pool
Address 0x8c01a000 to 0x8c020000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c020000 to 0x8c022000 not in this pool
Address 0x8c022000 to 0x8c032000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c032000 to 0x8c034000 not in this pool
Address 0x8c034000 to 0x8c054000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c054000 to 0x8c056000 not in this pool
Address 0x8c056000 to 0x8c09c000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c09c000 to 0x8c09e000 not in this pool
Address 0x8c09e000 to 0x8c0b6000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c0b6000 to 0x8c0b8000 not in this pool
Address 0x8c0b8000 to 0x8c0d2000 in sga heap(1,3) (idx=1, dur=4)

以上可以看到该granule真的是一个shared granule共享内存颗粒,其中不仅包含了部分buffer block,还包含了1号shared subpool共享池子池的durtaion为4的chunk,duration=4即execution duration;这类duration的chunk一般有着较短的生命周期,当其extent被置于quiesce list静默列表时将很有可能变得足够free。execution duration是共享池中唯一能可靠转移的,因此唯有该类duration所在的extent(一般来说一个extent占用一个granule)可以用来收缩。

以下我们列出一些有助于诊断ASMM问题的动态性能视图,仅供参考:

V$SGAINFO
Displays summary information about the system global area (SGA).

V$SGA
Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.

V$SGASTAT
Displays detailed information about the SGA.

V$SGA_DYNAMIC_COMPONENTS
Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.

V$SGA_DYNAMIC_FREE_MEMORY
Displays information about the amount of SGA memory available for future dynamic SGA resize operations.

V$SGA_RESIZE_OPS
Displays information about the last 400 completed SGA resize operations.

V$SGA_CURRENT_RESIZE_OPS
Displays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.

V$SGA_TARGET_ADVICE
Displays information that helps you tune SGA_TARGET.

近期内会写一篇介绍shared pool duration的文章,作为对这篇的补充。

待修订!!

How does SGA/PGA allocate on AMM?

Oracle 11g中引入了革命性的Automatic Memory Management(AMM)特性,通过该特性DBA只需要为Instance指定一个参数(memory_target),数据库软件就会根据SGA/PGA内存的实际使用统计信息来调优SGA/PGA内存区域的大小。从技术上说这是一个很cool的特性,可以说是Oracle所提倡的self_tuned即自身调优数据库软件大成的一个标志。但从另一方面来看,AMM也会给我们带来不少问题和困惑,DBA需要面对更多黑盒内隐藏的秘密了。

虽然我们无法彻底了解AMM的所有细节,但有一些关键性问题肯定会在我们使用AMM的过程造成许多的不确定性,在此我列出部分问题及其解答。

Question1:在使用AMM特性时,即设置了memory_target的情况下sga/pga内存区域默认各占百分之多少?

Answer:
这个问题存在多种不同的情景:

1)设置memory_target的同时,设置了sga_target及pga_aggregate_target参数:这种情况下设置的sga_target与pga_aggregate_target之和不能大于memory_target;默认sga大小等同于sga_target设定的值,可以从V$memory_Dynamic_Components视图中查询到pga target的current size可能远大于pga_aggregate_target(这里查询出来的current size仅代表pga的一个目标值,不是pga当前实际占用的内存),一般来说这里的pga current size等于(memory_target-sga_target),显然pga target的最小值会是pga_aggregate_target,而sga的最小值为sga_target.在此前提下sga/pga内存区域有较小的灵活性,实际上仅当memory_target>sga_target+pga_aggregate_target的情况下,sga/pga才可能发生扩展(grow)和收缩(shrink)

2)设置memory_target的同时,设置了sga_target而未设置pga_aggregate_target参数:这种情况下Oracle会自动调优以上2个参数,pga_aggregate_target的初始值为(memory_target-sga_target),注意这里说的是初始值;这种情况下因为没有硬性地设置pga_aggregate_target,故而sga/pga内存区域有较大的灵活性去扩展(grow)和收缩(shrink)。更倾向于sga的扩展,当然这并不绝对。

3)设置memory_target的同时,设置了pga_aggregate_target而未设置sga_target:这种情况下Oracle会自动调优以上2个参数,sga_target的初始值为min(memory_target-pga_aggregate_target,sga_max_size),注意这里说的是初始值;类似于以上的情况因为没有硬性地设置sga_target,故而sga/pga内存区域有较大的灵活性去扩展(grow)和收缩(shrink)。更倾向于pga的扩展,当然这并不绝对。

4)仅仅设置了memory_target参数,Oracle会自动调优sga/pga内存区域,且没有任何最小值或默认值的约束。当在初始化分配操作系统内存的时候存在一个分配pga/sga的策略;该策略会在实例启动阶段(instance startup)为SGA分配memory_target 60%的内存(该比例受到隐藏参数_memory_initial_sga_split_perc Initial default sga target percentage with memory target的影响,该参数默认为60),而为PGA分配40%的内存。这种情况下sga/pga内存区域的游标最为灵活,可以不受限制地上下浮动,是真正意义上的AMM,但也最为危险!

5)没有设置memory_target参数,该情况下Oracle 11g的表现与10g中”一致”(并非完全一致,黑盒中藏了很多秘密,只是看上去这样,后面会提到)

Question2:”我在10g就感到ASMM很不好用,产生了很多问题;我是个保守派,对11g中的AMM一定也不感兴趣。我要彻底禁用AMM和ASMM,在11g中是否只要设置sga_target和memory_target参数为零就ok了?”

Answer:
在11.2.0.1之前的版本(包括11.1.0.7,11.1.0.6等)我们仅需要设置sga_target和memory_target参数为零就可以避免sga/pga内存区域的resize,但在Oracle 11g Release 2中引入了immediate memory allocation requests立即内存分配的特性,该特性会在automatic memory management被禁用的情况下始终生效,引入这一特性的直接目的是尽可能的避免ORA-04031错误的发生,一般来说该特性更多地表现出了其积极的一面,但如果你实际需要的是一块安宁的、如死水寂静的sga/pga内存区域的话,我们还是有办法禁用该immediate memory allocation requests特性的,这样可以彻底杜绝sga中内存组建resize的发生:

SQL> col DESCRIB for a60
SQL> set linesize 200 pagesize 2000
SQL> col name for a32
SQL> col value for a10
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
3   WHERE x.inst_id = USERENV ('Instance')
4   AND y.inst_id = USERENV ('Instance')
5   AND x.indx = y.indx
6  AND x.ksppinm LIKE '%_memory_imm_mode_without_autosga%';
NAME                             VALUE      DESCRIB
-------------------------------- ---------- ------------------------------------------------------------
_memory_imm_mode_without_autosga TRUE       Allow immediate mode without sga/memory target
SQL> alter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;
System altered.
/* 修改该参数需要重启实例 !*/

Question3:”我是一个激进派,我喜欢Oracle self-tuned自动调优的种种特性,为了充分利用AMM的优势,我决定仅设置memory_target参数,而不设置sga_target和pga_aggregate_target参数,让Oracle自行去调优这2个参数。但是我有担心如果pga扩展过头,是不是可能造成sga频繁shrink,造成系统颠簸甚至实例hang住?”

Answer:你的担心是完全有理由的,已知的文档604080.1指出了在11.1.0.6到11.1.0.7版本中存在AMM下未设置PGA_AGGREGATE_TARGET导致Oracle所使用的总内存超过MEMORY_MAX_TARGET的bug 6346293(When not setting PGA_AGGREGATE_TARGET to an explicit value, MEMORY_MAX_TARGET can be exceeded)。该Bug在11.2以上版本中得到了修复。

显然通过R1多个版本的历练后,Oracle充分认识到了AMM”自由调度”所可能带来的问题,我相信Oracle已经得到了某些内存阀值的黄金比例(Another magic number),当然这些幻数隐藏得很深(可能由某个_memory开头的隐藏参数控制着,但我没有兴致去研究,这需要花费太多的时间)。通过这些经典阀值Oracle可以有效控制pga/sga在不过度扩展或收缩的情况下仍保留其灵活性。

针对那些和我一样热爱新特性的朋友,最好的建议可能是在设置memory_target的同时为sga_target、pga_aggregate_target设置最小的期望值:

Memory_Max_Target>=Memory_Target>>(Sga_Target+Pga_Aggregate_Target)

这样可以避免由AMM过于活跃所造成的系统颠簸,而又不失去其灵活性。

to be continued………….

沪ICP备14014813号

沪公网安备 31010802001379号