Re: ORA-04030 beating
- From: "Yaping Chen" <hangzhoumaster@xxxxxxxxx>
- To: dl1tech@xxxxxxxxx
- Date: Sat, 22 Nov 2008 23:17:58 +0800
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@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@chen ~]$ sqlplus test/test@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@xxxxxxxxx>:
>
> 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
>
--
Regards,
Yaping Chen
http://yaping123.wordpress.com
Other related posts:
