Oracle内存全面分析(11)

作者： fuyuncat
来源： www.HelloDBA.com

3.   内存错误处理

Oracle 中最常见的内存错误就是4030 和4031 错误。这两个错误分别是在分配PGA 和SGA 时，没有足够内存分配导致的。经过我们以上对Oracle 内存的了解以及对内存管理机制的浅析，可以总结在这两种错误发生时，我们该如何分析和处理。

3 .1.            分析、定位 ORA-4030

4030 错误是由于oracle 进程在内存扩展时，无法从OS 获取到所需的内存而产生的报错。在专有服务模式下，Oracle 进程的内存包括堆栈、PGA 、UGA （从PGA 中分配）和有些进程信息；而MTS 下，UGA 是从SGA 中分配，不包括在进程的内存范围内。

3.1.1.  4030 错误产生的原因

PGA 的大小不是固定的，是可以扩展的。PGA 通过系统调用扩展堆数据段时，操作系统分配新的虚拟内存给进程作为PGA 扩展段。这些扩展段一般是几个KB 。只要需要，oracle 会分配几千个扩展段。然而，操作系统限制了一个进程的堆数据段的增长。在UNIX 中，这个限制一般受到OS 内核参数MAXDSIZ 限制，这是限制单个进程的。还有一个堆所有进程的虚拟内存的总的大小的限制。这个限制和swap 交换空间（虚拟内存）大小有关。如果在扩展PGA 内存时达到这些限制，就会抛4030 错误。

3.1.2.  4030 错误分析

既然知道了4030 错误产生的可能原因，我们在分析4030 错误时，就可以从这几个方面分别收集信息进行分析，并结合Oracle 进程内存的使用情况来解决问题。

3 .1.2.1.          操作系统是否由足够的内存

在不同的操作系统下，我们可以使用相应的工具来收集系统的内存使用情况，以判断内存是否足够：

·        OpenVMS systems

可以使用show memory 查看物理内存和虚拟内存页的使用情况

Physical Memory Usage (pages):   Total      Free      In Use    Modified


  Main Memory (256.00Mb)                       32768      24849      7500     419


 .....





Paging File Usage (blocks):                     Free   Reservable  Total

 DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS    30720   30720      39936


 DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS    226160  201088    249984

 DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS  462224  405296    499968

一般情况下，空闲pagefile 的之和不能小于它的总数之和的一半。而且SWAPFILE 必须是始终没有被使用的，它的空闲页必须和总页数相同。

·         Windows

Windows 下可以通过任务管理器的性能页来查看内存的使用情况，也可以使用TopShow 来观察系统进程的内存使用情况

·         UNIX

不同厂商的UNIX 下，各种工具的使用和统计结果可能有所不同。常用的对内存的查看工具主要有：

o        TOP —— 查看物理内存和交换空间

o        vmstat —— 查看物理内存和交换空间状况，以及观察是否有page out/page in

o        swapon –s —— 查看交换空间情况

o        swapinfo –mt —— 查看交换空间使用情况

下面是swapinfo 的一个输出：

> swapinfo -mt

             Mb      Mb      Mb   PCT  START/      Mb

TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME

dev        4096       0    4096    0%       0       -    1  /dev/vg00/lvol2

dev        8000       0    8000    0%       0       -    1  /dev/vg00/swap2

reserve       -   12026  -12026

memory    20468   13387    7081   65%

total     32564   25413    7151   78%       -       0    -

 

此外，在一些操作系统中，还可以通过Oracle 自己提供的工具maxmem 来检查一个进程能够分配的最大堆数据段的大小。

> maxmem

Memory starts at: 6917529027641212928 (6000000000020000)

Memory ends at:   6917529031936049152 (6000000100000000)

Memory available: 4294836224 (fffe0000)

3 .1.2.2.          是否受到系统限制

在操作系统，往往会有对单个进程或者所有进程能够分配的内存大小做了限制。当Oracle 分配进程内存时，如果达到这些限制，也会导致4030 错误。在不同操作系统中，可以用不同方式检查系统是否有做限制。

·         OpenVMS systems:

show process/id=<process id>/quota 可以显示一个进程的可用配额是多少。

·         Windows

如前所述，在window 32 位系统中，进程的可用内存限制为2G （可以通过其他方式突破此限制）。而windows 下，oracle 是以一个单独进程方式运行的，它的内存包括了堆栈、SGA 、PGA 。我们可以通过任务管理器或TopShow 来检查Oracle 进程是否达到此限制。

