WORKAREA_SIZE_POLICY参数引起的ORA-04030错误

刚公交车上现场的工程人员打电话说在一个版本为10.2.0.4的AIX RAC上执行一个多表的join查询语句最近老是收到ORA-04030的错误（有时候又没报错），能够确定的是AIX操作系统对资源没有任何限制，内存足够的大，PGA设置是足够的，查询的数据加了WHERE条件，只有过滤出5天的数据。     回到家中吃了饭开始跟同事QQ上一同处理问题。 1).首先让同事尝试把PGA调大，不过没有效果一样报错，又改回来了。 2).SELECT中包含有GROUP BY语句，让同事去掉之后不报错，或者查询小于等于3天的数据也不报错，怀疑是否是数据量过大造成的PGA分配不够。 3).查询V$TEMPSEG_USAGE没有任何记录。 4).使用下面的语句查询隐含参数"_pga_max_size"的值。 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm LIKE '%&par%' 输入pga_max_size 查询结果是： NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- DESCRIB -------------------------------------------------------------------------------- _pga_max_size 209715200 Maximum size of the PGA memory for one process 每个进程能消耗的PGA内存最大值为200M，执行该SQL应该不会操作该值。 5).让同事查询了PGA的统计信息： select name,round(value/1024/1024) mb from v$pgastat; 查询结果是： aggregate PGA target parameter 512 aggregate PGA auto target 280 global memory bound 100 total PGA inuse 201 total PGA allocated 416 maximum PGA allocated 3227 total freeable PGA memory 41 process count 0 max processes count 0 PGA memory freed back to OS 209965 total PGA used for auto workareas 0 maximum PGA used for auto workareas 719 total PGA used for manual workareas 0 maximum PGA used for manual workareas 192 over allocation count 0 bytes processed 427185 extra bytes read/written 99480 cache hit percentage 0 recompute count (total) 1 PGA的当前值为512M，最大的时候达到过3227M，不过当前PGA空间应该是足够的。 6).尝试找出SQL查询一天数据花费的PGA空间。     让同事登录一个新的会话，先执行报错SQL查询一天的数据，这是不会报错的，然后立即执行以下的SQL找出分配给会话PGA的空间。 select v2.name, v1.value   from v$mystat v1, v$statname v2  where v1.statistic# = v2.statistic# and upper(v2.name) like '%PGA%'; 得到的结果是： 1 session pga memory 6215360 2 session pga memory max 41735872     折算下来查询一天的数据花费的PGA空间在6M左右，这个值乘以5倍也不算大啊，既没有操作单进程最大PGA的限制，也没有超过总的PGA空间的“假”限制。 7).使用putty连接到服务器，使用sqlplus / as sysdba方式登录到数据库实例，执行该SQL不会报ORA-04030的错误。     开始觉得素手无策了，于是又开始在METALINK上看文档，找到一篇详细记录ORA-04030错误的文章，已经分享到我的blog中，<a href="http://space.itpub.net/23135684/viewspace-712768">http://space.itpub.net/23135684/viewspace-712768</a>     在文章中看到一些bug，前面已经证实，遇到bug的可能性比较小。看了“Can you control the size of a process?”这小节之后尝试对进程使用的PGA进行手动的控制，于是在执行SQL语句执行执行如下的SQL： ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL; 然后再次执行SQL居然不报错了！ 文章上说当设置了PGA_AGGREGATE_TARGET参数之后，所有*_AREA_SIZE参数将被忽略。于是执行ALTER SYSTEM SET WORKAREA_SIZE_POLICY=MANUAL;在系统级别将该参数设置为手动模式（PGA_AGGREGATE_TARGET,WORKAREA_SIZE_POLICY两个参数都是可动态调整的参数），并且将PGA_AGGREGATE_TARGET参数从原有的512M调整为3G。     有关PGA_AGGREGATE_TARGET和WORKAREA_SIZE_POLICY之间的关系可以参见下面的描述： <span style="widows:2;text-indent:0px;border-collapse:separate;font:medium Simsun;white-space:normal;orphans:2;letter-spacing:normal;color:#000000;word-spacing:0px;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-size-adjust:auto;-webkit-text-stroke-width:0px;" class="Apple-style-span"> PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance. Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater. Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters. Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk. When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.     另外，同事反应excel程序使用oracle for oledb驱动程序连接RAC之前也会遇到ORA-04030的错误，oracle for oledb的客户端版本是10.2.0.1，考虑到在该版本的oracle问题比较多，于是将客户端的版本从10.2.0.1升级到10.2.0.5。     当出现问题素手无策的时候，多看看原理性的文章，可能对我们解决问题会有帮助。     更详细的了解ORA-04030的错误可参考文章：        《再次遇到ORA-04030的错误》：<a href="http://blog.itpub.net/23135684/viewspace-1769128/" target="_blank">http://blog.itpub.net/23135684/viewspace-1769128/</a> --end-- <div> </div>

更多相关文章

随机推荐