如何解决ORA-04031错误

以这种方式操作一段时间之后，共享池结构就会出现碎片。

    当共享池存在碎片的问题，分配一片空闲的空间就会花费更多的时间，数据库性能也会下降(整个操作的过程中，"chunk allocation"被一个叫做"shared pool latch"的闩所控制)或者是出现ORA-04031错误errors(在数据库不能找到一个连续的空闲内存块的时候)。

        参考<Note:616223.1>:可以得到关于共享池碎片的详细讨论。

    如果SHARED_POOL_SIZE足够大，大多数的ORA-04031错误都是由共享池中的动态SQL碎片导致的。可能的原因如下：

        非共享的SQL；

        生产不必要的解析调用(软解析)；

        没有使用绑定变量

    要减少碎片的产生你需要确定是前面描叙的几种可能的因素。可以采取如下的一些方法，当然不只局限于这几种：应用调整、数据库调整或者实例参数调整。

    请参考<Note:62143.1>，描述了所有的这些细节内容。这个注释还包括了共享池如何工作的细节。

    下面的视图有助于你标明共享池中非共享的SQL/PLSQL：

         V$SQLAREA视图

        这个视图保存了在数据库中执行的SQL语句和PL/SQL块的信息。下面的SQL语句可以显示给你带有literal的语句或者是带有绑定变量的语句：

1 SQL> col sql_text for a45;
2 SQL> select substr (sql_text,1,40) "SQL",count(*),
3   2     sum (executions) "TotExecs"
4   3    from v$sqlarea
5   4     where executions < 5
6   5  group by substr (sql_text,1,40)
7   6    having count(*) > 30
8   7  order by 2;

        注：Having后的数值"30"可以根据需要调整以得到更为详细的信息。

        X$KSMLRU视图

        这个固定表x$ksmlru跟踪共享池中导致其它对象换出(age out)的应用。这个固定表可以用来标记是什么导致了大的应用。

        如果很多对象在共享池中都被阶段性的刷新可能导致响应时间问题并且有可能在对象重载入共享池中的时候导致库高速缓冲闩竞争问题。

        关于这个x$ksmlru表的一个不寻常的地方就是如果有人从表中选取内容这个表的内容就会被擦除。这样这个固定表只存储曾经发生的最大的分配。这个值在选择后被重新设定这样接下来的大的分配可以被标记，即使它们不如先前的分配过的大。因为这样的重置，在查询提交后的结果不可以再次得到，从表中的输出的结果应该小心的保存。监视这个固定表运行如下操作：

1 SQL> select * from x$ksmlru where ksmlrsiz > 0;

        这个表只可以用SYS用户登录进行查询。

        X$KSMSP视图(类似堆Heapdump信息)

        使用这个视图能找出当前分配的空闲空间，有助于理解共享池碎片的程度。如我们在前面的描述，查找为游标分配的足够的大块内存的第一个地方是空闲列表(free list)。下面的语句显示了空闲列表中的大块内存：

SQL> SELECT   '0 (<140)' bucket, ksmchcls, 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, 10 * TRUNC (ksmchsiz / 10)
UNION ALL
SELECT   '1 (140-267)' bucket, ksmchcls, 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, 20 * TRUNC (ksmchsiz / 20)
UNION ALL
SELECT   '2 (268-523)' bucket, ksmchcls, 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, 50 * TRUNC (ksmchsiz / 50)
UNION ALL
SELECT   '3-5 (524-4107)' bucket, ksmchcls, 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, 500 * TRUNC (ksmchsiz / 500)
UNION ALL
SELECT   '6+ (4108+)' bucket, ksmchcls, 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, 1000 * TRUNC (ksmchsiz / 1000);

BUCKET           KSMCHCLS       From    Count     Biggest    AvgSize     Total
-------------- -------- ---------- ---------- ---------- ---------- ----------
0 (<140)       free        20       36          28     24       884
0 (<140)       free        70       10          76     73       736
0 (<140)       free        40       13          48     46       604
0 (<140)       free           100        2         108    106       212
0 (<140)       free        80       20          88     85      1708
0 (<140)       free        50       15          56     52       792
0 (<140)       free        30       22          36     34       764
0 (<140)       free           120        7         120    120       840
0 (<140)       free        60       11          68     63       696
1 (140-267)    free           240        1         248    248       248
1 (140-267)    free           220        1         228    228       228

