ORA-04030 error - suggested resolutions?

[MCubitt]

Users accessing our Oracle DB via web portals receive an error message:
ORA-04030: out of process memory when trying to allocate 51616 bytes (PLS non-lib hp, pdz7M04_Create)

Any suggestions for where I can start looking for a resolution, please?

Thanks


Oracle 9i on a AIX 5.1 box.




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[HimanB]

Hi,
You may look for Note:233869.1 Titled "Diagnosing and Resolving ORA-4030 errors" at Metalink Site.

HTH
Himanshu

 

[MCubitt]

I would love to, but our Oracle license has such restrictions that I am unable to register to Metalink.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[HimanB]

Hi,
No probs,I am pasting the extrat from Document below.

Regards
Himanshu

Diagnosing and Resolving ORA-4030 errors

What does an ORA-4030 mean?
This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

The ORA-4030 thus indicates the process needs more memory (stack UGA or PGA) to perform its job.

What causes this error?
Since you run into this error, you can't allocate memory from the operating system. This could be caused by your process itself, like your process is just requesting too much memory, or some other reasons cause the operating system memory to be depleted, like a too big SGA or too many processes to be accomadated for the systems virtual memory (physical memory + swap space). Many operating systems impose limits on the amout of memory a single process can acquire to protect itself.

This leads to the following questions:

Is there still sufficient memory available?
Is_there_an_operating_system_limit_set?
Is there an oracle limit set?

Which process is requesting too much memory?
How to collect information on what the process is actually doing?
These will be discussed in the subsequent sections.

Additional topics:

General suggestions on avoiding this error
References

Is there still sufficient memory available?
To answer this question, we will need to use operating system specific utilities to examine the memory usage.

OpenVMS systems : show memory will give you information about physical memory and pagefile usage:
Physical Memory Usage (pages): Total Free In Use Modified
Main Memory (256.00Mb) 32768 24849 7500 419

.....

Paging File Usage (blocks): Free Reservable Total
DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS 30720 30720 39936
DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS 226160 201088 249984
DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS 462224 405296 499968

As a general guideline, the sum of the free space in the pagefile(s) should not get lower than half the sum of the total space.
The swapfile(s) should almost be unused, free space should be about the same as the total space.


Windows systems: Check MEM Usage in the performance tab in Task Manager.


Unix systems: Every unix flavour usually has its own utilities to check for the global memory usage on the system like top, vmstat,... And memory management does work differently on each OS.

top usually displays physical memory and swapspace statistics.
swapon -s displays swapspace usage
vmstat displays free physical memory
Sample top output on Linux:

top - 10:17:09 up 1:27, 4 users, load average: 0.07, 0.12, 0.05
Tasks: 110 total, 4 running, 105 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.3% user, 1.6% system, 0.0% nice, 98.0% idle
Mem: 1033012k total, 452520k used, 580492k free, 59440k buffers
Swap: 1052248k total, 0k used, 1052248k free, 169192k cached
.....



When sufficient memory is available, check for operating system enforced limits. When the memory has been depleted, we will have to find out where the memory has been used for.

Is there an operating system limit set?
When there still seems to be plenty of virtual memory left, we might not be allowed to use the amount of memory we are asking for. Check for limits imposed by the operating system.


OpenVMS systems:
To check for the amount of physical memory you can use, check the working set quotas and the pagefile quota with the authorize utility. Refer to the reference for OpenVMS section on what quotas are used and how to modify them. Depending on what process and how it is started, the quotas used will not be those of the oracle account. Show process/id=<process id>/quota will show you how much quota is left for a process
UAF> show oracle7

Username: ORACLE7 Owner: Oracle7 DBA
Account: SUPPORT UIC: [200,2] ([SUPPORT,ORACLE7])
CLI: DCL Tables: DCLTABLES
Default: DISK$BOBBIE_USER1:[ORACLE7]
LGICMD: LOGIN
Flags:
Primary days: Mon Tue Wed Thu Fri
Secondary days: Sat Sun
No access restrictions
Expiration: (none) Pwdminimum: 6 Login Fails: 0
Pwdlifetime: (none) Pwdchange: 3-DEC-1997 15:38
Last Login: 27-MAY-2003 14:54 (interactive), 26-MAY-2003 16:15 (non-interactive)
Maxjobs: 0 Fillm: 1200 Bytlm: 180000
Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0
Maxdetach: 0 BIOlm: 500 JTquota: 8192
Prclm: 20 DIOlm: 500 WSdef: 2500
Prio: 4 ASTlm: 4000 WSquo: 4096
Queprio: 0 TQElm: 4000 WSextent: 30000
CPU: (none) Enqlm: 18000 Pgflquo: 750000
Authorized Privileges: .....

