Re: ORA-04030 beating

From: Yaping Chen <hangzhoumaster_at_gmail.com>
Date: Sat, 22 Nov 2008 23:17:58 +0800
Message-ID: <170d3ad90811220717y3a5fd8b9u705abd43a828814d@mail.gmail.com>

Hi Darrell,

The process's(session) environment variable & process address space inherits from different process when
connect db locally and remotely respectively. I think your issue maybe caused by it.

On Linux
[oracle_at_chen ~]$ sqlplus test/test

oracle   10486 10485  0 21:39 pts/1    00:00:00 -bash
oracle   10512 10486  2 21:39 pts/1    00:00:00 sqlplus
oracle   10513 10512  2 21:39 ?        00:00:00 oraclechen

(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

10486 --clone-> 10512 --fork-> 10513

[oracle_at_chen ~]$ sqlplus test/test_at_chen

oracle   10486 10485  0 21:39 pts/1    00:00:00 -bash
oracle   10518 10486  1 21:43 pts/1    00:00:00 sqlplus
oracle   10520     1  1 21:43 pts/1    00:00:00 oraclechen (LOCAL=NO)

10486 --clone-> 10518

2008/11/21, Darrell Landrum <dl1tech_at_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
>

-- 
Regards,
Yaping Chen

http://yaping123.wordpress.com

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Nov 22 2008 - 09:17:58 CST