BUCKET           KSMCHCLS       From    Count     Biggest    AvgSize     Total
-------------- -------- ---------- ---------- ---------- ---------- ----------
1 (140-267)    free           200        2         208    206       412
1 (140-267)    free           140        1         156    156       156
2 (268-523)    free           400        1         416    416       416
2 (268-523)    free           450        2         476    476       952
3-5 (524-4107) free          1000        2        1424       1224      2448
3-5 (524-4107) free           500        2         880    712      1424
3-5 (524-4107) free          1500        1        1532       1532      1532
6+ (4108+)     free       1646000        1     1646592    1646592    1646592
6+ (4108+)     free          9000        1        9432       9432      9432
6+ (4108+)     free       1915000        1     1915828    1915828    1915828
6+ (4108+)     free       3891000        1     3891140    3891140    3891140

22 rows selected.

4.ORA-04031错误与Large Pool
    大池是个可选的内存区，为以下的操作提供大内存分配：

        MTS会话内存和Oracle XA接口；

        Oracle备份与恢复操作和I/O服务器进行用的内存(缓冲)；

        并行执行消息缓冲。

    大池没有LRU列表。这和共享池中的保留空间不同，保留空间和共享池中其他分配的内存使用同样的LRU列表。大块内存从不会换出大池中，内存必须是显式的被每个会话分配并释放。一个请求如果没有足够的内存，就会产生类似这样的一个ORA-04031错误：

1 ORA-04031：unable to allocate XXXX bytes of shared memory
2 ("large pool","unknown object","session heap","frame")

这个错误发生时候可以检查几件事情：
        1).使用如下语句检查v$sgastat，得知使用和空闲的内存：

1 SQL> select pool,name,bytes from v$sgastat where pool = 'large pool';
2 
3 POOL         NAME                 BYTES
4 ------------ -------------------------- ----------
5 large pool   PX msg pool            491520
6 large pool   free memory           3702784

        2).你还可以采用heapdump level 32来dump大池的堆并检查空闲的大块内存的大小
        从大池分配的内存如果是LARGE_POOL_MIN_ALLOC子节的整块数有助于避免碎片。任何请求分配小于LARGE_POOL_MIN_ALLOC大块尺寸都将分配LARGE_POOL_MIN_ALLOC的大小。一般来说，你会看到使用大池的时候相对共享池来说要用到更多的内存。通常要解决大池中的ORA-04031错误必须增加LARGE_POOL_SIZE的大小。

5.ORA-04031和共享池刷新

     有一些技巧提高游标的共享能力，从而共享池碎片和ORA-04031都会减少。最佳途径是调整应用使用绑定变量。另外在应用不能调整的时候考虑使用CURSOR_SHARING参数和FORCE不同的值来做到(要注意那会导致执行计划改变，所以建议先对应用进行测试)。当上述技巧都不可以用的时候，并且碎片问题在系统中比较严重，刷新共享池可能有助于减轻碎片问题。但是，必须加以如下考虑：

         刷新将导致所有没有被使用的游标从共享池删除。这样，在共享池刷新之后，大多数SQL和PL/SQL游标必须被硬解析。这将提供CPU的使用，也会加大Latch的活动。

         当应用程序没有使用绑定变量并被需要用户进行类似的操作的时候(如在OLTP系统中)，刷新之后会很快还会出现碎片问题。所以共享池对设计糟糕的应用程序来说不是解决办法。

         对一个大的共享池刷新可能会导致系统挂起，尤其是实例繁忙的时候，推荐的非高峰的时候刷新

6.ORA-04031错误的高级分析

    如果前述的这些技术内容都不能解决ORA-04031错误，可能需要额外的跟踪信息来得到问题发生的共享池的快照。

    调整init.ora参数添加如下的事件得到该问题的跟踪信息：

event = "4031 trace name errorstack level 3"

event = "4031 trace name HEAPDUMP level 3"

    如果问题可重现，该事件可设定在会话层，在执行问题语句之前使用如下的语句：

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

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

    把这个跟踪文件发给Oracle支持人员进行排错。