ORA-04030 beating
Date: Thu, 20 Nov 2008 23:04:47 -0600
Message-Id: <C55C8F84-36DD-4106-85B1-B95768706D8C@gmail.com>
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_at_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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 20 2008 - 23:04:47 CST