FAQ: ORA-4030 [Video] [ID 399497.1]

Questions and Answers

What is an ORA-4030?

Basically, an 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  Note 262540.1  Relocate SGABEG on 64-bit Oracle
* Oracle internal limit example 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?

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.

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.

See  http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html


Additionally, programming mistakes can also lead to excessive memory usage.
For example a recursive or infinite loop can use up memory. An errorstack will show this code as follows:

----- PL/SQL Call Stack -----
object line object
handle number name
70000003d2d6198 17 package body SCOTT.REGISTRY_PKG
70000003d2d6198 133 package body SCOTT.REGISTRY_PKG
70000003d2d6198 155 package body SCOTT.REGISTRY_PKG
70000003d2d6198 31 package body SCOTT.REGISTRY_PKG
70000003d2d6198 133 package body SCOTT.REGISTRY_PKG
70000003d2d6198 155 package body SCOTT.REGISTRY_PKG
PL/SQL call stack truncated after 1024 bytes.

Refer to following notes for more details on Automatic PGA Memory Management

Note 223730.1  Automatic PGA Memory Managment in 9i and 10g

Note 443746.1  Automatic Memory Management(AMM) on 11g

Can you limit the size of a process?

You can take steps to control the size of a process as discussed above.
However, from within the database framework you cannot place a hard limit on the size of a process by setting any initialization parameters or database configuration.
You can limit the size of a process from the OS side by setting kernel limits or user shell limits.
However, this leads to the ORA-4030 and will cause transaction rollback.