服务器都128G内存，还报ora04030。郁闷，求助！

huanhuanlove

估计跟这个sql关系不是很大  可能还是哪儿设置的问题

nailxu

Alter System Set "_pga_max_size"=12288M;

SELECT ksppinm, ksppstvl, ksppdesc
  FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm IN ('_pga_max_size', '_smm_max_size');

_pga_max_size        12884901888
_smm_max_size       6291456


可是还报同样的错。

在toad中监控该进程，
SELECT   SID, NAME, VALUE,substr(value/1024/1024/1024,1,4)||'G'
    FROM v$statname n, v$sesstat s
   WHERE n.statistic# = s.statistic# AND NAME LIKE 'session%memory%'
ORDER BY 3 deSC;
总是当
session pga memory max 到达3.99G
session uga memory max 到达3.99G

时程序崩溃！
限制到底在哪里？

Yong Huang

I looked at the bug yezhibin mentioned. It would be triggered by bulk insert, which is not what you did.

Try setting pga_aggregate_target to a larger number, say, 100 GB (so 5% of it 5 GB).

On the other hand, it's unusual for a SQL to require so much PGA. Is it possible you break it up into smaller pieces and process them one piece at a time? The subquery factoring creates poi_keywords temporarily. How about forcing it to be a temporary segment by putting /*+ materialize */ hint in the subquery?

But in the whole SQL, the part that really uses too much PGA may be the XML processing part. I'm told there're two general methods to parse XML (not limited to Oracle or Java), one taking the whole XML document in, fast but with lots of memory, the other reading the document one chunk at a time, slow but with much less memory. But I don't know how to adapt that philosophy into Oracle XML processing.

Yong Huang

magic007

看上去你这个SQL使用了XML，而在SQL执行时，会生成大量的临时的XML对象存储在PGA中。
不知道为什么要生成几百万行的XML数据，生成XML可以考虑使用客户端来实现。

sundog315

好强大的数据量

nailxu

可是PGA已经很大了，应该能放得下这些数据了呀。
可为何还不行呀。

yezhibin

通常ora-4030的错误有三种情况：
1、OS内存限制
2、内存泄露bug
3、分配太多对象

用PGA heapdump确认一下subheap内容：
SQL>oradebug setospid xxxxx
SQL>oradebug dump headdump 536870913

(备注：536870913=0×20000000+level 1
       0×20000000: dump top 5 subheaps 内容）
     level 1: PGA
)

然后egrep "HEAP DUMP|FIVE LARGEST|^SUBHEAP|Subheap|^Total"  xxxxxx.trc

看占用最多PGA前5个PGA是什么？

yezhibin

另外忘了重复一下，先看以下内容是否和v$sesstat值匹配，如果不匹配，再用上面heapdump看多余的部分是什么？

select category, allocated, used, max_allocated
from v$process_memory
where pid = (
              select        pid
              from        v$process
              where addr = ( select        paddr
                        from        V$session
                        where        sid = 你的SID))

select pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
from v$process
where addr = (select  paddr
               from        V$session
               where sid = 你的SID);

nailxu

原来是个BUG：
Bug 5883585 - ORA-4030 from long running XMLAGG query [ID 5883585.8]
Bug 5883585  ORA-4030 from long running XMLAGG query
This note gives a brief overview of bug 5883585.
The content was last updated on: 11-DEC-2009
Click here for details of each of the sections below.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11  
Versions confirmed as being affected 10.2.0.4

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 10.2.0.4 Patch 5 on Windows Platforms
10.2.0.5 (Server Patch Set)
11.1.0.6 (Base Release)

升级到10.2.0.5后就不报错了。早听二楼的就好了，呵呵！感谢大家的关心和指导！


[ 本帖最后由 nailxu 于 2010-6-21 14:29 编辑 ]

maolinxie

关注