Subject: | Diagnosing and Resolving ORA-4030 errors | |||
Note:233869.1 | Type: | TROUBLESHOOTING | ||
Last Revision Date: | 23-MAY-2006 | Status: | PUBLISHED | |
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:
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:
These will be discussed in the subsequent sections.
Additional topics:
Additional topics:
Is there still sufficient memory available?
To answer this question, we will need to use operating system specific utilities to examine the memory usage.
1. 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.
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.
2. Windows systems: Check MEM Usage in the performance tab in Task Manager.
3. 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.
o top usually displays physical memory and swapspace statistics.
o swapon -s displays swapspace usage
o 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
.....
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.
1. 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=/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
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
2. 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
3. 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
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.
As from Oracle Version 9i, there is a parameter implemented which limits the total amount of PGA that can be allocated for an instance.
SQL> select
sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';
sum(value)/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 a30 SQL> 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 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.
1. 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 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
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 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
(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.
When you have determined that oracle is the process using the memory, this query will show you which session uses the most.
3. 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
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.
o 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 = ;
o 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
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"> EVENT: 10261 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.
This dump can be used by Oracle Support analysts to find the cause of the excessive memory allocation.
General suggestions on avoiding this error
o 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.
o 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. Please note that PGA_AGGREGATE_TARGET is NOT supported on OpenVMS in Oracle 9i, but it is in Oracle 10g. Refer to the following notes for more details:
o 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.
o Review the optimizer strategy, some access paths might need more memory due to sort operations, the use of functions on more rows,...
o On some operating systems, like Microsoft windows, the size of the SGA might be decreased to allow bigger PGA's.
o Make sure your operating system and oracle limits are set reasonably.
o Make sure there is enough memory available (physical memory and swapspace)
References
General:
NT:
NT:
Unix:
VMS:
@ Internal:
@ This event is very usefull. It will cause the process to dump information when the PGA grows above the specified limit
No comments:
Post a Comment