Analysis of ORA-04030 in alert logs

Analysis of ORA-04030 in alert logs

Yesterday I went to a customer to do inspection, encountered a classic ora-04030 error, alert Log is as follows:

Tue Oct 28 09:57:46 2014
Errors in file/Oracle/app/oracle/diag/rdbms/wmsdb/trace/wmsdb_ora_33358038.trc (incident = 177302 ):
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi, kllcqas: kllslibs)
Incident details in:/oracle/app/oracle/diag/rdbms/wmsdb/incident/incdir_177302/wmsdb_ora_33358038_i177302.trc
Errors in file/oracle/app/oracle/diag/rdbms/wmsdb/trace/wmsdb_ora_33358038.trc (incident = 177303 ):
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap, kgh stack)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi, kllcqas: kllslibs)
Incident details in:/oracle/app/oracle/diag/rdbms/wmsdb/incident/incdir_177303/wmsdb_ora_33358038_i177303.trc
Errors in file/oracle/app/oracle/diag/rdbms/wmsdb/incident/incdir_177302/wmsdb_ora_33358038_i177302.trc:
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap, kgh stack)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi, kllcqas: kllslibs)
Errors in file/oracle/app/oracle/diag/rdbms/wmsdb/trace/wmsdb_ora_33358038.trc (incident = 177304 ):
ORA-04030: out of process memory when trying to allocate 160 bytes (pga heap, control file cache)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi, kllcqas: kllslibs)
Incident details in:/oracle/app/oracle/diag/rdbms/wmsdb/incident/incdir_177304/wmsdb_ora_33358038_i177304.trc
Tue Oct 28 09:57:53 2014
Trace dumping is refreshing Ming id = [cdmp_20151128095753]
Tue Oct 28 09:57:54 2014
Sweep Incident [177303]: completed
Tue Oct 28 09:57:57 2014
Errors in file/oracle/app/oracle/diag/rdbms/wmsdb/incident/incdir_177302/wmsdb_ora_33358038_i177302.trc:
ORA-04030: out of process memory when trying to allocate 160 bytes (pga heap, control file cache)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi, kllcqas: kllslibs)
Errors in file/oracle/app/oracle/diag/rdbms/wmsdb/trace/wmsdb_ora_33358038.trc (incident = 177305 ):
ORA-04030: out of process memory when trying to allocate 16776728 bytes (QERHJ hash-joi, QERHJ Hash Table Entries)
Incident details in:/oracle/app/oracle/diag/rdbms/wmsdb/incident/incdir_177305/wmsdb_ora_33358038_i177305.trc
Trace dumping is refreshing Ming id = [cdmp_20151128095802]
......

The customer's database is Oracle 11.1.0.7 and runs on the p550 host. It has 16 logic CPUs, 16 GB memory, and AIX 6.1 64-bit system. 8 GB memory is allocated to SGA, 2 GB memory is provided to PGA, Which is dynamically adjusted by the memory_target = 10G parameter. At the same time, memory_max_target is also set to 10G, indicating that Oracle can occupy up to 10 Gb of OS Memory (62.5% of physical memory ). These two parameters are newly added for 11g and can be dynamically allocated to SGA and PGA. In 10g, only sga_target can be set to dynamically manage various memory components in sga, pga needs to be set manually.

SQL> show parameters target

NAME TYPE VALUE
----------------------------------------------------------------------------------------
Archive_lag_target integer 0
Db_flashback_retention_target integer 1440
Fast_start_io_target integer 0
Fast_start_mttr_target integer 0
Memory_max_target big integer 10G
Memory_target big integer 10G
Pga_aggregate_target big integer 2G
Sga_target big integer 8G

On the internet google a bit, a lot of ora-04030 errors are described are similar, mainly by the following types:
A. for 32-BIT systems, there are limits on SGA 1.7G
B. Some OS systems also have some memory parameter restrictions.
C. OS physical memory + Swap restrictions

For A, because the system is 64-bit, there is no GB limit;
For B, the result of viewing with ulimit is ulimited;
For C, the OS memory is 16 GB, but you can use topas to check that the memory usage is 90%.

