RE: ORA-04030 beating
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 21 Nov 2008 13:54:53 -0500
The maximum size of a pl/sql table (array) used to be OS dependent. I
can remember when we tested and found the limit was only around 10M on a
Pyramid on 7.0 or maybe 7.1.
I image that the amount of available free real memory on the client may
also be important.
Is the client where the task is failing at 111M a Windows or UNIX box?
Is the client version the same as the database version?
Interested in what others can add.
-- Mark D Powell --
Phone (313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Darrell Landrum
Sent: Friday, November 21, 2008 12:05 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: ORA-04030 beating
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
Other related posts:
