Oracle内存全面分析(11)[HelloDBA.COM]

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=/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 = ;
另外，可以做一个heapdump，将结果发给Oracle进行分析，
SQL> oradebug unlimit SQL> oradebug setorapid (通过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文件。