Generally, the problem with ORA-04030 is caused by PGA overuse (the corresponding operation is sort/hash_join ). Starting from 9i, pga_aggregate_target specifies the maximum PGA used by all sessions. Here, it is 2 GB. If this value is set, the default workarea_size_policy = auto, sort_area_size/sort_area_retained_size will be ignored. So directly reduce pga_aggregate_target can solve some ORA-04030 problems.

In addition, this error means that the Oracle server process cannot allocate more memory from the operating system. The memory is composed of PGA (Program Global Area) and its content depends on the server configuration. For dedicated server processes, the memory includes a stack and the UGA (User Global Area) used to store User session data, cursor information, and sorting areas ). In multi-threaded configuration (Shared Server), UGA is allocated in SGA (System Global Area), so in this configuration UGA is not the cause of ORA-4030 errors. Therefore, the ORA-04030 indicates that the process requires more memory (stack UGA or PGA) to execute its tasks.

Because of this error, memory cannot be allocated from the operating system. This error may be caused by the process itself. For example, the process requires too much Memory, or some other reasons may cause the operating system Memory to be exhausted. For example, the SGA is too large or the system virtual Memory (physical Memory + Swap) contains too many processes. Many operating systems limit the amount of memory that a single process can obtain for self-protection.

After reading an official document, I said this:

Applies:
Oracle Database-Enterprise Edition-Version 11.2.0.3 and later -- 11.2.0.3 have not fixed this bug!
Information in this document applies to any platform.
* ** Checked for currency 24-July-2014 ***

SYMPTOMS
1. A session crashes:
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-join, QERHJ list array)
2. Review of the instance parameters set reveals:
"_ Pga_max_size" = 614400KB

CAUSE
The cause of this problem has been identified in:
Bug: 13447197-ORA-04030: out of process memory when trying to allocate 262168 BYTES (QERGH HAS
Suincluded as related:
Unpublished Bug: 9506362-ORA-04030: out of process memory (QERHJ HASH-JOI, KLLCQAS: kllslibs)
Both bugs have been susponded due to lack of reproducibility.

-- This is a bug on 11g.

SOLUTION
As the bugs have been susponded due to lack of information to perform analysis, the only possible
Workaround is to set the _ PGA_MAX_SIZE instance parameter to a smaller value.

-- The only possible solution is to set the implicit parameter "_ PGA_MAX_SIZE" to a smaller value.

This parameter determines the maximum size which can be used for per-process PGA memory. The default value
Is 200 MB and the range of valid values is from 10 MB up to 4TB-1.

-- This parameter can be specified to allocate the PGA memory for each process. The default value is 200 MB, and the valid value is 10 MB ~ (4T-1)

The per-process PGA memory can be limited by setting the _ PGA_MAX_SIZE to a smaller value which internally
Forces the hash-join to use the less memory and avoid the ORA-4030, like in:
SQL> alter system set "_ pga_max_size" = 100 M;

-- Use "_ PGA_MAX_SIZE" to force hash-join to use less memory to avoid ora-04030, such as setting to 100 M.

General suggestions for avoiding this error

1. Some operations require a large amount of memory. Reducing SORT_AREA_SIZE will be helpful for sorting problems. The Oracle server process allocates SORT_AREA_SIZE bytes in the PGA to the sorting operation. If you need more memory to complete the search, the server process uses temporary segment. This means that reducing SORT_AREA_SIZE will affect the query performance of a large number of sorting operations.
2. For 9i and later versions, you can enable the automatic SQL Execution memory management function by setting the WORKAREA_SIZE_POLICY parameter to AUTO and specifying the PGA_AGGREGATE_TARGET size in the initialization file. Using Automatic PGA memory management will help reduce the likelihood of ORA-04030 errors. Note that the OpenVMS operating system does not support PGA_AGGREGATE_TARGET in Oracle 9i, but does support PGA_AGGREGATE_TARGET in Oracle 10g.
3. PL/SQL programs can also allocate a large amount of memory, so some parts of the application may need to be overwritten. Although PL/SQL tables are very easy to use, they do need to allocate memory in PGA.
4. Check the optimizer policy. Some access paths may require more memory for sorting operations, function usage on multiple rows, and other reasons.
5. On some operating systems (such as Microsoft Windows), you may need to reduce the size of the SGA so that the PGA can obtain larger memory.
6. Make sure that the memory limits of the operating system and Oracle are properly set.
7. Ensure sufficient available Memory (physical Memory and Swap ).

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian