总结上次碰到的ORA-04030错误

先更正一下，上次碰到的SQL，不是in里有9500多个值，是开发人员把9500多个值拆成了10个in,然后用or连接起来，太长了，没仔细看。所以不会报ORA-01795。

通常情况下4030错误，是由于PGA内存分配出现了问题，那么具体是PGA的哪一块出了问题呢？这就得理清楚PGA有些component,有四块组成：

1, User Session Memory,存储session涉及到的一些变量，及session相关的信息，比如登录信息。如果在MTS模式下，这块内存是共用的。

2，Private SQL Areas,存储邦定变量相关信息以及跑SQL时用到的一些临时内存，数据结构。根据执行SQL(cursor)的生命周期，一条SQL(cursor)涉及到的Priviate SQL Areas可分为两部：
1)：持久内存，保存Cursor整个生命周期的相关信息，比如邦定信息，这内存一直存在，直到Cursor关了。
2)：执行内存，当cursor执行完了(不是cursor关了)，就释放相应的内存。其实也就是执行时，用到的一些临时内存堆。这部分内存大小取决于执行的类型(select操作，还是DML操作，通常select操作要比DML占用更多的内存,特别当select操作涉及到sort),以及SQL的复杂程度,和要处理的数据量。
当MTS模式时，一部分Private SQL Areas在SGA，特别是持久内存这部分,放在SGA里。专用模式则所有的内存都在PGA里。
第2)部份内存是当执行完就释放，第1)部份内存则只有当cursor关了才会释放。
一个进程允许分配多少个Private SQL Areas,是通过参数open_cursor来控制的.

3，SQL Work Areas
这部份内存，可以理解为指针，指向Private SQL Areas的数据结构。

4,Cursor,SQL Areas
SQL执行中，有些操作会消耗比较多的内存，如：Sort Operations, Hash join, Bitmap merge以及Bitmap create.对于这些操作专门开设的内存来处理这类情况，其实可以认为他属于Private SQL Areas里的第2)部份，只是这些操作要消耗比较多的内存，所以单独列出来了。

从上面的分类中，其实可以看出当报ORA-04030时，PGA分配出问题的，很可能出在Private SQL Areas这里，User Session Memory和SQL Work Areas内存占用会比较少的，而Cursor,SQL Areas这部份虽然会占用比较大的内存，但在通常情况下，当如果内存不足时，会使用临时表空间，所以也不会出现内存不足和分配上的问题。所以要把重点放在Private SQL Areas这里，是不是内存使用过量。那接下来的问题，怎样才能查看到这部分内存的使用情况？ 这就要用到events来把相应的信息生成trace文件，然后分析trace文件.
在生成trace文件之前，我们还得就内存的状态分析一下，通常情况内存的回收是根据内存块(Chunk)的状态来执行的，通常情况下，内存有如下状态：
1) free,就是表示目前这内存块没有存储信息，处理以可以马上分配给请求者。
2) freeable,则表示该块存储了信息，但是临时信息，所以经过相关处理，可以让当前占用者释放该内存，然后分配给另一个请求者的。
3) recreate,则表示该块存储了信息，但这些信息可以很快重新构造出来的，所以，当没有内存可分配时，可以让当前占用者通过一定的机制释放该内存，也是可以分配给请求者。
4)Perm,表示永久性占用内存，处于整个生命周期中。

其实，这个跟shared pool里的一样。

生成trace文件，用如下命令：
alter system set events '4030 trace name errorstack level 3;name HEAPDUMP level 536870917';--这个数字不知是怎么计算出来的，只是Oracle support人说，这个是查看PGA的。536870912则是SGA
执行报4030错误的SQL
alter system set events '4030 trace name errorstack off';
alter system set events '4030 trace name HEAPDUMP off';

这样就可以从user_dump_dest里找到trace文件。根据错误：

SQL Error: ORA-04030: out of process memory when trying to allocate 31109888 bytes
(kxs-heap-c,temporary memory)
04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)"

从这个错误提示里，可以看到kxs-heap-c这个堆出问题了，通过分析trace,可以发现这样的信息：
EXTENT 0 addr=11e861e18
Chunk 11e861e28 sz= 16288 free " "
Chunk 11e865dc8 sz= 16408 freeable "kxs-heap-c " ds=110450070
Chunk 11e869de0 sz= 16408 freeable "kxs-heap-c " ds=110450070
Chunk 11e86ddf8 sz= 16408 freeable "kxs-heap-c " ds=110450070
这里的sz=16408，就是代表相应内存块的大小，根据这推断可以计算出这一内存堆的大小：
grep -i chunk _ora_2732202.trc | grep 'kxs-heap-c' | awk 'BEGIN{sum=0}{sum=sum+$4}END{print sum}'
203445176
可以看出当出问题时，kxs-heap-c已经占用了200M内存了，根据上面分析，可以进一步查看每个状态下的内存各是多少，在trace文件里，可以找到：
SUBHEAP 1: desc=110450070
******************************************************
HEAP DUMP heap name="kxs-heap-c" desc=110450070
extent sz=0x4000 alt=32767 het=32767 rec=0 flg=2 opc=2
parent=110192880 owner=0 nex=0 xsz=0x0
EXTENT 0 addr=11e865de0
Chunk 11e865df0 sz= 3944 perm "perm " alo=3944
Chunk 11e866d58 sz= 12424 free " "
....
Total free space = 11003288
....
Permanent space =190911712
FIVE LARGEST SUB HEAPS for heap name="kxs-heap-c" desc=110450070
******************************************************
分析这段信息，可以看出这个kxs-heap-c堆的内存，只有free,freeable,perm,而free+freeable=11003288,perm=190MB! 一个perm已经占用了190M了，从这里可以看出，如果不报4030错误，Perm应还会不断增长的。这跟文档5391505.8所描述的bug5391505是吻合。从错误的信息来看,Oracle没能从kxs-heap-c分配31109888 bytes,而在kxs-heap-c中free space是11K左右,显然不能满足请求,报错也就是自然而然的事了.

我们知道，通常情况下，一个进程占用的PGA是有大小限制的，一般为max(100MB,pga_aggregate_target*5%)。而目前PGA的5%是：
SQL> select 943718400/1024/1024*0.05 from dual;

943718400/1024/1024*0.05
------------------------
45

从这里可得出，单个进程占的PGA已经超上了阀值。从这一点也可以佐证PGA分配出问题了.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45188/viewspace-1026283/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45188/viewspace-1026283/