Good day folks,I’d like to get your thoughts on this issue. (What else would you check?)
First the issue, then I’ll list all of the implementation specs.This is a 2 node RAC system that encounters ORA-04030, out of process memory when trying to allocate xx bytes whenever 2 things are true: 1) the session connects through the network and 2) the session reaches 111 MB of PGA. This initially manifested by an application process that tries to create an index. Since then, I created a script to test with that creates a pl/sql table and fills it with records until it either dies at 111 MB of PGA or succeeds at 364 MB (if run locally).
Sessions connected locally work fine.This is a 2 node RAC system, Oracle 10.2.0.4, on AIX 5.3, ASM for database storage (with its own 10.2.0.4 home). All services/processes run as oracle and oracle has all soft/hard settings of unlimited.
Settings in /etc/security/limits for oracle also reflect unlimited (-1).Server RAM on both nodes is 18 GB and is barely 21% used under load. Paging space is less than 1%; these nodes are really not stressed at all. Pga_aggregate_target is 5000M, maximum pga allocated from v$pgastat is 3467M. Workarea_size_policy is set to auto. ASM and the database were created with dbca after all Oracle software was installed and patched. I have an SR open on this and Oracle support was concerned about the listener. The listener was running out of the ASM oracle home and just to be sure, I reconfigured it to run out of the database oracle home with no impact. I find it striking that this occurs only when connected through the network (either from a client or even on the database server using sqlplus user@sid). Oh, and I have 2 different RAC implementations, both 2 node, both 10.2.0.4, ASM, etc. that I can reproduce this issue on.
Thank you! Darrell Landrum