ORA-04030 beating

From: Darrell Landrum <dl1tech_at_gmail.com>
Date: Thu, 20 Nov 2008 23:04:47 -0600
Message-Id: <C55C8F84-36DD-4106-85B1-B95768706D8C@gmail.com>

Good day folks,
Id like to get your thoughts on this issue. (What else would you check?)

First the issue, then Ill 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, on AIX 5.3, ASM for database storage (with its own 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_at_sid).
Oh, and I have 2 different RAC implementations, both 2 node, both, ASM, etc. that I can reproduce this issue on.

Thank you!
Darrell Landrum

Received on Thu Nov 20 2008 - 23:04:47 CST

Original text of this message