Oracle内存全面分析(11)

oracle 性能优化

目录(?)[-]

1. 内存错误处理
2. 1. 分析定位ORA-4030
   2. 1. 错误产生的原因
      2. 错误分析
      3. 1. 操作系统是否由足够的内存
         2. 是否受到系统限制
         3. 哪个Oracle进程请求了过多的内存
         4. 收集进程正在进行的操作
      4. 解决4030错误的建议
   3. 分析定位ORA-4031
   4. 1. 错误产生的原因
      2. 错误分析
      3. 1. 对shared pool的分析
         2. 对large pool的分析
      4. 解决4031错误
      5. 1. bug导致的错误
         2. Shared pool太小
         3. Shared pool碎片
         4. 编译java代码导致的错误
         5. Large pool导致的错误
      6. SGA内存自动管理
      7. FLUSH SHARED POOL
      8. TRACE 4031错误

转自：http://www.hellodba.com/reader.php?ID=110&lang=CN

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查看物理内存和虚拟内存页的使用情况

[plain]  view plain copy

1. Physical Memory Usage (pages):   Total      Free      In Use    Modified  Main Memory (256.00Mb)                       32768      24849      7500     419  
2.  .....  
3. Paging File Usage (blocks):                     Free   Reservable  Total  
4.  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的一个输出：

[plain]  view plain copy

1. > swapinfo -mt  
2.              Mb      Mb      Mb   PCT  START/      Mb  
3. TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME  
4. dev        4096       0    4096    0%       0       -    1  /dev/vg00/lvol2  
5. dev        8000       0    8000    0%       0       -    1  /dev/vg00/swap2  
6. reserve       -   12026  -12026  
7. memory    20468   13387    7081   65%  
8. total     32564   25413    7151   78%       -       0    -  
9.    

 

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

[plain]  view plain copy

1. > maxmem  
2. Memory starts at: 6917529027641212928 (6000000000020000)  
3. Memory ends at:   6917529031936049152 (6000000100000000)  
4. 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下的限制：

[plain]  view plain copy

1. > ulimit -a  
2. time(seconds)        unlimited  
3. file(blocks)         unlimited  
4. data(kbytes)         1048576  
5. stack(kbytes)        131072  
6. memory(kbytes)       unlimited  
7. coredump(blocks)     4194303  

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

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

select   sid,name,valuefrom   v$statname n,v$sesstat swhere   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错误的进程并不一定是导致内存不足的进程。只不过在它请求分配内存时，内存已经不足了。很有可能在此之前就已经有大量消耗内存的进程导致内存不足。你需要找出内存消耗不断增长的进程，观察它锁进行的操作。这条语句可以查出会话进程正在执行的语句：

[sql]  view plain copy

1. select sql_text    
2. from v$sqlarea a, v$session s   
3. where a.address = s.sql_address and s.sid = ;  

 

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

[sql]  view plain copy

1. SQL> oradebug unlimitSQL> 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)  
2. 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语句的相关信息。以下语句可以查出没有共享的语句，

[sql]  view plain copy

1. 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中的内容，

[sql]  view plain copy

1. SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;   

    ·X$KSMSP表

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

[sql]  view plain copy

1. 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错误，错误信息一般如下：

[plain]  view plain copy

1. A SQL exception occurred while compiling: :   
2. 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的使用情况，

[sql]  view plain copy

1. 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的语法为，

[sql]  view plain copy

1. alter system flush shared_pool;  

3.2.6.  TRACE 4031错误

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

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

   

[sql]  view plain copy

1. 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文件。