·         UNIX

可以使用命令ulimit 来查看unix 下的限制：

> ulimit -a

time(seconds)        unlimited

file(blocks)         unlimited

data(kbytes)         1048576

stack(kbytes)        131072

memory(kbytes)       unlimited

coredump(blocks)     4194303

3 .1.2.3.          哪个 Oracle 进程请求了过多的内存

有些进程会做某些操作时会需要分配大量内存，如使用了PL/SQL TABLE 或者做排序时。如果这样的进程在系统中运行一段时间后，就可能导致4030 错误的产生。我们可以用以下语句来查看各个进程的内存使用情况：

select

  sid,name,value

from

   v$statname n,v$sesstat s

where

   n.STATISTIC# = s.STATISTIC# and

   name like '%ga %'

order by 3 asc;

 

同时，我们还可以从操作系统的角度来确认这些大量消耗内存的进程。

·         OpenVMS systems:

show process/continious 可以查看各个进程的物理和虚拟内存的使用情况。

·         Windows

在windows 中，由于Oracle 是以一个单独进程运行的，而由线程来服务于会话的。因此无法查看单个会话的内存占用情况。

·         UNIX

UNIX 中，可以通过ps –lef|grep ora 来查看oracle 进程占用的内存情况。

3 .1.2.4.          收集进程正在进行的操作

在解决4030 问题时，有一点很重要，抛出4030 错误的进程并不一定是导致内存不足的进程。只不过在它请求分配内存时，内存已经不足了。很有可能在此之前就已经有大量消耗内存的进程导致内存不足。你需要找出内存消耗不断增长的进程，观察它锁进行的操作。这条语句可以查出会话进程正在执行的语句：

select sql_text  

from v$sqlarea a, v$session s 

where a.address = s.sql_address and s.sid = <SID>;

 

另外，可以做一个heapdump ，将结果发给Oracle 进行分析，

SQL> oradebug unlimit

SQL> oradebug setorapid <PID> (

通过

v$process

查到的

pid, 

用

setospid

来设置

OS

中的

PID

【或者

v$process

中的

spid

】

)

SQL> oradebug dump heapdump 7 (1-PGA; 2-Shared Pool; 4-UGA; 8-CGA; 16-top CGA; 32-large pool)



SQL> alter session set events '4030 trace name heapdump level 25'; 



3.1.3. 
		



解决

4030

错误的建议

如果问题是由于swap 空间不足造成的，并且由中度或者严重的page in/page out （可以用vmstat 查看），你就需要尝试降低系统整体的虚拟内存的使用（如调整SGA 大小），或者降低单个进程内存的使用（如调整sort_area_size ），或者减少进程数量（如限制processes 参数，使用MTS ）。而如果page in/page out 很少或者根本没有，就可以考虑增大swap 空间。某些系统中，可以考虑使用伪交换区（如hp-ux 中，可以考虑设置swapmen_on ）。

如果问题和PLSQL 操作有关，可以，1 、检查PLSQL 中的TABLE ，看看其中的数据是否全都必要，是否可以减少数据放入TABLE 中；2 、优化相关语句（比如通过调整优化器策略，使查询计划走sort 比较少的访问路径），减少sort 操作，或者减少sort_area_size （代价就是一部分sort 操作会放在磁盘上进行，降低性能）。

9i 以后可以考虑设置PGA 内存自动管理。即设置PGA_AGGREGATE_TARGET 在一定数值范围内，WORKAREA_SIZE_POLICY 设置为AUTO 。但是注意，9i 在OpenVMS 系统上、或者在MTS 模式下不支持PGA 内存自动关联。

如果是因为进程数过多导致的内存大量消耗，首先可以考虑调整客户端，减少不必要的会话连接，或者采用连接池等方式，以保持系统有稳定的连接数。如果会话非常多，且无法降低的话，可以考虑采用MTS ，以减少Oracle 进程数。

检查SGA 中的内存区是否分配过多（如shared pool 、large pool 、java pool ）等，尝试减少SGA 的内存大小。

在windows 下，可以尝试使用ORASTACK 来减少线程的堆栈大小，以释放更多的内存。

考虑增加物理内存。

3 .2.            分析、定位 ORA-4031

 