$ sho proc/id=20200139/quota

24-JUN-2003 12:30:54.39 User: ORACLE7 Process ID: 20200139
Node: BOBBIE Process name: "ORA_BOB901_PMON"

Process Quotas:
Account name: SUPPORT
CPU limit: Infinite Direct I/O limit: 100
Buffered I/O byte count quota: 9994816 Buffered I/O limit: 100
Timer queue entry quota: 99 Open file quota: 29997
Paging file quota: 145968 Subprocess quota: 10
Default page fault cluster: 64 AST quota: 496
Enqueue quota: 49995 Shared file limit: 0
Max detached processes: 0 Max active jobs: 0


Windows systems:
On Microsoft windows operating systems, oracle processes are implemented as threads in 1 process. The addressable amount of memory is 2Gb (including stack, PGA, SGA). This limit can be increased to 3Gb or higher. See <Note:46001.1> "Oracle Database and the Windows NT memory architecture, Technical Bulletin" for more information. The total memory used by the oracle process, excluding process stack and code, can be determined with this query.

Unix systems:
use the limit / ulimit shell builtin commands. Be aware that unlimited might not really mean unlimited but actually an historical limit like 2Gb. Setting a real amount is preferred:
Sample Linux output:

aroelant@aroelant-be:~> ulimit -a

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited

It is possible the limit is just set too low and will need to be increased. It might be we just want to use too much.

Is there an oracle limit set?
As from Oracle Version 9i, there is a parameter implemented which limits the total amount of PGA that can be allocated for an instance. <Note:223730.1> "Automatic PGA Memory Managment in 9i" provides more information on this issue. The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:

SQL> select
sum(bytes)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';

Which process is requesting too much memory?

Some operations will require lots of process memory like huge PL/SQL tables or big sort operations. In these cases, the processes will run for a certain period of time before getting the ora-4030 error, hopefully allowing us to find out where and why the memory is allocated. You can use the following query to find out oracle's idea of PGA and UGA size for the oracle processes.

SQL> col name format a30SQL> select
sid,name,valuefrom v$statname n,v$sesstat swhere n.STATISTIC#
= s.STATISTIC# and name like 'session%memory%'order by 3 asc;
This query will show the most hungry process last in the list.

It's usually a good idea to confirm the process memory usage from the Operating System point of view. After all, it might no be an oracle server process that is using too much memory.Usually, for server processes, oracle and the operating system more or less agree on memory usage. The following command will allow you to find out the memory usage for processes from the operating system.


OpenVMS systems: Show system will give you an overview of processes and resource usage. Processes wich are heavily pagefaulting usually use lots of virtual memory. The pages column indicates the use of physical pages. The show process/continious command gives physical (working set) and virtual memory usage.

$ show system/page


OpenVMS V7.2-1 on node BOBBIE 13-JUN-2003 09:56:30.44 Uptime 17 18:58:18
Pid Process Name State Pri I/O CPU Page flts Pages
20200101 SWAPPER HIB 16 0 0 00:00:02.45 0 0
20200106 CLUSTER_SERVER HIB 13 104 0 00:00:00.03 87 104
20200107 CONFIGURE HIB 10 21 0 00:00:00.06 77 17


$ sho process/id=xxx/cont:


Process AROELANT
State CUR Working set 131

Cur/base priority 6/4 Virtual pages 11714

Current PC 800D9B28 CPU time 0 00:00:01.28

Current PSL 00000003 Direct I/O 178

Current user SP 7A5227F0 Buffered I/O 962

PID 20200469 Page faults 1312

UIC [SUPPORT,AROELANT] Event flags C0000003
&nbs C0000000

