Oracle的内存结构以及ORA-04030和ORA-04031错误
Tags： Buffer CacheDatabase Buffer CacheJava PoolJava池（Java Pool）Large PoolLibrary CacheORA-04030ORA-04031OraclePGARedo Log BufferRedo日志缓冲区（Redo Log Buffer）SGAShared PoolStreams PoolUGA体系结构保留池（Keep Pool）保留池（Reserved Pool）共享池内存大小评估内存结构回收池（Recycle Pool）大池（Large Pool）库缓存库缓存（Library Cache）数据字典缓存（Data Dictionary Cache）数据缓冲区流池（Streams Pool）程序全局区结果缓存（Result Cache）默认池（Default Pool）
简介
Oracle内存结构主要分共享内存区与非共享内存区，共享内存区主要包含SGA（System Global Area，系统全局区），非共享内存区主要由PGA（Program Global Area，程序全局区）组成，如下图所示：
SGA介绍
SGA（System Global Area，系统全局区）是Oracle实例的基本组成部分，是Oracle为一个实例分配的一组共享内存缓冲区，保存着Oracle系统与所有数据库用户的共享信息，包括数据维护、SQL语句分析、Redo日志管理等。SGA是实例的主要部分，它在实例启动时分配。
SGA是动态的，由参数SGA_MAX_SIZE决定。查看当前系统的SGA可以使用的最大内存大小的命令是：SHOW PARAMETER SGA_MAX_SIZE。修改SGA大小的命令是：ALTER SYSTEM SET SGA_MAX_SIZE=1200M SCOPE=SPFILE。因为实例内存的分配是在数据库启动时进行的，所以，要让修改生效，必须重启数据库。当Oracle运行在32位Linux上时，其默认SGA无法超过1.7GB。
在Oracle 10g中引入了ASMM（Automatic Shared Memory Management，自动共享内存管理），DBA只需设置SGA_TARGET，Oracle就会自动地对共享池、数据缓冲区、Redo日志缓冲区、大池、Java池和流池进行自动调配，取消自动调配的方法为设置SGA_TARGET为0。
需要注意的是，Oracle分配内存的单位是granule，即粒度。最小的粒度为4M，设置大小不到一个粒度按一个粒度计算。在32位操作系统的平台上，粒度的最大值为16M。粒度的大小在数据库实例周期内不能被修改。按照粒度为单位分配的组件包括：Shared Pool、Buffer Cache（以及不同大小块的Buffer Cache）、Redo Log Buffer、Java Pool、Streams Pool和Large Pool。粒度的大小参考下表：
通过视图V$SGAINFO可以查询当前SGA分配的粒度大小，如下所示：
1 2 3 4 | SYS@orclasm > SELECT * FROM V$SGAINFO WHERE NAME='Granule Size'; NAME BYTES RES -------------------------------- ---------- --- Granule Size 4194304 No |
下面将对SGA的各个组成部分进行介绍。
缓存了各用户间可共享的各种结构，例如，缓存最近被执行的SQL语句和最近被使用的数据定义。共享池主要包括：库缓存（Library Cache）、数据字典缓存（Data Dictionary Cache）、保留池（Reserved Pool）和结果缓存（Result Cache）。
库缓存（Library Cache）
库缓存（Library Cache）是存放用户SQL命令、解析树和执行计划的区域。对于库缓存来说，具体包含以下几个部分：
共享SQL区（Shared SQL Area）：保存了SQL语句文本，编译后的语法分析树及执行计划。查看共享SQL区的使用率命令为：
SELECT (SUM(PINS-RELOADS))/SUM(PINS) "LIBRARY CACHE" FROM V$LIBRARYCACHE;。私有SQL区（Private SQL Area）：包含当前会话的绑定信息以及运行时内存结构。每个发出SQL语句的会话，都有一个Private SQL Area。当多个用户执行相同的SQL语句，此SQL语句保存在共享SQL区。若是共享服务器模式，则Private SQL Area位于SGA的Share Pool或Large Pool中。若是专用服务器模式，则Private SQL Area位于PGA中。
共享PL/SQL区（Shared PL/SQL Area）：保存了分析与编译过的PL/SQL块（存储过程、函数、包、触发器和匿名PL/SQL块）。
控制结构区（Control Structure Area）：保存锁等控制信息。
数据字典缓存（Data Dictionary Cache）
数据字典缓存（Data Dictionary Cache）存放数据库运行的动态信息，例如，表和列的定义，数据字典表的权限。查看数据字典缓冲区命中率的SQL为：“SELECT ROUND((SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)*100,2) DATA_DICTIONARY_CACHE FROM V$ROWCACHE;”。
保留池（Reserved Pool）
保留池（Reserved Pool）也叫保留区域（Reserved Area），是指Shared Pool中配置的一个内存保留区域，这个保留区域用做当在普通的Shared Pool列表中的空间不能用来满足Large Request的内存分配请求而分配大块的连续内存块。当一个内存请求大于隐含参数“_SHARED_POOL_RESERVED_MIN_ALLOC”（默认：4400 bytes，如果系统经常出现ORA-04031错误，基本上都是请求大于4400的内存块，那么就可能需要增加SHARED_POOL_RESERVED_SIZE参数设置。）的值时就是一个Large Request，反之当内存请求小于“_SHARED_POOL_RESERVED_MIN_ALLOC”时就是一个Small Request。
另外关于Reserved Pool还有两个参数需要关注一下，一个是SHARED_POOL_RESERVED_SIZE，另外一个是隐含参数“_SHARED_POOL_RESERVED_PCT”（默认：5%）。通过SHARED_POOL_RESERVED_SIZE可以为Reserved Pool指定一个大小，也可以通过“_SHARED_POOL_RESERVED_PCT”来为Shared Pool指定一个比例。如果这两个参数同时设置了，那么就会以“_SHARED_POOL_RESERVED_PCT”为准。参数SHARED_POOL_RESERVED_SIZE的缺省值是SHARED_POOL_SIZE的5％，最小值为5000bytes，最大不得超过SHARED_POOL_SIZE的50％。
通过视图V$SHARED_POOL_RESERVED可以查到保留池的统计信息。其中字段REQUEST_MISSES记录了没有立即从空闲列表中得到可用的大内存段请求次数，这个值理想状态下要为0。当REQUEST_FAILURES大于0时，则需要增加SHARED_POOL_SIZE和SHARED_POOL_RESERVED_SIZE的空间。当REQUEST_MISS等于0，或是FREE_MEMORY大于等于SHARED_POOL_RESERVED_SIZE的空间时，则增加SHARED_POOL_RESERVED_SIZE的空间。MAX_USED_SPACE字段可以用来判断保留池的大小是否合适。保留区使用Shared Pool的LRU链表来管理内存块。可以通过如下的SQL语句来查询保留池的命中率（Hit Ratio），查询语句如下：
1 2 3 4 | SELECT (REQUEST_MISSES / (REQUESTS + 0.0001)) * 100 "REQUEST MISSES RATIO", (REQUEST_FAILURES / (REQUESTS + 0.0001)) * 100 "REQUEST FAILURES RATIO" FROM V$SHARED_POOL_RESERVED; |
以上结果应该都要小于1%，如果大于1，那么应该考虑加大SHARED_POOL_RESERVED_SIZE。
结果缓存（Result Cache）
结果缓存（Result Cache）是存放SQL查询结果和PL/SQL函数查询结果的区域。
共享池大小评估
共享池的大小由参数SHARED_POOL_SIZE决定。只要将初始化参数STATISTICS_LEVEL设置为TYPICAL（默认值）或ALL，就能启动对Shared Pool的建议功能，如果设置为BASIC，则关闭建议功能。使用如下的SQL语句可以查询到Oracle所建议的Shared Pool的大小：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | SYS@orclasm > SELECT SHARED_POOL_SIZE_FOR_ESTIMATE, 2 ESTD_LC_SIZE, 3 ESTD_LC_MEMORY_OBJECTS, 4 ESTD_LC_TIME_SAVED, 5 ESTD_LC_TIME_SAVED_FACTOR, 6 ESTD_LC_MEMORY_OBJECT_HITS 7 FROM V$SHARED_POOL_ADVICE; SHARED_POOL_SIZE_FOR_ESTIMATE ESTD_LC_SIZE ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_MEMORY_OBJECT_HITS ----------------------------- ------------ ---------------------- ------------------ ------------------------- -------------------------- 180 9 596 15816 .9874 244830 184 13 876 15879 .9913 247845 188 17 996 15910 .9933 249132 192 21 1191 15937 .9949 249697 196 25 1329 15944 .9954 250167 200 29 1447 16018 1 254285 204 33 1630 16033 1.0009 255345 208 37 1810 16041 1.0014 255949 212 40 1999 16068 1.0031 256701 216 43 2209 16069 1.0032 257237 220 46 2428 16083 1.0041 257685 224 50 2549 16088 1.0044 258030 228 53 2639 16089 1.0044 258234 232 57 2833 16092 1.0046 258457 236 61 2985 16095 1.0048 258802 240 65 3061 16097 1.0049 259258 260 85 3709 16118 1.0062 259946 280 104 4411 16142 1.0077 260564 300 124 5084 16157 1.0087 260859 320 143 5990 16166 1.0092 260956 340 162 7155 16174 1.0097 260999 360 162 7155 16174 1.0097 261001 380 162 7155 16174 1.0097 261001 400 162 7155 16174 1.0097 261001 |
第一列表示Oracle所估计的Shared Pool的尺寸值，其他列表示在该估计的Shared Pool大小下所表现出来的指标值。可以主要关注ESTD_LC_TIME_SAVED_FACTOR列的值，当该列值为1时，表示再增加Shared Pool的大小对性能的提高没有意义。对于上例来说，当Shared Pool为200MB时，达到最佳大小。对于设置比200MB更大的Shared Pool来说，就是浪费空间，没有更多的好处了。
（2）数据缓冲区（Database Buffer Cache）
也叫数据库缓冲区高速缓存，用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能，是数据库实例的重要组成部分。参数DB_CACHE_SIZE可指定数据缓冲区的大小，需要在参数文件中静态修改。Oracle在处理某个查询时，服务器进程会在Buffer Cache中查找它所需的所有数据块。如果未在Buffer Cache中找到所需要的数据块，那么服务器进程会从数据文件中读取所需的数据块，并在Buffer Cache中添加一个副本。因为关于同一数据块的后续请求可能会在内存中找到该数据块，因此，这些请求可能不需要进行物理读操作。Buffer Cache中的内存块有4种状态：
① Pinned：当前块正在被某个进程读取到Cache或正写到磁盘，即当前正在被访问的数据块，可防止多个会话同时对同一数据块进行写操作。此时，其他会话正等待访问该块。
② Clean：服务器进程从数据文件中读入的Block且还没有被其它进程所修改或者后台进程DBWn将Dirty Buffer写入到数据文件中的Buffer，该Buffer中的内容与数据文件中的Block一致。该状态的数据块是可以立即被移出的候选数据块。
③ Free/Unused：Buffer内为空，为实例刚启动时的状态。Buffer Cache初始化时或者在执行alter system flush buffer_cache以后的Buffer，该Buffer中没有存放任何内容。此状态与“clean”状态非常相似，不同之处在于“free/unused”状态的缓冲区尚未使用。
④ Dirty：脏数据，数据块已被修改，需要先被DBWn刷新到磁盘，才能执行过期处理（移出缓冲区）。在该状态下，该Buffer的内容与数据文件中Block的内容不一致。
图 3-6 Database Buffer Cache中数据块的状态转变
数据库高速缓冲区的主要功能是用来暂时存放最近读取自数据库中的数据，也就是数据文件（Data File）内的数据，而数据文件是以数据块（Block）为单位，因此，数据库高速缓冲区中的大小是以块为基数。当用户通过应用程序第一次向Oracle数据库发出查询请求时，Oracle会先在Buffer Cache内寻找该数据，如果有该请求所需要的数据，那么就直接从Buffer Cache传回给用户，这称为缓存命中（Cache Hit），这样就可以减少硬盘上的I/O次数。如果Oracle发现用户要的数据并不在Buffer Cache里，就称为缓存失误（Cache Miss），Oracle会从数据库中读取所需要的数据块，先放入Buffer Cache中，再传送给用户。该区域内的数据块通过LRU（Least Recently Used，最近最少使用）算法管理。LRU将Buffer Cache中的所有的Clean和Free状态的Buffer按照它们被读取的频率连接起来。（冷端：最少使用的；热端：最常被使用的；在服务器进程将磁盘的Block读取到Buffer Cache时，会先覆盖冷端的Buffer。）。
Buffer Cache可以分为多个缓冲池：
① 回收池（Recycle Pool）：放到回收池中的数据，只要空间不够用，它们马上就会被释放出来，即回收池中的数据会最先被替换出内存，很少使用的数据放在该区。被放在回收池中的数据块不会被反复使用。也就是说，这些数据块只在事务（Transaction）还存在时才会被用到，一旦事务结束，就会被释放出来。回收池的大小最好是默认池的1/2，通过DB_RECYCLE_CACHE_SIZE参数指定回收池的大小。该缓存不参与ASMM的动态管理，不能自动调整大小。默认未启用，大小为0。手工修改指定值后，Default Pool的空间将被相应的减少。
② 保留池（Keep Pool）：当数据被放到保留池里时，就代表这个数据是需要常常被重复使用的。保留池中的数据不会被替换出去，可以将常用的小表放置在该区可以降低I/O操作。可以通过DB_KEEP_CACHE_SIZE参数指定保留池的大小。该区域的大小不会被ASMM自动调节。默认未启用，大小为0，当手工修改指定该值后，Default Pool的空间将被相应的减少。
③ 默认池（Default Pool）：当没有指定对象存储的缓冲池时，数据就会放在默认池中，相当于一个没有Keep与Recycle池的实例的Buffer Cache。也就是说，放在默认池的数据利用的是LRU机制。通过DB_CACHE_SIZE参数指定默认池的大小。
BUFFER_POOL子句可以在对象的STORAGE子句中为对象指定使用具体的Buffer Pool。如果现有对象没有明确指定Buffer Pool，那么默认都指定为Default Buffer Pool。可以使用CREATE或ALTER语句指定对象存储的缓冲池：
1 2 3 4 5 | CREATE INDEX CUST_IDX ON TT(ID) STORAGE (BUFFER_POOL KEEP); ALTER TABLE OE.CUSTOMERS STORAGE (BUFFER_POOL RECYCLE); ALTER INDEX OE.CUST_LNAME_IX STORAGE (BUFFER_POOL KEEP); |
在同一个数据库中，支持多种大小的数据块缓存。通过DB_nK_CACHE_SIZE参数指定，如：
DB_CACHE_SIZE（指定标准块（这里为8K）的缓存区）
DB_2K_CACHE_SIZE（指定块大小为2K的缓存区）
DB_4K_CACHE_SIZE（指定块大小为4K的缓存区）
DB_16K_CACHE_SIZE（指定块大小为16K的缓存区）
DB_32K_CACHE_SIZE（指定块大小为32K的缓存区）
标准块缓冲区大小由DB_CACHE_SIZE指定。如标准块为nK，则不能通过DB_nK_CACHE_SIZE来指定标准块缓冲区的大小，应由DB_CACHE_SIZE指定。
当数据库高速缓冲区需要读取或写回数据到数据文件中时，都需要通过DBWn这个后台进程来协助处理，而参数DB_WRITER_PROCESSES主要设置要由几个DBWn来协助处理。在此建议不要超过系统CPU的个数，如果设置的值超过了CPU的个数，那么超过的那些是无法起作用的。
当参数DB_CACHE_ADVICE设置为ON（当STATISTICS_LEVEL为TYPICAL或ALL时，DB_CACHE_ADVICE参数值默认为ON）时，表示开启DB_CACHE_ADVICE功能。当开启参数DB_CACHE_ADVICE后，经过一段时间，Oracle就会自动收集足够的相关统计数据，并预测出DB_CACHE_SIZE在不同大小情况下的性能数据，而这些数据就是通过V$DB_CACHE_ADVICE视图来显示的，因此，可以根据这些数据对DB_CACHE_SIZE做相关的调整，以达到最佳情况。
对视图V$DB_CACHE_ADVICE的各列介绍如下表所示：
| 字段名 | 数据类型 | 说明 |
|---|---|---|
| ID | NUMBER | 不同数据库高速缓冲区的编号，一般来说，DB_CACHE_SIZE的编号是3 |
| NAME | VARCHAR2(20) | 数据库高速缓冲区的名称(Default、Keep、Recycle) |
| BLOCK_SIZE | NUMBER | 数据块的大小(单位是K) |
| ADVICE_STATUS | VARCHAR2(3) | 开启状态：ON代表开启，OFF代表关闭 |
| SIZE_FOR_ESTIMATE | NUMBER | 预测性能的Cache大小(以M为单位) |
| SIZE_FACTOR | NUMBER | 预测的Cache大小比例，也就是与目前大小的比例 |
| BUFFERS_FOR_ESTIMATE | NUMBER | 预测性能数据的数据块个数 |
| ESTD_PHYSICAL_READ_FACTOR | NUMBER | 在数据库高速缓冲区里物理读取的因子，也就是说，当数据库高速缓冲区大小为SIZE_FOR_ESTIMATE此字段时，DB_CACHE_ADVICE预测的物理读数与当前物理读数的比率值。如果当前物理读数为0，那么，这个值为空 |
| ESTD_PHYSICAL_READS | NUMBER | 当数据库高速缓冲区大小为SIZE_FOR_ESTIMATE时，DB_CACHE_ADVICE预测的实际读数 |
| ESTD_PHYSICAL_READ_TIME | NUMBER | 当前物理读取的时间 |
| ESTD_PCT_OF_DB_TIME_FOR_READS | NUMBER | 当前物理读取的时间占所有时间的比例 |
查询视图V$DB_CACHE_ADVICE如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | SYS@orclasm > SELECT ADVICE_STATUS, 2 SIZE_FOR_ESTIMATE, 3 ESTD_PHYSICAL_READ_FACTOR, 4 ESTD_PHYSICAL_READS 5 FROM V$DB_CACHE_ADVICE 6 WHERE NAME = 'DEFAULT'; ADV SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS --- ----------------- ------------------------- ------------------- ON 4 1.028 4028357 ON 8 1.0223 4006098 ON 12 1.0178 3988551 ON 16 1.0109 3961532 ON 20 1.0068 3945138 ON 24 1.0052 3939208 ON 28 1.0045 3936105 ON 32 1.004 3934171 ON 36 1.0033 3931620 ON 40 1.0023 3927591 ON 44 1.0013 3923578 ON 48 1 3918655 ON 52 .9992 3915665 ON 56 .9988 3913960 ON 60 .9984 3912432 ON 64 .9982 3911506 ON 68 .998 3910840 ON 72 .9979 3910450 ON 76 .9978 3910125 ON 80 .9925 3889182 |
由以上结果可以知道，当ESTD_PHYSICAL_READ_FACTOR为1时，最佳的DB_BUFFER_SIZE是48，因为在这之后再进行调整对降低I/O的影响有限，所以该系统最佳的Buffer Cache的大小为48M。
（3）Redo日志缓冲区（Redo Log Buffer）
对数据库进行修改的任何事务（Transaction）在记录到Redo日志文件之前都必须首先放到Redo日志缓冲区中。Redo日志缓冲区中的内容将被后台进程LGWR写入联机Redo日志文件（Online Redo Log Files）中。Redo日志缓冲区是一个循环缓存区，在使用时从顶端向底端写入数据，然后再返回到缓冲区的起始点循环写入。Oracle中所有的DML和DDL操作都会记录日志，即便没有提交的DML操作也会记录日志，在指定了NOLOGGING时，也会记录一些日志。Redo日志缓冲区大小由参数LOG_BUFFER决定，需要在参数文件中静态修改。服务器进程（Server Process）及后台进程（Background Process）对Oracle的变更记录会写到Redo日志缓冲区，这些变更的数据都在内存中的Redo日志缓冲区中以Redo Entry（重做条目，也可称为Redo Record）的方式存储。Redo Entry是Oracle从用户会话占用的内存里将这些变更的记录复制到Redo日志缓冲区内，其在内存中是一段连续的内存块，Oracle利用后台进程LGWR在适当的时机将Redo日志缓冲区中的信息（也就是Redo Entry）写回到联机Redo日志文件内，以便万一数据库崩溃，可以进行必要的恢复。后台进程LGWR将Redo Entry写回到联机Redo日志文件的时机如下：
① 用户发出提交命令（COMMIT）
② 每隔3秒
③ Redo日志缓冲区空间剩余不到2/3
④ Redo日志缓冲区内的数据达到1MB
⑤ 在发生联机Redo日志切换（Log Switch）时
⑥ 在DBWn进程将修改的缓冲区写入磁盘时（如果相应的Redo日志数据尚未写入磁盘）
（4）大池（Large Pool）
SGA中一个可选的内存区域，大池用来分配大块的内存，处理比共享池更大的内存，用来缓解Shared Pool的负担。
大池主要用在3种情况下，
①若是共享服务器模式时，则在Large Pool中分配UGA，若Large Pool没有分配则在Shared Pool中分配。若是专用服务器（多线程服务器MTS，Multi-Threaded Server）连接，则UGA在PGA中创建；
②语句的并行查询（Parallel Executeion of Statements），允许进程间消息缓冲区的分配，用来协调并行查询服务器；
③恢复管理器RMAN，用于RMAN磁盘I/O缓冲区。
大池的大小由参数LARGE_POOL_SIZE决定，可以动态修改。大池也使用共享池的闩锁机制，但和共享池不同的是，大池并不使用LRU机制，而是使用Large Memory Latch的保护，因此，大池中缓冲区内的数据不会被置换出来。大池内的数据会利用用户的会话来控制分配和释放大池的空间。如果大池的空间不足，那么也会出现ORA-04031错误。
（5）Java池（Java Pool）
为Java命令的语法分析提供服务。Java池也是SGA中的一块可选内存块，大小由参数JAVA_POOL_SIZE决定。在Oracle 10g以后，提供了一个新的Java池的建议功能，以辅助调整Java池的大小，而建议的统计数据可以通过视图V$JAVA_POOL_ADVICE来查询。
（6）流池（Streams Pool）
被Oracle流所使用，主要提供专门的Streams复制功能，流池是可选用内存块，它也属于SGA中的可变区域。参数STREAMS_POOL_SIZE可以指定流池的大小。如果设置为0，那么当第一次使用Streams复制功能时，Oracle会自动建立此块区域，而自动建立的大小为共享池大小的10%。Oracle也提供了一个流池的建议功能，来协助调整流池的大小，而建议的统计数据可以通过视图V$STREAMS_POOL_ADVICE来查询。
PGA介绍
PGA（Program Global Area，程序全局区）是单个Oracle进程使用的内存区域，为每个连接到Oracle数据库的用户进程保留的内存，不属于实例的内存结构。它含有单个进程工作时需要的数据和控制信息。PGA是非共享的，只有服务进程本身才能够访问它自己的PGA区。PGA在进程创建时分配，进程结束时释放。PGA的内容随服务器的模式（专用模式/共享服务器模式）不同而不同。PGA的大小由参数PGA_AGGREGATE_TARGET决定，可动态修改。
图 3-7 PGA结构图
PGA有如下几个组件：
① Private SQL Area（私有SQL区）：参考Shared Pool部分的介绍。
② Cursor and SQL Areas（游标和SQL区）：Oracle Pro*C程序（Pro*C是Oracle提供的应用程序专用开发工具，它以C语言为宿主语言，能在C程序中嵌入SQL语句，进行数据库操作。）的应用程序开发人员或Oracle调用接口（Oracle Call Interface，OCI）程序可以显式打开游标或处理私有SQL区。
③ Session Memory（会话内存）：保存会话的变量（例如，登录信息）及其他与会话相关的信息。在共享服务器模式下，Session Memory是共享的。
④ Work Area（工作区）：PGA的一大部分被分配给Work Area，用来执行如下操作：
基于排序的操作，GROUP BY、ORDER BY、ROLLUP和窗口函数。由于排序需要内存空间，Oracle利用该内存排序数据，这部分空间称为排序区。排序区存在于请求排序的用户进程的内存中，该空间的大小为适应排序数据量的大小，可增长，但受参数SORT_AREA_SIZE所限制。
HASH连接，大小受参数HASH_AREA_SIZE所限制
位图合并，大小受参数BITMAP_MERGE_AREA_SIZE所限制
位图创建，大小受参数CREATE_BITMAP_AREA_SIZE所限制
批量装载操作使用的写缓存
PGA和SGA最明显的差别在于，PGA不是共享内存，是私有不共享的。用户对数据库发起的无论查询还是更新的任何操作，都是在PGA先预处理，然后接下来才进入实例区域，由SGA和系列后台进程共同完成用户发起的请求。PGA起到的具体作用主要有三点：第一，保存用户的连接信息，如会话属性、绑定变量等；第二，保存用户权限等重要信息，当用户进程与数据库建立会话时，系统会将这个用户的相关权限查询出来，然后保存在这个会话区内；第三，当发起的指令需要排序的时候，PGA正是这个排序区，如果在内存中可以放下排序的尺寸，就在内存PGA区内完成，如果放不下，超出的部分就在临时表空间中完成排序，也就是在磁盘中完成排序。
自动PGA内存管理（Automatic PGA Memory Management）
从Oracle9i开始，Oracle引入了PGA自动管理的特性。若设置参数PGA_AGGREGATE_TARGET为非0，则表示启用PGA内存自动管理，并忽略所有*_AREA_SIZE的设置，例如SORT_AREA_SIZE、HASH_AREA_SIZE等。默认为启用PGA的自动管理，Oracle根据SGA的20%来动态调整PGA中专用于Work Area部分的内存大小，最小为10MB。
设置WORKAREA_SIZE_POLICY参数，可以在PGA自动（AUTO，默认是AUTO）和PGA手动管理（MANUAL）之间进行选择，然后通过设置初始化参数PGA_AGGREGATE_SIZE来设置PGA的内存总和。如果设置参数WORKAREA_SIZE_POLICY为MANUAL（默认值是AUTO），就代表此数据库的PGA管理模式属于手动管理模式，且在此模式下必须设置SORT_AREA_SIZE、HASH_AREA_SIZE等相关参数。需要注意的是，在Oracle 9i时，PGA自动管理只对Dedicate Server有效，对Shared Server无效，但是从Oracle 10g开始，PGA自动管理都有效。
对于OLTP系统，典型的PGA内存为：
PGA_AGGREGATE_SIZE = (total_memory 80%) 20%
对于OLAP系统，由于会运行一些很大的查询：
PGA_AGGREATE_SIZE = (total_memoery 80%) 50%
80%是指，将机器总内存的80%分给Oracle使用。然后再将80%中的20%给PGA。
可以使用PGA相关的一些视图来调整PGA_AGGREGATE_SIZE的大小，例如：V$PGASTAT、V$PGA_TARGET_ADVICE、V$PGA_TARGET_ADVICE_HISTOGRAM等。当自动PGA内存管理功能打开后，可以从V$PGA_TARGET_ADVICE中得到相关的指导数据，进而评估PGA_AGGREGATE_TARGE是否需要调整。该视图的ESTD_OVERALLOC_COUNT列表示需要额外分配的PGA内存，如果此数值不是0，就表示PGA_AGGREGATE_TARGE设置得太小，需要调整。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> SELECT PGA_TARGET_FOR_ESTIMATE / 1024 / 1024 PGAMB, 2 PGA_TARGET_FACTOR P_TR_FCT, 3 ESTD_PGA_CACHE_HIT_PERCENTAGE E_P_C_HIT_PRCT, 4 ESTD_OVERALLOC_COUNT E_OR_CNT 5 FROM V$PGA_TARGET_ADVICE; PGAMB P_TR_FCT E_P_C_HIT_PRCT E_OR_CNT ---------- ---------- -------------- ---------- 23.75 .125 82 1179 47.5 .25 82 1179 95 .5 95 233 142.5 .75 99 6 190 1 99 1 228 1.2 99 0 266 1.4 99 0 304 1.6 99 0 342 1.8 99 0 380 2 99 0 570 3 99 0 760 4 99 0 1140 6 99 0 1520 8 99 0 |
从上面的查询中可以看出当设置PGA的大小为228MB时，可以消除PGA过载的情形。
UGA介绍
UGA（User Global Area）保存了会话信息，会话总能访问这部分内存。UGA的位置取决于会话连接到Oracle的方式。如果是专用服务器连接，那么UGA在PGA中创建；如果是共享服务器连接，那么UGA在SGA的Large Pool中创建，若Large Pool没有分配则在Shared Pool中分配。
PGA和UGA两者间的区别跟一个进程和一个会话之间的区别是类似的。尽管说进程和会话之间一般都是一对一的关系，但实际上比这个更复杂。一个很明显的情况是MTS配置，会话往往会比进程多得多。在这种配置下，每一个进程会有一个PGA，每一个会话会有一个UGA。PGA所包含的信息跟会话是无任何关联的，而UGA包含的信息是以特定的会话为基础的。
SHOW SGA和V$SGA的结果区别
SHOW SGA的结果比V$SGA的结果多一行“Total System Global Area”数据。其实，SHOW SGA的结果来源于V$SGA视图。运行命令“vi $ORACLE_HOME/bin/sqlplus”打开sqlplus文件，匹配SGA可以发现这么一行代码：
1 2 3 4 5 6 7 8 9 10 | SELECT DECODE(NULL, '', 'Total System Global Area', '') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA, VALUE, DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA FROM V$SGA; |
该行代码的结果和执行SHOW SGA可以得到一样的结果，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SYS@omflhr> SHOW SGA Total System Global Area 1068937216 bytes Fixed Size 2253216 bytes Variable Size 771755616 bytes Database Buffers 289406976 bytes Redo Buffers 5521408 bytes SYS@omflhr> SELECT DECODE(NULL, '', 'Total System Global Area', '') NAME_COL_PLUS_SHOW_SGA, 2 SUM(VALUE), 3 DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA 4 FROM V$SGA 5 UNION ALL 6 SELECT NAME NAME_COL_PLUS_SHOW_SGA, 7 VALUE, 8 DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA 9 FROM V$SGA; NAME_COL_PLUS_SHOW_SGA SUM(VALUE) UNITS_COL_PLUS_ ------------------------ ---------- --------------- Total System Global Area 1068937216 bytes Fixed Size 2253216 bytes Variable Size 771755616 bytes Database Buffers 289406976 bytes Redo Buffers 5521408 bytes |
在以上结果中，各部分的含义如下：
Total System Global Area：显示目前此SGA的大小，包括Fixed Size、Variable Size、 Database buffers和Redo Buffers的大小总和。
Fixed Size：这里存储了SGA各部分组件的相关信息，主要是作为引导SGA创建的区域，Oracle通过这个区找到SGA其它区，类似一个SGA各个组件的索引。这部分是Oracle内部使用的一个区，包括了数据库与实例的控制信息、状态信息、字典信息等。当实例被打开时，此块区域就被固定住而不能做任何变动，此区域也可称为Fixed SGA。不同平台和不同版本下这部分的大小可能不一样。
Variable Size：包括Shared Pool、Java Pool、Large Pool、Streams Pool、游标区和其它结构。由于这些内存块都是可动态分配的，所以统称为Variable Size。
Database Buffers：显示数据库高速缓冲区的大小，是SGA中最大的地方，决定数据库性能。为DB_CACHE_SIZE、DB_KEEP_CACHE_SIZE、DB_RECYCLE_CACHE_SIZE、DB_NK_CACHE_SIZE的总大小，当然这是SGA_TARGET为0的情况，也就是手动SGA管理模式下，如果是自动SGA管理（SGA_TARGET>0），则这个值根据SGA的分配情况自动进行调整。
Redo Buffers：显示Redo日志缓冲区的大小，这部分是实际分配的Redo Log Buffer的大小，由初始化参数LOG_BUFFER根据SGA的最小分配单位granule向上取整得到。
和内存相关的比较有用的视图
V$SGASTAT
V$SGASTAT主要记录了有关SGA的统计信息，以及内存分配的情况，对于发生ORA-04031错误有很重要的参考价值。其中的信息由三个字段组成，依序是：Name（SGA内存块的名称）、Bytes（内存块的大小）、Pool（内存所属的内存块）。
以下SQL可以查询SGA每个组件真实的分配大小和剩余空间（重要）：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT POOL, NAME, ROUND(BYTES / 1024 / 1024,2) MB FROM V$SGASTAT order by pool,name, BYTES desc; SELECT POOL, ROUND(sum(BYTES) / 1024 / 1024,2) SIZE_MB,sum(CASE WHEN NAME = 'free memory' THEN ROUND((BYTES) / 1024 / 1024,2) else 0 END) AS FREE FROM V$SGASTAT A WHERE A.POOL IS NOT NULL group by POOL UNION ALL SELECT A.NAME, ROUND(BYTES / 1024 / 1024,2) SIZE_MB,0 FROM V$SGASTAT A WHERE A.POOL IS NULL UNION ALL SELECT 'ALL:', ROUND(SUM(BYTES) / 1024 / 1024,2) SIZE_MB,sum(CASE WHEN NAME = 'free memory' THEN ROUND((BYTES) / 1024 / 1024,2) else 0 END) AS FREE FROM V$SGASTAT A order by SIZE_MB desc; |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | SQL> SELECT POOL, ROUND(sum(BYTES) / 1024 / 1024,2) SIZE_MB,sum(CASE WHEN NAME = 'free memory' THEN ROUND((BYTES) / 1024 / 1024,2) else 0 END) AS FREE 2 FROM V$SGASTAT A 3 WHERE A.POOL IS NOT NULL 4 group by POOL UNION ALL 6 SELECT A.NAME, ROUND(BYTES / 1024 / 1024,2) SIZE_MB,0 7 FROM V$SGASTAT A WHERE A.POOL IS NULL 9 UNION ALL 10 SELECT 'ALL:', ROUND(SUM(BYTES) / 1024 / 1024,2) SIZE_MB,sum(CASE WHEN NAME = 'free memory' THEN ROUND((BYTES) / 1024 / 1024,2) else 0 END) AS FREE 11 FROM V$SGASTAT A 12 order by SIZE_MB desc; POOL SIZE_MB FREE -------------------------- ---------- ---------- ALL: 122885.87 14035.05 buffer_cache 76800 0 shared pool 43050.45 11261.49 java pool 1536 1501.42 large pool 768 760.19 streams pool 512 511.95 log_buffer 217.25 0 fixed_sga 2.16 0 8 rows selected. SQL> SQL> SELECT POOL, NAME, ROUND(BYTES / 1024 / 1024,2) MB 2 FROM V$SGASTAT order by pool,name, BYTES desc; POOL NAME MB ------------ -------------------------- ---------- java pool JOXLE 33.31 java pool free memory 1501.42 java pool joxs heap 1.27 large pool PX msg pool 7.81 large pool free memory 760.19 shared pool ASH buffers 48 shared pool Checkpoint queue 4.7 shared pool DML lock 4.14 shared pool FileIdentificatonBlock .73 shared pool FileOpenBlock 14.22 shared pool JOXLE 4.32 shared pool JOXLS 0 shared pool KCB Table Scan Buffer 4 shared pool KEWS sesstat values 1.13 shared pool KGI Session State 0 shared pool KGLA .18 shared pool KGLDA 422.01 shared pool KGLH0 8743.86 shared pool KGLHD 2099.61 shared pool KGLNA 104.54 shared pool KGLS 118.12 shared pool KGLSG 5.02 shared pool KGNFS scontext .5 shared pool KKBTD .6 shared pool KKSSP 2.71 shared pool KKTDF 0 shared pool KOKTD 3.63 shared pool KQR L PO 499.64 shared pool KQR L SO 11.8 shared pool KQR M PO 39.75 shared pool KQR M SO 6.94 shared pool KSFD SGA I/O b 4 shared pool KSK VT POOL 2.48 shared pool KSKQ SGA NODEINFO 1.3 shared pool KTI-UNDO 13.44 shared pool KUPP subheap 1 shared pool PLDIA 1.78 shared pool PLMCD 14.51 shared pool PRTDS .02 shared pool PRTMV 0 shared pool PX subheap 1 shared pool Result Cache 1.05 shared pool SQLA 15450.47 shared pool SQLK .01 shared pool SQLP 2.7 shared pool Temporary Tables State Ob .56 shared pool VIRTUAL CIRCUITS 6 shared pool Wait History Segment 1.81 shared pool XDBSC 14.07 shared pool base-kglhdusr 656.53 shared pool branch .74 shared pool buffer handles 4.03 shared pool constraints .56 shared pool db_block_hash_buckets 712 shared pool dbktb: trace buffer 409.59 shared pool dbwriter coalesce buffer 12.01 shared pool dirty object counts array 12 shared pool enqueue 4.91 shared pool enqueue resources 1.61 shared pool event statistics per sess 22.88 shared pool file state object 8.17 shared pool free memory 11262.69 shared pool kdlwss 0 shared pool keswx:plan en .95 shared pool kglhdusr .67 shared pool kglsim hash table bkts 4 shared pool kglsim heap 624.05 shared pool kglsim object batch 1130.77 shared pool kksLoadBaseParentOnLock:b 63.32 shared pool kkslBind_value 83.89 shared pool kkslLoadParentOnLock:lite 235.43 shared pool krdrsb read violation arr .53 shared pool ksunfy : SSO free list 21.76 shared pool ktlbk state objects 2.07 shared pool lock state hash table .62 shared pool miscellaneous .27 shared pool modification 1.16 shared pool object queue 18.14 shared pool object queue hash buckets 3.38 shared pool parameter handle .01 shared pool parameter table block .11 shared pool parameter value memory 0 shared pool private strands 25.98 shared pool prmtzdini tz region .76 shared pool procs: ksunfy 4.9 shared pool row cache 8.23 shared pool simulator hash buckets 32.06 shared pool state objects 3.13 shared pool sys event stats .63 shared pool temporary tabl .02 shared pool transaction 4.9 shared pool write state object 14.64 streams pool fixed allocation callback 0 streams pool free memory 511.95 streams pool image handles .01 streams pool kgqmdm_fl_1 0 streams pool kwqbsinfy:bms .01 streams pool kwqbsinfy:bqg 0 streams pool kwqbsinfy:cco 0 streams pool kwqbsinfy:mpr .03 streams pool kwqbsinfy:sta 0 streams pool spilled:kwqbl 0 buffer_cache 76800 fixed_sga 2.16 log_buffer 217.25 105 rows selected. |
以下的语句可查询内存块还剩余多少使用空间。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT POOL, NAME, ROUND(BYTES / 1024 / 1024,2) MB FROM V$SGASTAT WHERE NAME = 'free memory'; SQL> SELECT POOL, NAME, ROUND(BYTES / 1024 / 1024,2) MB 2 FROM V$SGASTAT 3 WHERE NAME = 'free memory'; POOL NAME MB ------------ -------------------------- ---------- shared pool free memory 11228.52 large pool free memory 760.19 java pool free memory 1501.42 streams pool free memory 511.95 SQL> |
以下的语句可查询共享池的空闲空间比率。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT TO_NUMBER(B.VALUE/1024/1024) VALUE_MB, A.BYTES / 1024 / 1024 FREE_SIZE_MB, (A.BYTES / B.VALUE) * 100 PERCENT_FREE FROM V$SGASTAT A, V$PARAMETER B WHERE A.NAME = 'free memory' AND B.NAME = 'shared_pool_size' AND A.POOL = 'shared pool'; SQL> SELECT TO_NUMBER(B.VALUE/1024/1024) VALUE_MB, 2 A.BYTES / 1024 / 1024 FREE_SIZE_MB, 3 (A.BYTES / B.VALUE) * 100 PERCENT_FREE 4 FROM V$SGASTAT A, V$PARAMETER B WHERE A.NAME = 'free memory' 6 AND B.NAME = 'shared_pool_size' 7 AND A.POOL = 'shared pool'; VALUE_MB FREE_SIZE_MB PERCENT_FREE ---------- ------------ ------------ 40960 11216.8381 27.3848586 |
V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_COMPONENTS记录了SGA内各个组件的情况，包括SGA的各个内存块的放大和缩小情况，且所提供的信息是经过统计的，其字段组成如下表所示：
| 字段名 | 数据类型 | 说明 |
|---|---|---|
| COMPONENT | VARCHAR2(64) | 内存块名称 |
| CURRENT_SIZE | NUMBER | 目前的大小 |
| MIN_SIZE | NUMBER | 实例启动后的最小值 |
| MAX_SIZE | NUMBER | 实例启动后的最大值 |
| OPER_COUNT | NUMBER | 实例启动后的调整次数 |
| LAST_OPER_TYPE | VARCHAR2(6) | 记录最后一次的调整动作，值包括：GROW（增加），SHRINK（缩小） |
| LAST_OPER_MODE | VARCHAR2(6) | 最后一次完成调整动作的模式有两种：MANUAL（手动）和AUTO（手动） |
| LAST_OPER_TIME | DATE | 最后一次完成的调整动作的开始时间 |
| GRANULE_SIZE | NUMBER | 内存粒度大小 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | COL COMPONENT FORMAT A30 COL LAST_OPER_TIME FORMAT A20 select COMPONENT, trunc(CURRENT_SIZE / 1024 / 1024) CURRENT_SIZE_MB, trunc(MIN_SIZE / 1024 / 1024) MIN_SIZE_MB, trunc(MAX_SIZE / 1024 / 1024) MAX_SIZE_MB, trunc(USER_SPECIFIED_SIZE / 1024 / 1024) USER_SPECIFIED_SIZE_MB, OPER_COUNT, LAST_OPER_TYPE, LAST_OPER_MODE, TO_CHAR(LAST_OPER_TIME,'YYYY-MM-DD HH24:MI:SS') LAST_OPER_TIME, trunc(GRANULE_SIZE / 1024 / 1024) GRANULE_SIZE_MB from V$SGA_DYNAMIC_COMPONENTS; SQL> COL COMPONENT FORMAT A30 SQL> COL LAST_OPER_TIME FORMAT A20 SQL> select COMPONENT, 2 trunc(CURRENT_SIZE / 1024 / 1024) CURRENT_SIZE_MB, 3 trunc(MIN_SIZE / 1024 / 1024) MIN_SIZE_MB, trunc(MAX_SIZE / 1024 / 1024) MAX_SIZE_MB, 5 trunc(USER_SPECIFIED_SIZE / 1024 / 1024) USER_SPECIFIED_SIZE_MB, 6 OPER_COUNT, 7 LAST_OPER_TYPE, 8 LAST_OPER_MODE, TO_CHAR(LAST_OPER_TIME,'YYYY-MM-DD HH24:MI:SS') LAST_OPER_TIME, 10 trunc(GRANULE_SIZE / 1024 / 1024) GRANULE_SIZE_MB 11 from V$SGA_DYNAMIC_COMPONENTS; COMPONENT CURRENT_SIZE_MB MIN_SIZE_MB MAX_SIZE_MB USER_SPECIFIED_SIZE_MB OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE_MB ------------------------------ --------------- ----------- ----------- ---------------------- ---------- ------------- --------- -------------------- --------------- shared pool 42496 40960 42496 40960 6 GROW IMMEDIATE 2022-07-26 11:08:37 256 large pool 768 768 768 0 0 STATIC 256 java pool 1536 1536 1536 0 0 STATIC 256 streams pool 512 0 512 0 2 GROW IMMEDIATE 2022-06-28 02:00:03 256 DEFAULT buffer cache 76800 76800 78848 71680 8 SHRINK IMMEDIATE 2022-07-26 11:08:37 256 KEEP buffer cache 0 0 0 0 0 STATIC 256 RECYCLE buffer cache 0 0 0 0 0 STATIC 256 DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 256 DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 256 DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 256 DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 256 DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 256 Shared IO Pool 0 0 0 0 0 STATIC 256 ASM Buffer Cache 0 0 0 71680 0 STATIC 256 14 rows selected. |
V$LIBRARYCACHE
V$LIBRARYCACHE的内容包含了关于库缓存的性能统计信息，对于共享池的性能优化有很大帮助。V$LIBRARYCACHE的结构如下表所示。
| 字段名 | 数据类型 | 说明 |
|---|---|---|
| NAMESPACE | VARCHAR2(15) | 库缓存的命名空间，例如INDEX、SQL AREA、OBJECT等 |
| GETS | NUMBER | 得到该对象的次数 |
| GETHITS | NUMBER | 在内存中得到该对象的次数，也就是锁命中的次数 |
| GETHITRATIO | NUMBER | 得到该对象的命中率 |
| PINS | NUMBER | 要求PIN的次数 |
| PINHITS | NUMBER | PIN实际命中的次数 |
| PINHITRATIO | NUMBER | PIN命中率 |
| RELOADS | NUMBER | 在PIN过程中需要从硬盘中加载对象的次数 |
| INVALIDATIONS | NUMBER | 命名空间中的无效的对象 |
| DLM_LOCK_REQUESTS | NUMBER | 因为GET而导致的实例锁的数量 |
| DLM_PIN_REQUESTS | NUMBER | 因为PIN请求导致的实例锁的数量 |
| DLM_PIN_RELEASES | NUMBER | 请求释放PIN锁的次数 |
| DLM_INVALIDATION_REQUESTS | NUMBER | GET请求非法锁定实例的次数 |
| DLM_INVALIDATIONS | NUMBER | 从其他实例得到的非法PIN的数量 |
此表中必须特别注意PIN和GET的命中率PINHITRATIO或未命中率，当命中率小于99%或未命中率大于1%时，说明Hard Parse过多，可能需要加大共享池或是使用绑定变量等优化的动作。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | SQL> set pagesize 9999 SQL> select * from V$LIBRARYCACHE; NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO RELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS ---------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ------------- ----------------- ---------------- ---------------- ------------------------- ----------------- SQL AREA 594286117 532183117 .89549983 3854192240 3758362668 .975136276 4656561 30395265 0 0 0 0 0 TABLE/PROCEDURE 330284952 320056059 .969030097 882705717 861566271 .976051536 1346918 8 0 0 0 0 0 BODY 147404812 147402348 .999983284 204614861 204611069 .999981468 1384 0 0 0 0 0 0 TRIGGER 81503324 81503142 .999997767 81660297 81660023 .999996645 84 0 0 0 0 0 0 INDEX 3426436 2203206 .643002233 3365348 1526863 .453701371 15064 0 0 0 0 0 0 CLUSTER 152870 152531 .99778243 159616 159277 .997876153 0 0 0 0 0 0 0 LOB 4 2 .5 4 0 0 2 0 0 0 0 0 0 DIRECTORY 56367 56308 .998953288 94051 93843 .997788434 90 0 0 0 0 0 0 QUEUE 583793 583467 .999441583 37840404 37840726 1.00000851 324 0 0 0 0 0 0 JAVA SOURCE 322 216 .670807453 652 432 .662576687 6 0 0 0 0 0 0 JAVA RESOURCE 8 7 .875 8 7 .875 0 0 0 0 0 0 0 APP CONTEXT 157 54 .343949045 314 211 .671974522 0 0 0 0 0 0 0 RULESET 12587 10859 .8627155 17816224 17813200 .999830267 5 0 0 0 0 0 0 XML SCHEMA 741 559 .754385965 1233 735 .596107056 89 0 0 0 0 0 0 SUBSCRIPTION 76673 75570 .985614232 76673 75568 .985588147 2 0 0 0 0 0 0 JAVA DATA 324 321 .990740741 2935 2929 .997955707 0 0 0 0 0 0 0 RULE 4082 2892 .708476237 4082 2041 .5 851 0 0 0 0 0 0 XDB CONFIG 1 0 0 1 0 0 0 0 0 0 0 0 0 TEMPORARY TABLE 73590 62252 .845930154 73590 0 0 62252 0 0 0 0 0 0 TEMPORARY INDEX 29522 14854 .503150193 29522 0 0 14854 0 0 0 0 0 0 EDITION 407713 407710 .999992642 734246 734238 .999989104 1 0 0 0 0 0 0 DBLINK 215399 215398 .999995357 0 0 1 0 0 0 0 0 0 0 OBJECT ID 4562253 0 0 0 0 1 0 0 0 0 0 0 0 SCHEMA 10785962 10785704 .99997608 0 0 1 0 0 0 0 0 0 0 DBINSTANCE 1 0 0 0 0 1 0 0 0 0 0 0 0 SQL AREA STATS 32972537 4176902 .126678211 32972512 4175507 .126635999 1395 0 0 0 0 0 0 ACCOUNT_STATUS 207696 207695 .999995185 0 0 1 0 0 0 0 0 0 0 SQL AREA BUILD 32949023 4180409 .126875052 0 0 1 0 0 0 0 0 0 0 28 rows selected. |
OCP真题
真题1、Identify the memory component from which memory may be allocated for:
1.Session memory for the shared server
2.Buffers for I/O slaves
3.Oracle Database Recovery Manager(RMAN) backup and restore operations
A、Large Pool B、Redo Log Buffer
C、Database Buffer Cache D、Program Global Area(PGA)
答案：A。
根据本小节的讲述，题目中的3项均是大池（Large Pool）的适用场景。
所以，本题的答案为A。
真题2、You have executed this command to change the size of the database buffer cache:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2516582; System altered. To verify the change in size, you executed this command: SQL> SHOW PARAMETER DB_CACHE_SIZE NAME TYPE VALUE -------------- ------------ -------- db_cache_size big integer 4194304 |
Why is the value set to 4194304 and not to 2516582?
A、because 4194304 is the granule size
B、because 4194304 is the standard block size
C、because 4194304 is the largest nonstandard block size defined in the database
D、because 4194304 is the total size of data already available in the database buffer cache
答案：A。
Oracle分配内存的单位是granule，即粒度。最小的粒度为4M，设置大小不到一个粒度按一个粒度计算，通过如下的SQL可查看粒度的大小：
SELECT * FROM V$SGAINFO WHERE NAME='Granule Size';
在本题中，设置的DB_CACHE_SIZE约为2.5M，不足1个粒度，所以，Oracle自动调整为4M大小。
所以，本题的答案为A。
如何解决ORA-04030和ORA-04031错误？
ORA-04030和ORA-04031都是典型的内存分配错误，下面分别讲解。
（一）ORA-04030错误
ORA-04030报错形如“ORA-04030 'out of process memory when trying to allocate %s bytes (%s,%s)'”，该错误意味着Oracle Server进程无法从操作系统分配更多内存。该内存由PGA组成，其内容取决于服务器配置。对于专用的服务器进程，内存包含堆栈以及用于保存用户会话数据、游标信息和排序区的UGA。在多线程服务器（共享服务器）中，UGA被分配在SGA中，所以在这种配置下UGA不是造成ORA-04030错误的原因。因此，ORA-04030表示进程需要更多内存（堆栈、UGA或PGA）来执行其任务。
ORA-04030错误可能是进程本身导致的，例如进程需要过多的内存，或者一些其它原因导致操作系统内存被耗尽，例如，SGA分配太大或系统虚拟内存（物理内存+交换空间）中要容纳的进程过多。许多操作系统会对单个进程能够获取的内存量加以限制，以便自我保护。在大部分情况下，发生ORA-04030错误的进程并非总是内存损耗的元凶，错误的发生仅仅是因为此进程无法取得所需的内存造成的。
MOS文档（ID 1548826.1和199746.1）对该错误有非常详细的说明。如果发生了ORA-04030错误，那么会在告警日志中记录详细信息。若发生ORA-04030错误，则可以从以下几个方面去排查该错误：
① 是否仍然有足够的可用内存？
主要使用操作系统特定的工具（top、topas、vmstat、swapon -s）来检查内存使用情况。如果有足够的内存可用，那么就需要检查操作系统是否存在强制限制。如果内存已被耗尽，那么就需要找出内存被用到了哪些地方。
② 是否设置了操作系统限制？
如果仍有足够的内存可用，那么有可能是进程需要使用的内存量是不被允许的。使用命令“ulimit -a”查看操作系统限制。尤其对于“data seg size”选项，应该设置为unlimited。在RAC或GRID环境中，由于数据库监听是通过CRS进行启动，所以监听继承了root用户的ulimit限制。如果在root的ulimit限制中data(kbytes)的限制为1310kb，那么表示每个通过监听连接的进程能分配的内存资源不能超过1310kb。
③ 是否设置了Oracle限制？
查询参数PGA_AGGREGATE_TARGET的大小，该参数限制一个实例可以分配的PGA总量。以下查询用于查找分配给所有会话的PGA区的内存总量：
1 2 3 4 | SELECT SUM(VALUE) / 1024 / 1024 PGA_SIZE_MB FROM V$SESSTAT S, V$STATNAME N WHERE N.STATISTIC# = S.STATISTIC# AND NAME = 'session pga memory'; |
④ 哪个进程需要的内存过多？
一些操作会需要大量的进程内存，例如，大型的PL/SQL表或大量的排序操作。在这些情况下，在出现错误ORA-04030之前，进程将会运行一段时间，所以，可以在这段时间内找出内存分配的位置和原因。可以使用以下查询来查找所有Oracle进程的PGA和UGA大小：
1 2 3 4 5 | SELECT SID, NAME, VALUE FROM V$STATNAME N, V$SESSTAT S WHERE N.STATISTIC# = S.STATISTIC# AND NAME LIKE 'session%memory%' ORDER BY 3 ASC; |
通常，从操作系统的角度来确认进程内存使用情况，是一个好办法。毕竟，使用过多内存的不一定是Oracle Server进程。
⑤ 如何收集有关进程实际正在执行的任务的信息？
可以做heapdump，然后分析dump结果：
1 2 3 4 5 6 7 | SQL> select PID from v$process p, v$session s where p.addr=s.paddr and sid=<SID>; SQL> oradebug setorapid <PID> SQL> oradebug unlimit SQL> oradebug dump errorstack 3 SQL> oradebug dump heapdump 536870917 SQL> oradebug tracefile_name (shows the path and filename information) SQL> oradebug close_trace |
或者使用event，如下所示：
设置系统级事件来产生ORA-04030错误时的trace文件，设置event如下：
1 | alter system set events '4030 trace name errorstack level 3;name HEAPDUMP level 536870917'; |
得到报错的dump文件后，关闭生成event：
1 2 3 4 | alter system set events '4030 trace name errorstack off'; alter system set events '4030 trace name HEAPDUMP off'; |
（二）ORA-04031错误
SGA中的内存池由不同大小的内存块组成。当数据库实例启动时，大量的内存块被分配到不同的池中并且由空闲列表哈希Bucket追踪。随着时间推移，由于内存块被分配和回收，内存块会根据它们的大小在池中的不同空闲列表Bucket中移动。
当Oracle不能找到一个足够大的内存块来满足用户操作所带来的内部分配请求的时候，ORA-04031错误就可能在SGA的任何一个池中（Large Pool、Java Pool、Streams Pool（10g新增）、Shared Pool）出现。ORA-04031错误信息会指出哪个池出了问题。如果错误指出问题不在共享池中，那么这通常意味着对应用环境来说，出问题的池配置的太小了。可以将出问题的池增大，然后继续观察后续的问题。如果使用Oracle 10g的ASMM功能，那么MMAN进程会随着时间推移，尝试根据内存需要收缩或者增大SGA中不同组件的大小。如果在Large Pool、Streams Pool或者Java Pool中遇到了ORA-04031错误，那么可以通过增大SGA_TARGET，使MMAN可以管理更多的内存。
MOS文档（ID 2016002.1和146599.1）对ORA-04031有非常详细的说明。
在Oracle 9i和之后的版本，共享池可以被划分为子池。每个子池是一个小号的共享池，有它自己的空闲列表，内存结构条目和LRU列表。这是一个对共享池和大池的可扩展性的改变，现在每一个子池都由一个Child Latch来保护，因此可以增加这些池的吞吐量。这意味着不再有之前版本的对于共享池和大池的单独Latch的竞争。共享池中的保留区域也被平均的划分到每个子池中。
在Oracle 9.2.0.5或者更高版本中，当发生ORA-04031错误时，会生成一个trace文件（通过_4031_dump_bitvec参数控制）。ORA-04031的报错形式如下所示：
1 | ORA-04031:unable to allocate 4192 bytes of shared memory("shared pool","SELECT/*+FIRST_ROWS*/*F...","sql area(6,0)","kafco:qkacol") |
在这个例子中，问题发生在共享池。错误消息也包含内存请求失败的大小的信息。在这里，请求SQL Area中4192byte时失败，并且发生在第6个子池中。
子池的个数跟以下3个方面相关：
① CPU的个数 系统中每4个CPU可以分配一个子池，最多分配7个子池，由参数CPU_COUNT控制。
② 参数SHARED_POOL_SIZE大小 若ASMM或AMM没有启用（即手动管理内存，SGA_TARGET和MEMORY_TARGET都没有设置），则在子池的内存分配原则如下所示：
| 版本 | 最小子池大小 |
|---|---|
| 9i（9.2.0.5） | 128MB |
| 大于10g且小于10.2.0.3 | 256MB |
| 大于10.2.0.3 | 512MB |
若启用了ASMM或AMM，参数SHARED_POOL_SIZE被显式的设置了值，则使用上表的分配原则。如果没有显式的设置该参数的值，那么SHARED_POOL_SIZE的值取SGA_TARGET的50%。如果使用的是AMM，则SGA_TARGET的值取MEMORY_TARGET的60%。
③ 隐含参数“_KGHDSIDX_COUNT”的大小，默认为1，表示1个子池。设置的SQL命令为：“ALTER SYSTEM SET "_KGHDSIDX_COUNT"=2 SCOPE=SPFILE;”。
在以上规则中，若设置了隐含参数“_KGHDSIDX_COUNT”为大于1的值，则以该参数为准，否则取①和②中的最小值。例如，如果在Oracle 11g中，有16个CPU，MEMORY_TARGET为4.2G，SGA_TARGET和SHARED_POOL_SIZE的值都为0，那么16个CPU可以分配4个子池，SHARED_POOL_SIZE的值为1.26G（4.2*0.6*0.5），可以分配2个子池，每个子池为630MB。由于没有设置隐含参数“_KGHDSIDX_COUNT”，所以，该系统的子池个数为MIN(4,2)=2。
子池的创建是在启动过程中SGA创建时发生的，所以修改隐含参数“_KGHDSIDX_COUNT”、SHARED_POOL_SIZE和CPU_COUNT之后，需要重新启动数据库，子池的数量才能变动。
具体的子池分配、内存情况及剩余内存情况可以使用如下的SQL语句查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | col SUBPOOL format a30 SELECT 'shared pool (' ||NVL(DECODE(TO_CHAR(KSMDSIDX), '0', '0 - Unused', KSMDSIDX), 'Total') || '):' SUBPOOL, ROUND(SUM(KSMSSLEN) / 1048576, 2) size_all_MB, ROUND(sum(CASE WHEN LOWER(KSMSSNAM) LIKE LOWER('%free memory%') THEN KSMSSLEN ELSE 0 END)/ 1048576, 2) size_free_mb FROM X$KSMSS WHERE KSMSSLEN > 0 GROUP BY ROLLUP(KSMDSIDX) ORDER BY SUBPOOL ASC; SQL> col SUBPOOL format a30 SQL> SELECT 'shared pool (' ||NVL(DECODE(TO_CHAR(KSMDSIDX), '0', '0 - Unused', KSMDSIDX), 'Total') || '):' SUBPOOL, 2 ROUND(SUM(KSMSSLEN) / 1048576, 2) size_all_MB, ROUND(sum(CASE WHEN LOWER(KSMSSNAM) LIKE LOWER('%free memory%') THEN KSMSSLEN ELSE 0 END)/ 1048576, 2) size_free_mb 4 FROM X$KSMSS 5 WHERE KSMSSLEN > 0 GROUP BY ROLLUP(KSMDSIDX) 7 ORDER BY SUBPOOL ASC; SUBPOOL SIZE_ALL_MB SIZE_FREE_MB ------------------------------ ----------- ------------ shared pool (1): 7773.04 1751.41 shared pool (2): 7002.48 2147.59 shared pool (3): 7002.54 1604.62 shared pool (4): 6750.91 2046.75 shared pool (5): 7772.52 1742.72 shared pool (6): 6749.01 2054.6 shared pool (Total): 43050.51 11347.69 7 rows selected. |
通过以下查询可以详细列举不同子池的Free内存块情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | set linesize 1000 col sga_heap format a15 col size format a10 SELECT KSMCHIDX SubPool, 'sga heap(' || KSMCHIDX || ',0)' SGA_HEAP, KSMCHCOM CHUNKCOMMENT , decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') "size", COUNT(*), KSMCHCLS STATUS, SUM(KSMCHSIZ) BYTES, ROUND(SUM(KSMCHSIZ) / 1048576, 2) MB FROM X$KSMSP d WHERE KSMCHCOM = 'free memory' GROUP BY KSMCHIDX, KSMCHCLS, 'sga heap(' || KSMCHIDX || ',0)', KSMCHCOM, KSMCHCLS , decode(round(ksmchsiz/1000),0,'0-1K',1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') ORDER BY SubPool ; |
需要注意的是，在ASM环境中也用到了共享池。在Oracle 10gR1版本的ASM实例上有ORA-04031的问题，原因是默认值太小了，不能适应磁盘组管理活动的需要。
当试图在共享池中分配大块连续内存而失败时，Oracle会首先从池中清理当前不用的对象从而使得空闲内存碎片（chunk：内存块）得以合并。如果这样仍然没有足够大的单个chunk来满足分配需要，那么会产生ORA-04031报错。有许多ORA-04031错误直接原因都是由于共享池的大小或调整不当造成的。
在日常维护上可以查询视图V$SHARED_POOL_RESERVED，当REQUEST_FAILURES>0时，则说明Shared Pool在内存分配上存在一定的问题。
如果问题可以重现，那么可在执行有问题的SQL语句前，在会话级别对事件进行设置：
1 2 3 | SQL> alter session set events '4031 trace name errorstack level 3'; SQL> alter session set events '4031 trace name HEAPDUMP level 536870914'; |
从Oracle 9.2.0.5版本开始，除了在请求heapdump时使用1、2、3或32等级，还可以使用相同等级并加值536870912，这样将会在此等级上再进一步显示5个最大的subheaps同时每个subheap下显示相关5个最大的heap areas。
下面给出分析ORA-04031产生的TRACE文件的一般步骤：
（1）首先需要确认报错的是SGA的哪个池、哪个子池、需要分配哪部分内存、需要分配多大内存等问题
大部分信息可以从报错信息直接获取到。检查告警日志并查看错误是否记录，但是，不是所有的ORA-04031错误都会记录在告警日志中。如果错误被记录，请检查是SGA的哪部分收到此错误，共享池，大池，Java池或Streams池。找到发生ORA-04031错误时的trace文件。
（2）在trace文件中搜索关键字“Memory Utilization of Subpool”（子池，可以7直接搜索Subpool）和granule size（粒度大小），确认子池的个数及粒度的大小等参数。
（3）找到各个Subpool的使用情况
从Subpool部分分析哪个组件用的内存最多，free memory还剩多少内存？可能有的子池的free memory已经为0了。可以从视图V$SGASTAT来检查是否有组件表现出非正常增长，查询SQL可以为“SELECT * FROM V$SGASTAT A WHERE A.NAME='obj stat memo';”。如果V$SGASTAT中没有记录，那么也可以查询DBA_HIST_SGASTAT视图：SELECT * FROM DBA_HIST_SGASTAT A WHERE A.NAME='obj stat memo';。
如果用的最多的是SQLA（SQLAREA），那么很可能就是没有使用绑定变量。
如果是不常见的组件（例如，obj stat memo），那么很可能就是BUG。如果是BUG，那么可以拿关键字去MOS上进行搜索，基本上可以找到相关内容。
在产生的TRACE文件中还需要关注“LIBRARY CACHE STATISTICS”，它代表库缓存的信息，如下：
1 2 3 4 5 6 7 8 9 10 11 12 | LIBRARY CACHE STATISTICS: (emphasis added on key areas) namespace gets hit ratio pins hit ratio reloads invalids -------------- --------- --------- --------- --------- ---------- ---------- CRSR 1201146935 0.999 4127355897 0.997 977858 467750 TABL 9017452 0.998 680339529 1.000 98991 0 BODY 45900052 1.000 102763403 1.000 428 0 TRGR 127502 0.999 1661141 1.000 194 0 INDX 880913 0.990 1846250 0.978 20895 0 CLST 15560 0.997 32730 0.997 58 0 KGLT 0 0.000 0 0.000 0 0 PIPE 0 0.000 0 0.000 0 0 LOB 0 0.000 0 0.000 0 0 |
在Library Cache统计信息里，找到“hit ratio”百分比，这指示了碎片问题。目标是使得“hit ratio”尽可能的接近100%。另外要查看reloads和invalids信息。reloads和invalids很多意味着库缓存中发生了很多内存清理，可能意味着应用低效和碎片化。
在AWR中，可以查看“Load Profile”、“Shared Pool Advisory”和“Library Cache Activity”等部分进行分别分析。
可以通过如下的SQL语句分析ORA-04031出现的次数和需要分配的字节数：
1 2 3 4 5 | SELECT INDX,KGHLURCR,KGHLUTRN,KGHLUFSH,KGHLUOPS,KGHLUNFU,KGHLUNFS FROM SYS.X$KGHLU WHERE INST_ID = userenv('Instance'); INDX KGHLURCR KGHLUTRN KGHLUFSH KGHLUOPS KGHLUNFU KGHLUNFS ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 1233 30360 57700 518988 114 4096 |
其中，KGHLUNFU表示出现ORA-04031的次数，这里指出现了114次的4031错误。KGHLUNFS表示最后一次出现ORA-04031时，申请的分配大小，在以上结果中表示最后申请共享池的大小是4096字节。
综上所述，导致ORA-04031错误的原因有如下几点：
（1）配置问题，表现为某些池配置过小或配置错误。解决办法为增大相应的池（例如，Shared Pool）大小及使用AMM方式来管理内存。如果报错的是Large Pool或Java Pool，那么可以简单的增大参数LARGE_POOL_SIZE和JAVA_POOL_SIZE即可。
（2）内存中存在大量碎片，导致在分配内存的时候没有连续的内存可供分配从而导致ORA-04031错误。解决办法为刷新共享池（ALTER SYSTEM FLUSH BUFFER_CACHE;），该操作在生产库上要慎用。也可以重启数据库，重启数据库会释放内存，还会清理内存碎片。
（3）应用问题，主要表现在①没有使用绑定变量，不使用绑定变量会导致库缓存的过度使用。②有多个子游标，每个子游标都会在共享池中分配空间。③高解析率，可能由于使用了动态PL/SQL或在高负载阶段执行DDL语句。每次DDL语句执行，都会导致所有引用了这个对象的语句失效。下次执行引用了这个对象的SQL语句时，则不得不重新解析并加载到共享池中。④过度的打开CURSOR而不关闭，一般会导致Shared Pool中的ORA-04031错误。⑤SESSION_CACHED_CURSORS、OPEN_CURSOR设置过高。OPEN_CURSOR如果设置的过大，那么会导致Library Cache中很多对象都处于pin状态，而不能释放，那么当申请Shared Pool内存时，通过LRU依然不能找到可用空间，就会导致ORA-04031错误。遇见这种情况可以适当减少OPEN_CURSOR的值。
（4）Subpool的不均衡使用。Subpool的不均衡使用是使用Subpool一个缺点之一，对于这种情况可以设置隐含参数“_ENABLE_SHARED_POOL_DURATIONS＝FALSE”来改变Shared Pool的Subpool内存结构的分配方式，或者完全使用AMM。
（5）Oracle的BUG导致内存泄露。例如，在一些版本中查询V$SEGMENT_STATISTICS这样的视图导致内存泄露，使Shared Pool内存耗光。同样的情形还有类似于“obj stat memory”、“gcs resources”、“ges resources”等。这类内存通常是在分配时就确定了固定的用途，不能用于其它用途，因此极容易产生碎片。
参考
有关ORA-04030错误的更多内容可以参考：http://blog.itpub.net/26736162/viewspace-2138387/。
有关ORA-04031错误的更多内容可以参考：http://blog.itpub.net/26736162/viewspace-2138388/。
诊断并解决 ORA-4030 错误 (文档 ID 1548826.1)
ORA-4031 错误故障排除与诊断[视频] (文档 ID 2016002.1)
Troubleshooting and Diagnosing ORA-4031 Error [Video] (文档 ID 396940.1)