4031 错误是Oracle 在没有足够的连续空闲空间分配给Shared Pool 或者Large Pool 时抛出的错误。

3.2.1.  4031 错误产生的原因

前面我们描述Shared Pool 的空闲空间的请求、分配过程。在受到空闲空间请求时，内存管理模块会先查找空闲列表，看是否有合适的空闲chunk ，如果没有，则尝试从LRU 链表中寻找可释放的chunk ，最终还未找到合适的空闲chunk 就会抛出4031 错误。

在讨论4031 问题之前，可以先到第一章中找到与shared pool （shared_pool_size 、shared_pool_reserved_size 、shared_pool_reserved_min_alloc ）和large pool （large_pool_size ）的参数描述，再了解一下这些参数的作用。这对于理解和分析4031 错误会很有帮助。此外，还需要再回顾以下10g 以后的SGA 内存自动关联部分（相关参数是SGA_TARGET ），因为使用这一特性，能大大减少4031 错误产生的几率。

3 .2.2.  4031 错误分析

通常，大多数的4031 错误都是和shared pool 相关的。因此，4031 错误的分析，主要是对shared pool 的分析。

3.2.2.1.          对 shared pool 的分析

当4031 错误提示是shared pool 无足够连续内存可分配时，有可能是由于shared pool 不足或者shared pool 中严重的碎片导致的。

·         Shared pool 不足分析

视图V$SHARED_POOL_RESERVED 中可以查询到产生4031 的一些统计数据、以及shared pool 中保留区（前面说了，保留区是用来缓存超过一定大小的对象的shared pool 区）的统计信息。

如果字段REQUEST_FAILURES >= 0 并且字段LAST_FAILURE_SIZE < _SHARED_POOL_RESERVED_MIN_ALLOC ，可以考虑减小_SHARED_POOL_RESERVED_MIN_ALLOC ，以使更多的对象能放到保留区中区（当然，你还需要观察字段MAX_USED_SPACE 以确保保留区足够大）。如果还没有效果，就需要考虑增加shared_pool_size 了。

·         碎片问题分析

Library cache 和shared pool 保留区的碎片也会导致4031 错误的产生。

还是观察上面的视图，如果字段REQUEST_FAILURES > 0 并且字段LAST_FAILURE_SIZE > _SHARED_POOL_RESERVED_MIN_ALLOC ，就可以考虑增加_SHARED_POOL_RESERVED_MIN_ALLOC 大小以减少放入保留区的对象，或者增加SHARED_POOL_RESERVED_SIZE 和shared_pool_size （因为保留区是从shared pool 中分配的）的大小。

 

此外，要注意有一个bug 导致REQUEST_FAILURES 在9.2.0.7 之前所有版本和10.1.0.4 之前的10g 版本中统计的数据是错误的，这时可以观察最后一次4031 报错信息中提示的无法分配的内存大小。

3.2.2.2.          对 large pool 的分析

Large pool 是在MTS 、或并行查询、或备份恢复中存放某些大对象的。可以通过视图v$sgastat 来观察large pool 的使用情况和空闲情况。

而在MTS 模式中，sort_area_retained_size 是从large pool 中分配的。因此也要检查和调整这个参数的大小，并找出产生大量sort 的会话，调整语句，减少其中的sort 操作。

MTS 中，UGA 也是从large pool 中分配的，因此还需要观察UGA 的使用情况。不过要注意一点的是，如果UGA 无法从large pool 获取到足够内存，会尝试从shared pool 中去分配。

3.2.3.  解决 4031 错误

根据4031 产生的不同原因，采取相应办法解决问题。

3 .2.3.1.          bug 导致的错误

有很多4031 错误都是由于oracle bug 引起的。因此，发生4031 错误后，先检查是否你的系统的4031 错误是否是由bug 引起的。下面是已经发现的会引起4031 错误的bug 。相关信息可以根据bug 号或note 号到metalink 上查找。

 

BUG	说明	修正版本
Bug 1397603	ORA-4031 由于缓存句柄导致的SGA 永久内存泄漏	8172, 901
Bug 1640583	ORA-4031 due to leak / 由于查询计划中AND-EQUAL 访问路径导致缓冲内存链争用，从而发生内存泄漏。	8171, 901
Bug:1318267     （未公布）	如果设置了TIMED_STATISTICS 可能导致INSERT AS SELECT 无法被共享。	8171,  8200
Bug:1193003    （未公布）	Oracle 8.1 中，某些游标不共享。	8162, 8170, 901
Bug 2104071	ORA-4031 太多PIN 导致shared pool 消耗过大。	8174, 9013, 9201
Note 263791.1	许多与4031 相关的错误在9205 补丁集中修正。	9205

3 .2.3.2.          Shared pool 太小

大多数4031 错误都是由shared pool 不足导致的。可以从以下几个方面来考虑是否调整shared pool 大小：

·         Library cache 命中率

通过以下语句可以查出系统的library cache 命中率：

SELECT SUM(PINS) "EXECUTIONS", 

       SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",

       1 - SUM(RELOADS)/SUM(PINS)

FROM V$LIBRARYCACHE; 

如果命中率小于99 ％，就可以考虑增加shared pool 以提高library cache 的命中率。

·         计算shared pool 的大小

以下语句可以查看shared pool 的使用情况

select sum(bytes) from v$sgastat

where pool='shared pool'

and name != 'free memory';

 

专用服务模式下，以下语句查看cache 在内存中的对象的大小，

select sum(sharable_mem) from v$db_object_cache;

专用服务模式下，以下语句查看SQL 占用的内存大小，

select sum(sharable_mem) from v$sqlarea;

Oracle 需要为保存每个打开的游标分配大概250 字节的内存，以下语句可以计算这部分内存的占用情况，

select sum(250 * users_opening) from v$sqlarea;

 

此外，在我们文章的前面部分有多处提到了如何分析shared pool 是否过大或过小，这里就不在赘述。

3 .2.3.3.          Shared pool 碎片

每当需要执行一个SQL 或者PLSQL 语句时，都需要从library cache 中分配一块连续的空闲空间来解析语句。Oracle 首先扫描shared pool 查找空闲内存，如果没有发现大小正好合适的空闲chunk ，就查找更大的chunk ，如果找到比请求的大小更大的空闲chunk ，则将它分裂，多余部分继续放到空闲列表中。这样就产生了碎片问题。系统经过长时间运行后，就会产生大量小的内存碎片。当请求分配一个较大的内存块时，尽管shared pool 总空闲空间还很大，但是没有一个单独的连续空闲块能满足需要。这时，就可能产生4031 错误。

如果检查发现shared_pool_size 足够大，那4031 错误一般就是由于碎片太多引起的。

如果4031 是由碎片问题导致的，就需要弄清楚导致碎片的原因，采取措施，减少碎片的产生。以下是可能产生碎片的一些潜在因素：

o        没有使用共享SQL ；

o        过多的没有必要的解析调用（软解析）；

o        没有使用绑定变量。

以下表/ 视图、语句可以查询shared pool 中没有共享的SQL

·         通过V$SQLAREA 视图

前面我们介绍过这个视图，它可以查看到每一个SQL 语句的相关信息。以下语句可以查出没有共享的语句，

SELECT substr(sql_text,1,40) "SQL", 

count(*) , 

sum(executions) "TotExecs" 

FROM v$sqlarea 

WHERE executions < 5 –-语句执行次数

GROUP BY substr(sql_text,1,40) 

HAVING count(*) > 30 –-所有未共享的语句的总的执行次数

ORDER BY 2; 

·         X$KSMLRU 表

这张表保存了对shared pool 的分配所导致的shared pool 中的对象被清出的记录。可以通过它来查找是什么导致了大的shared pool 分配请求。

如果有许多对象定期会被从shared pool 中被清出，会导致响应时间太长和library cache latch 争用问题。

不过要注意一点，每当查询过表X$KSMLRU 后，它的内容就会被删除。因此，最好将查出的数据保存在一个临时的表中。以下语句查询X$KSMLRU 中的内容，

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0; 

·         X$KSMSP 表

从这张表中可以查到当前分配了多少空闲空间，这对于分析碎片问题很有帮助。一些语句可以查询shared pool 的空闲列表中chunk 的统计信息，

select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From", 

count(*) "Count" , max(KSMCHSIZ) "Biggest", 

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 

from x$ksmsp 

where KSMCHSIZ<140 

and KSMCHCLS='free' 

group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) 

UNION ALL 

select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) , 

count(*) , max(KSMCHSIZ) , 

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 

from x$ksmsp 

where KSMCHSIZ between 140 and 267 

and KSMCHCLS='free' 

group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20) 