Windows systems: On Microsoft windows systems, oracle is implemented by using threads in a single oracle process. Up to now, i didn't find a way to view the memory usage per thread. We can however check if oracle and the operating system agree on the memory used by oracle. For the Operating system point of view, we can use task manager. Use the view pusbutton and select Select Columns... make sure Virtual Memory Size is selected. The size mentioned in the VM Size column for oracle.exe should match the sum of SGA, total PGA memory and the process stack and code size. The following query can be used to get the memory size as viewed by oracle, however, not including the process stack and code size:
select sum(bytes)/1024/1024 Mb from
(select bytes from v$sgastat
union
select value bytes from
v$sesstat s,
v$statname n
where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory'
);

MB
----------
517.296406


On my system, this is about 30 Mb lower than the VM size seen with task manager.
When you have determined that oracle is the process using the memory, this query will show you which session uses the most.


Unix systems: The top utility is a usefull tool here, as you can customize display and sort key. The ps command will be available on most systems, but the possibilities will vary. For example, on Linux, 'ps -AF --sort resident' will list all processes with those having the biggest resident set size last. Also refer to <Note:174555.1> "UNIX: Determining the Size of an Oracle Process".


How to collect information on what the process is actually doing
This section will only discuss Oracle server processes. You should have determined with the methods discusses in the previous sections, that one or more oracle server processes is responsible for the memory consumption. Remember it is not always the process getting the ORA-4030 that is responsible for the memory consumption. It happens to be the process not getting the memory it requests.

For processes steadily increasing their memory, we can have a look while it runs.

You can check in v$sql_area what is beeing executed with the following query:

SQL> select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = <SID>;

We can force a heapdump and have it examined by oracle support services:
SQL> oradebug unlimit
SQL> oradebug setorapid 10 (this is for the oracle pid, use setospid for the os process id)
SQL> oradebug dump heapdump 7

When the problem is intermittend or some process fails too fast to examine and it is most likely the cause of the memory consumption, we can use events to get a heapdump when the process hits the error:

SQL> alter session set events '4030 trace name heapdump level 25'; or set this event in the databases init.ora.


href="

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=21234.1"

class="moz-txt-link-rfc2396E"> <Note:21234.1> EVENT: 10261 "Limit the size of the PGA heap"

This dump can be used by Oracle Support analysts to find the cause of the excessive memory allocation.

General suggestions on avoiding this error
As mentioned before, some operations just require a lot of memory. For sort issues, decreasing SORT_AREA_SIZE can help. The Oracle server process will allocate SORT_AREA_SIZE bytes in the PGA for sort operations. When more memory is required to complete the search, the server process will use a temporary segment. This means that lowering SORT_AREA_SIZE can have a performance impact on queries requiring huge sort operations.

With 9i and higher, the automatic SQL execution memory management feature is enabled by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET in the initialization file. Using automatic PGA memory management will help reduce the possibility of ORA-4030 errors. Refer to the following notes for more details:

<Note:262946.1> "Performance Issues After Increasing Workload",
<Note:223730.1> "Automatic PGA Memory Managment in 9i",
<Note:223299.1> "Top Oracle 9i init.ora Parameters Affecting Performance"


PL/SQL procedures can also allocate lots of memory, so it might be required to rewrite some parts of your application. While a PL/SQL table is easy to use, it does require memory to be allocated in the PGA.
Review the optimizer strategy, some access paths might need more memory due to sort operations, the use of functions on more rows,...

On some operating systems, like Microsoft windows, the size of the SGA might be decreased to allow bigger PGA's.

Make sure your operating system and oracle limits are set reasonably.
Make sure there is enough memory available (physical memory and swapspace)

References

General:

<Note:237899.1> Resolving ORA-4030 Errors After Upgrading

NT:

<Note:116076.1> Tackling ORA-4030 on WindowsNT
<Note:46001.1> Oracle Database and the Windows NT memory architecture, Technical Bulletin


Unix:

<Note:199746.1> How to Resolve ORA-4030 Errors on UNIX (unix specific but general enough for some suggestions)
<Note:174555.1> UNIX: Determining the Size of an Oracle Process

 

[sem]

Himanshu, don't you think that you violate Oracle Copyright by publishing their notes?

Have you read this

http://metalink.oracle.com/metalink/plsql/ml2.show_legal_text

?

I should ask you to be more lawful, because this site owners may suffer from your carelessness.

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Him,

Thank you very much for your kind offer of help. It's very much appreciated.




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[HimanB]

Thanks Dima,
I was not aware of this.
I'll take care in future.

Regards