Error | resolution
Analyze and resolve ora-4030 errors
What does ORA-4030 mean?
This error means that the Oracle server process cannot obtain more memory from the operating system. The memory here refers to the PGA (Program Global Area) and its subkeys that are determined by the configuration. For dedicated server processes, memory includes the stack area, UGA (User Global area). UGA includes user session data, cursor information, and sorting area. In a multithreaded configuration (shared server), UGA is in the SGA (System global Area), and it does not cause ora-4030 errors.
Therefore, ora-4030 means that the process requires more memory (stacks, UGA, or PGA) to perform its work.
What caused this error?
This error indicates that the operating system cannot allocate enough memory. This error may be caused by your process itself, such as your process requiring too much memory, or some other reason for operating system memory exhaustion, such as the SGA is too differentiated or too many processes compete system virtual memory (physical memory + swap partition). Many operating systems limit the memory that a process obtains to ensure system stability.
Use the following procedure to check your system:
· Do you still have enough memory to allocate?
· Is there a limit to the operating system?
· Is there a limit to Oracle databases?
· Which process requires too much memory?
· How do I gather information about what the process (which requires too much memory) is doing?
These will be discussed in the next section.
Further discussion topics:
· General recommendations for avoiding this type of error
· Reference
Do you still have enough memory to allocate?
To answer this question, we need to use the operating system-specific tools to detect memory usage.
1.OpenVMS System: Displays information that tells you about physical memory and paging file usage.
Physical Memory Usage (pages):
Total free at use Modified Main Memory (256.00Mb) 32768 24849 750 0 419
......
Paging File Usage (blocks):
Free reservable Total
Disk$bobbieaxpsys:[sys0. Sysexe]swapfile. SYS 30720 30720 39936 disk$bobbieaxpsys:[sys0. Sysexe]pagefile. SYS 2261 60201088 249984 disk$bobbie_user3:[sys0. Pagefile]pagefile. SYS 462224 405296 499968
As a general rule, the total amount of free capacity in the paging file should be no less than half the total capacity. The exchange file should hardly be used, the idle capacity should be almost as much as the total volume.
1.Windows system: View memory usage in Task Manager.
2.Unix system: Each UNIX system has its own tools to detect all memory usage, such as top,vmstat ... and each system is different.
o top is commonly used to display physical memory and swap space.
o swapon–s Display swap space usage
o vmstat display of idle physical memory
Sample top output on Linux:
The output example of "Top" on Linux:
top-10:17:09 up 1:27, 4 users, load average:0.07, 0.12, 0.05
TASKS:110 Total, 4 running, 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
.....
If you have enough memory, check to see if the operating system has a mandatory limit. If the memory is depleted, we need to find out where the memory is being used.
Is there a limit to the operating system?
If there is still sufficient virtual memory remaining, we may not be able to use the part of the memory requested. Check your operating system for restrictions.
1.OpenVMS System: To check the total amount of physical memory you can use, check the work (page) area quota (working set quotas) and the paging file quota (pagefile quota). Check the OpenVMS use Guide to determine the quota situation and how to modify them. Depending on how the processes are used and how they are started, quota usage differs from Oracle statistics. Process/id=<process Id>/quota will show how much remaining quotas are available for a particular 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:loginflags:
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
2.Windows system: On Microsoft's Windows operating system, the Oracle process set runs as many threads of a process. The address space cannot exceed 2GB (including stacks, PGA, SGA). This limit can break through to 3GB or higher. (See Oracle documentation <NOTE:46001.1>). For information about Oracle databases and Windows NT memory Architecture, consult the Technical Bulletin board. The total memory conditions used by the Oracle process, excluding process stacks and code, can be viewed in query.
3.Unix System: Use the built-in shell command: Limit/ulimit. Note that those unlimited do not necessarily mean infinite, but may have the limitations of the old system, such as 2GB.
An example of an output on a Linux system:
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 (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 that the memory limit has been set too small to increase it. Or maybe we demands too much.
Is there a limit to Oracle databases?
After Oracle 9i, there is a parameter that determines how an Oracle instance can be allocated to the PGA total. <Note:223730.1> "Automatic PGA Memory Managment in 9i" provides more information on this. The following query can be used to find out the total amount of memory allocated to the PGA area for all sessions.
Sql> select sum (value)/1024/1024 Mb from V$sesstat s, v$statname n
Where n.statistic# = s.statistic# and name = ' Session PGA Memory ';
Which process requires too much memory? Some operations require a large amount of memory such as a large pl/sql table or a large number of sorting operations. In this case, the process will run for a period of time before returning the ora-4030 error. Hopefully we can find out which process the memory is assigned to and why it is assigned. You can use the following query to find out the operation of the Oracle database PGA and UGA.
Sql>col name format A30sql>select sid,name,value from V$statname n,v$sesstat s
where n.statistic# = s.statistic# and name like ' session%memory% ' ORDER by 3 ASC;
This query displays the memory "starvation" process in the list. From an operating system perspective, it is also a good idea to determine the memory usage of the process. In short, it is unlikely that the server process for an Oracle database uses too much memory. In general, the Oracle database and the operating system are more or less able to agree on the use of memory for the server process. The following command allows you to find out the memory usage of the process from the operating system's perspective.
1.OpenVMS system: The show system command gives an overview of how processes and resources are used. Processes that frequently invoke page failures often consume a large amount of virtual memory. The page column indicates the use of physical memory. "Show Process/continious" (in the original case, I suspect the continuous) command gives you the physical memory (Work page area) 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 10:00:53
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 C0000000
2.Windows system: For Microsoft's Windows operating system, the Oracle process set runs as many threads of a process. So far, I haven't found a way to view the memory usage of a thread. However, we can check whether Oracle is satisfied with the memory allocated by the operating system. From the operating system point of view, we can use Task Manager. Pull up the Task Manager, click the "View" button, select "Select column", in the pop-up window in the "Virtual memory size" before the tick. The virtual memory size (VM size) used by the Oracle.exe process should match the SGA, the PGA and the process stack, and the total amount of memory used by the code. The following query command gives you the amount of memory used by Oracle, however, this does not include the process stack and the amount of RAM used by the code.
Select sum (bytes)/1024/1024 Mb from (select bytes to v$sgastat union Select value bytes from V$sesstat s,v$s Tatname n where n.statistic# = s.statistic# and n.name = ' session PGA Memory '); MB
----------517.296406
On my system, the virtual memory shown in Task Manager is about 30MB larger than the amount of memory used in the query above. When you confirm that Oracle is using this memory, this query will give you the most use of the session.
3.Unix system: The "top" tool is a very useful tool that you can customize to display and sort columns. The PS command is available in most systems, but some cannot. For example, on Linux, "Ps-af--sort resident" will list the most recent maximum resident memory set (resident set) for all processes (Note II). You may also refer to <Note:174555.1> "unix:determining the Size of an Oracle Process".
How do I gather information about what the process (which requires too much memory) is doing?
This section will discuss only the Oracle server process. Using the methods described in the previous sections, you should be able to determine that one or more Oracle server processes are causing the exhaustion of memory resources. Remember that it is not always the process that causes the exhaustion of memory resources to cause ORA-4030 errors. This error simply means that the process does not have the memory resources it needs.
If the process is growing in memory, we can look at it as it runs.
O You can use the following query statement in the V$sql_area table to query what processes are executing.
Sql> Select Sql_text from V$sql_area A, v$session s
where a.address = s.sql_address and S.sid = <SID>;
o We can force a heapdump and have it examined by Oracle support services. (This sentence does not know how to translate)
sql> oradebug unlimitsql> oradebug setorapid 10 (This is the corresponding Oracle PID, with "Setospid" corresponding to the operating system's process ID) sql> Oradebug dump HEAPD UMP 7
If the problem no longer occurs, or if some processes are too fast to do so, it is likely that this is the cause of memory exhaustion. We can use the event set to get a heapdump when this process causes this error.
Sql> alter session SET events ' 4030 Trace name heapdump level 25 ';
or set this event in the Init.ora file of the database. <Note:21234.1> event:10261 "Limit the size of the PGA heap" This dump can help Oracle support analyze and identify the cause of excessive memory allocations.
General advice on how to avoid this error.
O As mentioned earlier, some operations require a large amount of memory. Reducing sort_area_size may be helpful for sorting operations. The Oracle server process allocates the Sort_area_size bytes required by the sort operation in the PGA. If you need too much memory to complete a query, the server process will use a temporary segment. This means that when a query requires a large number of sorting operations, fewer sort_area_size can make execution more compact.
O for 9i or later Oracle databases, you can turn on the automatic SQL Execution memory management feature with the device parameter Workarea_size_policy as auto, or you can specify Pga_aggregate_ in the initialization file The size of the target.
<Note:262946.1> "Performance Issues after increasing workload", <Note:223730.1> "Automatic PGA Memory MANAGM ent in 9i ", <Note:223299.1> top Oracle 9i init.ora Parameters affecting Performance"
o Pl/sql routines may also require a lot of memory, so it is necessary to rewrite this part of the query code in your application. If a pl/sql table is often used, it does allocate a chunk of memory to the PGA.
o Look at the optimization strategy again, because the sort operation may require too much memory for some access paths, the function call returns too many rows, and so on ...
O on some operating systems, such as the Microsoft WINDOWS,SGA, the size should be reduced to allow for greater memory for the PGA.
o Make sure that your operating system and Oracle database memory limits are modest.
o Be sure to have enough memory (physical memory and swap space).
Reference
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> Resolve ORA-4030 Errors on Unix (Unix specific but general enough for some suggestions)
Unix:determining the Size of an Oracle Process
Vms:
<Note:67033.1> Background Process Quotas <Note:68663.1> Dedicated server process quotas (sql*net V2.3.3, V8.0 . X) <Note:70671.1> process quotas for bequeath connections (V7, V8) <Note:68849.1> bequeath listener Process Quo Tas (V7, V8) <Note:68226.1> Listener process quotas (sql*net V2.3.3, v8.0.x)
@ Internal:
@ <Note:21234.1> event:10261 "Limit the size of the PGA heap"
@ This event is very usefull. It would cause the process to dump information when the PGA grows above the specified limit
I note one: Work (page) area (Working set): 1. A collection of user pages that must be activated in order to avoid excessive paging. 2. In order to avoid system failure, paging required the actual storage capacity.
I note two: Resident memory set (resident set): In the virtual storage system, any time in the main memory of a program's page or program section of the whole.