UNION ALL 

select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) , 

count(*) , max(KSMCHSIZ) , 

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 

from x$ksmsp 

where KSMCHSIZ between 268 and 523 

and KSMCHCLS='free' 

group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) 

UNION ALL 

select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) , 

count(*) , max(KSMCHSIZ) , 

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 

from x$ksmsp 

where KSMCHSIZ between 524 and 4107 

and KSMCHCLS='free' 

group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) 

UNION ALL 

select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) , 

count(*) , max(KSMCHSIZ) , 

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 

from x$ksmsp 

where KSMCHSIZ >= 4108 

and KSMCHCLS='free' 

group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000); 

 

如果使用ORADEBUG 将shared pool 信息dump 出来，就会发现这个查询结果和trace 文件中空闲列表信息一直。

如果以上查询结果显示大多数空闲chunk 都在bucket 比较小的空闲列表中，则说明系统存在碎片问题。

3.2.3.4.          编译 java 代码导致的错误

当编译java （用loadjava 或deployjb ）代码时产生了4031 错误，错误信息一般如下：

A SQL exception occurred while compiling: : 

ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal") 

这里提示时shared pool 不足，其实是错误，实际应该是java pool 不足导致的。解决方法将JAVA_POOL_SIZE 加大，然后重启实例。

3 .2.3.5.          Large pool 导致的错误

Large pool 是在MTS 、或并行查询、或备份恢复中存放某些大对象的。但和shared pool 中的保留区（用于存放shared pool 的大对象）不同，large pool 是没有LRU 链表的，而后者使用的是shared pool 的LRU 链表。

在large pool 中的对象永远不会被清出的，因此不存在碎片问题。当由于large pool 不足导致4031 错误时，可以先通过v$sgastat 查看large pool 的使用情况，

SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool'; 

或者做一个dump ，看看large pool 中空闲chunk 的大小情况。

进入large pool 的大小条件是由参数LARGE_POOL_MIN_ALLOC 决定的，根据以上信息，可以适当调整LARGE_POOL_MIN_ALLOC 的大小。

Large pool 的大小是由LARGE_POOL_SIZE 控制的，因此当large pool 空间不足时，可以调整这个参数。

3.2.4.  SGA 内存自动管理

10g 以后，Oracle 提供了一个非常有用的特性，即SGA 内存自动管理。通过设置SGA_TARGET 可以指定总的SGA 大小，而无需固定每个区的大小。这就是说，当分配shared pool 或large pool 时，只要SGA 区足够大，就能获取到足够内存，因而可以大大减少4031 错误发生的几率。

3 .2.5.  FLUSH SHARED POOL

使用绑定变量是解决shared pool 碎片的最好方法。此外，9i 以后，可以设置CURSOR_SHARING 为FORCE ，强行将没有使用绑定变量的语句使用绑定变量，从而共享SQL 游标。当采用以上措施后，碎片问题并不会马上消失，并可能还会长时间存在。这时，可以考虑flush shared pool ，将内存碎片结合起来。但是，在做flush 之前，要考虑以下问题。

·         Flush 会将所有没有使用的游标从library cache 中清除出去。因此，这些语句在被再次调用时会被重新硬解析，从而提高CPU 的占用率和latch 争用；

·         如果应用没有使用绑定变量，即使flush 了shared pool 以后，经过一段时间运行，仍然会出现大量碎片。因此，这种情况下，flush 是没有必要的，需要先考虑优化应用系统；

·         如果shared pool 非常大，flush 操作可能会导致系统被hung 住。

 

因此，如果要flush shared pool ，需要在系统不忙的时候去做。Flush 的语法为，

alter system flush shared_pool;

3 .2.6.  TRACE 4031 错误

如果问题比较复杂（比如由于内存泄漏导致），或者你不幸遇上了oracle 的bug ，这时就需要考虑设置4031 事件来trace 并dump 出相关内存信息。

以下语句在整个系统设置4031 事件，

SQL> alter system set events '4031 trace name errorstack level 3'; 

SQL> alter system set events '4031 trace name HEAPDUMP level 3'; 

这个事件也可以在会话中设置，只要将以上语句中的“system ”改为“session ”就行了。

然后将dump 出来的trace 文件发给oracle 吧。

不过注意一点，9205 以后就无需设置这个事件了，因为一旦发生4031 错误时，oracle 会自动dump 出trace 文件。