ORA-04040问题诊断

ORA-04030问题分析一、什么是ORA-04030？ORA-04030: 在尝试分配 8389132 字节 (pga heap,redo read buffer) 时进程内存不足当oracle无法从操作系统上分配内存给server process时，就会报4030错误。一般有以下几种原因：1、操作系统内存不足（包括虚拟内存和交换）2、操作系统有内存方面的限制，例如内核限制和用户shell限制3、应用程序设计有问题（内存泄露等）4、bug二、如何分析4030产生的原因？查看操作系统的内存及交换（虚拟内存）使用情况Windows 32bitwindows的内存使用方式与UNIX系统有个非常明显的不同，UNIX系统都是尽量多的使用内存，内存不够时才会使用交换空间，而windows则不然，每个应用程序运行时都会使用到虚拟内存，即使还有很多的空闲内存。在我的电脑属性中可以检查操作系统的总内存和总虚拟内存。在进程管理器中可以查询ORACLE.EXT使用的内存和虚拟内存。AIXAIX上可以使用topas检查当前内存和交换的使用情况，如下：MEMORYReal,MB 3920% Comp 91% Noncomp 6% Client 6PAGING SPACESize,MB 4608% Used 7% Free 93也可以使用lsps检查交换空间情况，例如：$ lsps -aPage Space Physical Volume Volume Group Size %Used Active Auto Type Chksumpaging00 hdisk0 rootvg 4096MB 5 yes yes lv 0hd6 hdisk0 rootvg 512MB 28 yes yes lv 0HP-UXHPUX可以使用TOP检查内存和交换使用情况，如下：Memory: 1525128K (1456736K) real, 1704176K (1595932K) virtual, 581836K free Page# 1/5LINUXLINUX也可以使用TOP命令检查内存和交换使用情况，如下：Mem: 7801704k total, 6267772k used, 1533932k free, 414392k buffersSwap: 10241396k total, 0k used, 10241396k free, 3785672k cached2、检查操作系统的限制在操作系统的内存（虚拟内存）和交换空间还充足的情况下，遇到4030错误，则可能是受到了操作系统的限制。WINDOWS 32位在一个windows 32位的系统上，无论实际内存有多大，其虚拟内存地址只有4G，且这4G的虚拟内存地址中有2G是为操作系统自身保留的，即一个用户进程（oracle.exe）最多能对2G的虚拟内存进行寻址，实际情况下，ORACLE.EXE（SGA,PGA,process stack and code）只能使用到1.7G甚至更少。查询当前SGA+PGA使用：select sum(bytes)/1024/1024 Mb from ( select bytes from v$sgastat union select value bytes from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and = session pga memory ); LINUX 、AIX、HPUX的用户shell限制如果用户使用的是C shell，则使用limit；如果用户使用的是Bshell或者Kshell，则使用ulimit； 使用ulimit查看当前用户的shell限制 $ ulimit -atime(seconds) unlimitedfile(blocks) unlimiteddata(kbytes) unlimitedstack(kbytes) 4194304memory(kbytes) unlimitedcoredump(blocks) unlimitednofiles(descriptors) unlimitedthreads(per process) unlimitedprocesses(per user) unlimited可选以下option，有些option在有些操作系统上无法使用 -c Maximum size of core files. -d Maximum size of data segment or heap. -f Maximum size for files written by child processes. -m Maximum size of physical memory. -n Maximum number of file descriptors. -s Maximum size for the stack area. -t Maximum number of seconds to be used by each process. -v Maximum size of virtual memory. -w Maximum size of swap area. -H The limits specified for the resources are hard limits. -S The limits specified for the resources are soft limits. (the default if neither -H or -S is specified.在linux上跟4030错误有关的是以下三个参数data seg sizemax memory sizevirtual memory在AIX和HP上跟4030错误有关的有以下两个参数datamemory注意：对于RAC，如果oracle实例和监听是通过crs启动的，那么还需要检查root用户的限制。 使用ulimit修改当前用户限制，以LINUX上修改内存大小为例： roots1-11g # ulimit -a |grep memory max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited virtual memory (kbytes, -v) unlimited roots1-11g # ulimit -m 2147483648 roots1-11g # ulimit -m 2147483648 roots1-11g # ulimit -a |grep memory max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) 2147483648 virtual memory (kbytes, -v) unlimited roots1-11g # ulimit -m unlimited roots1-11g # ulimit -a |grep memory max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited virtual memory (kbytes, -v) unlimited注意：使用ulimit设置的参数只对当前登录用户有效。因为shell限制导致的4030错误，比较常见的是AIX上，在AIX上修改/etc/security/limits使参数永久生效，例如：oracle: data = -1 3、pga过小导致每个进程可以使用的pga内存过小设置了PGA_AGGREGATE_TARGET以后，每个进程PGA内存的大小也是受限制的： 串行操作时，每个进程可用的PGA内存为MIN(PGA_AGGREGATE_TARGET * 5%, _pga_max_size/2)，其中隐含参数_pga_max_size的默认值是200M，建议不要随便修改其值。假设PGA设置为800M，那么在串行操作时，每个进程最大可用的内存为40M。 并行操作时，并行语句可用的PGA内存为PGA_AGGREGATE_TARGET * 30% / DOP （Degree Of Parallelism 并行度）。4、检查哪个会话使用最多的PGAselect sid,name,valuefromv$statname n,v$sesstat swheren.STATISTIC# = s.STATISTIC# andname like session%memory%order by 3 asc;三、避免或解决4030错误的建议1、在WINDOWS 32操作系统上，可以设置更小的SGA（如果没有使用SGA_TARGET，就减小buffer ca