Diagnosing and Resolving ORA-4030 errors

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 accomodated 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:

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;

How to collect information on what the process is actually doing

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$sqlarea 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 =;

General suggestions on avoiding this error

1. 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.
2. 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:
   
     "Performance Issues After Increasing Workload",
     "Automatic PGA Memory Managment",
    "Top Oracle 9i init.ora Parameters Affecting Performance"
3. 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.
4. Review the optimizer strategy, some access paths might need more memory due to sort operations, the use of functions on more rows,...
5. On some operating systems, like Microsoft windows, the size of the SGA might be decreased to allow bigger PGA's.
6. Make sure your operating system and oracle limits are set reasonably.
7. Make sure there is enough memory available (physical memory and swapspace)

What is an ORA-4030?

ORA-4030 indicates a limit has been reached with respect to the Oracle process private memory allocation.
Typical causes:

• OS Memory limit reached such as physical memory and/or swap/virtual paging
• OS limits reached such as kernel or user shell limits that limit overall, user level or process level memory usage
• OS limit on PGA memory size due to SGA attach address as documented in unpublished Document:262540.1 - Relocate SGABEG on 64-bit Oracle
• Oracle internal limit example unpublished Bug:3130972
• Application design causing limits to be reached
• Bug – space leaks, heap leaks

What is difference between 4030 and 4031?

An ORA-4030 error is an error in the PGA; Memory limitation related to an Oracle rdbms server process is reached. This includes the database background processes and oracle shadow processes that are spawned directly by the database or the listener.

An ORA-4031 error is an error in the SGA; Memory limitation in SGA component such as shared pool,large pool, java pool, streams pool is reached.

What are the contents of Program Global Area memory?

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a non-shared memory area created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.

Components of the PGA vary depending on how the database is configured. The PGA is comprised of four memory areas:

• User Session Memory
  
  User session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private
• Private SQL Areas - contains data such as bind information and runtime buffers
  
  A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.
  
  The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:
  • The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed.
  • The run-time area, which is freed when the execution is terminated.
  
  The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a Dedicated Server, private SQL areas are located in the server process's PGA. However, if a session is connected through a Shared Server, part of the private SQL area (specifically, the peristent area) is kept in the SGA.
  
  The runtime area contains information used while the SQL statement is being executed. The size of the runtime area depends on the type and complexity of the SQL statement being executed and on the sizes of the rows that are processed by the statement. In general, the runtime area is somewhat smaller for INSERT, UPDATE, and DELETE statements than it is for SELECT statements, particularly when the SELECT statement requires a sort.
  
  Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE, and DELETE statements, Oracle frees the runtime area after the statement has been executed. For queries, Oracle frees the runtime area only after all rows are fetched or the query is canceled.
  
  The location of a private SQL area depends on the type of connection established for a session. If a session is connected via a dedicated server, private SQL areas are located in the user's PGA. However, if a session is connected via the multi-threaded server, the persistent areas and, for SELECT statements, the runtime areas, are kept in the SGA.
• SQL Work Areas
  
  The application developer of an Oracle precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Recursive cursors that Oracle issues implicitly for some SQL statements also use shared SQL areas.
  
  The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.
  
  A private SQL area continues to exist until the corresponding cursor is closed or the statement handle is freed. Although Oracle frees the runtime area after the statement completes, the persistent area remains waiting. Application developers close all open cursors that will not be used again to free the persistent area and to minimize the amount of memory required for users of the application.
• Cursor and SQL Areas
  
  For complex queries (for example, decision-support queries), a big portion of the runtime area is dedicated to work areas allocated by memory-intensive operators such as the following:
  • Sort-based operators (order by, group-by, rollup, window function)
  • Hash-join
  • Bitmap merge
  • Bitmap create
  
  For example, a sort operator uses a work area (sometimes called the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (also called the hash area) to build a hash table from its left input. If the amount of data to be processed by these two operators does not fit into a work area, then the input data is divided into smaller pieces. This allows some data pieces to be processed in memory while the rest are spilled to temporary disk storage to be processed later. Although bitmap operators do not spill to disk when their associated work area is too small, their complexity is inversely proportional to the size of their work area. Thus, these operators run faster with larger work area.
  
  In shared server mode, portions of the UGA are stored in the following:
  • Large pool if configured.
  • Shared pool if large pool is not configured.
  
  Note that as of 10g, the work areas controlled by PGA_AGGREGATE_TARGET are located in the PGA for shared servers. On 9i shared server work areas are defined by the *_area_size parameters and located in the sga.

Why do I see processes growing larger than the PGA_AGGREGATE_TARGET/MEMORY_TARGET/MEMORY_MAX/TARGET?

Parameter PGA_AGGREGATE_TARGET does not set a hard limit on pga size. It is only a target value used to dynamically size the process work areas.

It also does not affect other areas of the PGA that are allowed to grow beyond this limit. See below for explanation of process memory. The same is true for the MEMORY_* parameters. They are used to control the SGA and PGA size with PGA sizes controlled via dynamically calculated PGA_AGGREGATE_TARGET.

Can you control the size of a process?

As a database administrator you can control the size of a process within limitations. For example, you can configure certain database initialization parameters that affect the size of a process work area. If you choose, you can manually control the maximum size of SQL work areas by setting the following parameters: SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE. Setting these parameters is difficult, because the maximum work area size is ideally selected from the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one time to another. Thus, the various *_AREA_SIZE parameters are hard to tune under the best of circumstances.

With 9i and higher, you can automatically and globally manage the size of SQL work areas by specifying the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target. The key word in this statement is "tries". It is possible that PGA memory will grow beyond the "target".

The initialization parameter WORKAREA_SIZE_POLICY is a session- and system-level parameter that can be set to MANUAL or AUTO. The default is AUTO. You can switch back and forth between automatic and manual management of the work areas by setting PGA_AGGREGATE_TARGET and changing the WORKAREA_SIZE_POLICY parameter.

With PGA_AGGREGATE_TARGET, sizing of work areas for all dedicated sessions is automatic and all *_AREA_SIZE parameters are ignored for these sessions. Beginning with version 10 the PGA_AGGREGATE_TARGET is also applicable to shared server sessions.

At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirement.

Keep in mind that the PGA_AGGREGATE_TARGET does not limit the amount of PGA memory usage. It is only a target and is used to dynamically size the process work areas. It does not affect other areas of the PGA that are allowed to grow beyond this limit.

There are certain areas of PGA that cannot be controlled by initialization parameters. Such areas include PL/SQL memory collections such as PL/SQL tables and VARRAYs, and local PL/SQL variables. Depending on the programming code and amount of data being handled these areas can grow very large (up to 20G internal limit on 10) and can consume large amounts of memory. This memory growth can be controlled by good programming practices. As an example, use LIMIT clause with BULK COLLECT.