When considering Oracle Database performance, memory design is extremely important.

When an appropriate amount of memory is allocated, you can reduce physical I/O to data files, SQL hard parses, and temporary tablespace I/O caused by sorting and hash joins.

However, increasing memory does not necessarily improve performance.

For example, an increase in physical I/O may be caused not only by an insufficient buffer cache, but also by inefficient SQL, missing indexes, large full table scans, or changes in the workload. Hard parses in the shared pool may also be caused not only by an insufficient shared pool, but by the repeated execution of SQL statements containing literal values, object changes, or differences in the execution environment.

Therefore, when tuning Oracle Database memory, it is important to follow the sequence below.

Check the symptoms
   ↓
Check wait events, statistics, and advisors
   ↓
Determine whether insufficient memory is the cause
   ↓
Change the SGA or PGA
   ↓
Compare performance before and after the change

This article explains the differences between the SGA and PGA, the main memory components, automatic memory management, SQL statements used for checking memory, and methods for handling common problems, with a focus on Oracle Database 19c.

Oracle Database Memory Architecture
1. Overall Memory Architecture
1. Database Buffer Cache
1. Symptoms That May Indicate an Insufficient Buffer Cache
2. Shared Pool
1. Main Causes of Increased Hard Parses
3. Redo Log Buffer
4. Large Pool
5. Java Pool
6. In-Memory Area
PGA_AGGREGATE_TARGET Is Not a Limit
AMM: Automatic Memory Management
1. Advantages of AMM
2. AMM Considerations
ASMM: Automatic Shared Memory Management
1. Advantages of ASMM
2. ASMM Considerations
Which Should Be Used on Linux?
Required Privileges and Considerations in a CDB Environment
1. Check the Overall SGA Summary
2. Check Memory-Related Parameters
1. Typical AMM Configuration
2. Typical ASMM Configuration
3. Check the Current Sizes of SGA Components
4. Check the SGA Resize History
5. Check the Effect of Expanding the Buffer Cache
6. Check Shared Pool Advisory Information
7. Check Hard Parse Activity
8. Check PGA Usage
9. Check the Number of Optimal, One-pass, and Multi-pass Executions
10. Check the Effect of Changing PGA_AGGREGATE_TARGET
11. Check Processes Consuming Large Amounts of PGA
Step 1: Record the Values Before the Change
Step 2: Check Memory on the Operating System
Step 3: Check Oracle Advisors
Step 4: Do Not Change Multiple Parameters at the Same Time
Step 5: Check the Differences After the Change
Step 6: Restore the Previous Value if Performance Does Not Improve
Error Message
Main Causes
Example Checks
Approach to Resolving the Problem
1. About ALTER SYSTEM FLUSH SHARED_POOL
Error Message
Main Causes
Important Consideration
Error Message
Q1. Does a Larger Buffer Cache Always Improve Performance?
Q2. Should DB_CACHE_SIZE and SHARED_POOL_SIZE Be Set to 0 When Using ASMM?
Q3. Can PGA Usage Exceed PGA_AGGREGATE_TARGET?
Q4. Does TEMP Tablespace Usage Mean That the PGA Is Insufficient?
Q5. Should the Shared Pool Be Flushed When ORA-04031 Occurs?

Oracle Database Memory Architecture

The two main memory areas used by an Oracle Database instance are:

SGA: Memory shared by multiple Oracle processes
PGA: Private memory used by individual Oracle processes

Oracle Database processes SQL by combining the SGA with the PGA of each process. The SGA is allocated when the instance starts, but when AMM or ASMM is used, some components can dynamically expand or shrink while the instance is running. The PGA is allocated separately for each server process and background process. (docs.oracle.com)

Overall Memory Architecture

+------------------------------------------------------------------+
|                    Oracle Database Instance                      |
+------------------------------------------------------------------+
|                                                                  |
|  +--------------------------- SGA -----------------------------+  |
|  | Shared by multiple Oracle processes                         |  |
|  |                                                              |  |
|  |  ・Database Buffer Cache                                     |  |
|  |  ・Shared Pool                                                |  |
|  |  ・Redo Log Buffer                                            |  |
|  |  ・Large Pool                                                 |  |
|  |  ・Java Pool                                                  |  |
|  |  ・Streams Pool                                               |  |
|  |  ・In-Memory Area, etc.                                       |  |
|  +--------------------------------------------------------------+  |
|                                                                  |
|  +--------- PGA ---------+  +--------- PGA ---------+             |
|  | For Server Process 1  |  | For Server Process 2  |   ……        |
|  |                       |  |                       |             |
|  | ・Sort Work Area      |  | ・Sort Work Area      |             |
|  | ・Hash Work Area      |  | ・Hash Work Area      |             |
|  | ・Cursor Execution    |  | ・Cursor Execution    |             |
|  |   Information         |  |   Information         |             |
|  +-----------------------+  +-----------------------+             |
|                                                                  |
+------------------------------------------------------------------+

Memory Area	Main Purpose	Scope
SGA	Stores data blocks, SQL statements, execution plans, data dictionary information, redo data, and other shared information	Shared by Oracle processes within the instance
PGA	Stores sort data, hash join data, cursor execution state, and process-specific information	Generally allocated per Oracle process

In a dedicated server configuration, a server process and a user session generally have a one-to-one relationship. In a shared server configuration, however, some or all of the UGA that stores session state is located in the SGA. Therefore, it is not accurate to assume that the PGA is always allocated on a per-session basis. The PGA is fundamentally private memory allocated per process. (docs.oracle.com)

Main SGA Components

The SGA consists of multiple memory components, each with a different purpose.

+------------------------------------------------------------------+
|                              SGA                                 |
+------------------------------------------------------------------+
| Database Buffer Cache                                            |
|  └ Stores data blocks read from data files                       |
+------------------------------------------------------------------+
| Shared Pool                                                       |
|  ├ Library Cache: SQL, PL/SQL, and execution plans               |
|  └ Data Dictionary Cache: Definitions of tables, columns, etc.   |
+------------------------------------------------------------------+
| Redo Log Buffer                                                   |
|  └ Stores redo entries before they are written to online redo    |
|    logs                                                           |
+------------------------------------------------------------------+
| Large Pool, Java Pool, Streams Pool, In-Memory Area, etc.         |
+------------------------------------------------------------------+
| Fixed SGA                                                         |
|  └ Stores internal instance management information               |
+------------------------------------------------------------------+

Oracle documentation describes the buffer cache, shared pool, redo log buffer, large pool, Java pool, and In-Memory area as major SGA components. (docs.oracle.com)

1. Database Buffer Cache

The database buffer cache stores copies of data blocks read from data files.

When a block required by SQL is already present in the buffer cache, Oracle Database does not need to read it again from a data file. Therefore, an appropriately sized buffer cache can significantly reduce physical I/O. (docs.oracle.com)

However, you should not immediately increase DB_CACHE_SIZE or SGA_TARGET simply because physical reads are high.

Physical reads may increase for the following reasons:

The buffer cache is too small
SQL statements repeatedly perform full table scans
Required indexes do not exist
The amount of data being read has increased
Batch processing and online processing are concentrated in the same time period
The SQL execution plan has changed

Before increasing the buffer cache, check the estimates in V$DB_CACHE_ADVICE, high-load SQL statements, and wait events. V$DB_CACHE_ADVICE can be used to estimate the number of physical reads that would occur if the cache size were changed. (docs.oracle.com)

Symptoms That May Indicate an Insufficient Buffer Cache

・Physical I/O waits such as db file sequential read are high
・The same data blocks are repeatedly read from data files
・DB Cache Advice predicts that expanding the cache will reduce physical reads

Rather than making decisions based only on the cache hit ratio, it is important to determine how much increasing the buffer cache would actually reduce processing time and physical reads.

2. Shared Pool

The shared pool mainly contains the following areas:

Library cache
Data dictionary cache
Server result cache, etc.

The library cache stores parsed SQL statements, PL/SQL code, execution plans, and other information. When the same SQL can be shared, Oracle Database does not need to perform a hard parse every time, reducing CPU, memory, latch, and other resource usage. (docs.oracle.com)

Main Causes of Increased Hard Parses

An undersized shared pool is not the only possible cause.

Increase in hard parses
   ├─ The shared pool is too small and SQL is aged out of the cache
   ├─ Large numbers of SQL statements differing only in literal values
   ├─ Differences in SQL text, spaces, uppercase, lowercase, etc.
   ├─ Differences in referenced schemas or session environments
   ├─ Cursors are invalidated by DDL or statistics updates
   └─ The application does not reuse cursors

In an OLTP system in particular, repeatedly issuing SQL statements containing literal values, such as the following, may cause them to be handled as different SQL statements.

SELECT employee_name
FROM employees
WHERE employee_id = 100;

SELECT employee_name
FROM employees
WHERE employee_id = 101;

Using a bind variable makes the SQL statement easier to share.

SELECT employee_name
FROM employees
WHERE employee_id = :employee_id;

Oracle documentation also recommends using bind variables instead of literals whenever possible so that shared cursors can be used. However, for analytical SQL involving heavily skewed data distributions, using literal values may allow the optimizer to estimate selectivity more appropriately. Therefore, SQL statements should not be mechanically converted to use bind variables without considering the characteristics of the workload. (docs.oracle.com)

3. Redo Log Buffer

The redo log buffer is a circular buffer that stores redo entries representing database changes before they are written to online redo log files.

The LGWR process writes the contents of the redo log buffer to the online redo log files. (docs.oracle.com)

When a large update operation generates a high volume of redo and LGWR cannot keep up with the writes, the log buffer space wait event may occur.

However, the occurrence of log buffer space does not necessarily mean that increasing LOG_BUFFER will solve the problem.

Check the following:

Whether the storage containing the redo logs is slow
Whether LGWR I/O waits have increased
Whether a large amount of redo is being generated in a short time
Whether online redo log switches are occurring too frequently
Whether redo buffer allocation retries is increasing

LOG_BUFFER is a static initialization parameter that cannot be changed dynamically, and its default value is determined based on the SGA size, number of CPUs, and other factors. The redo log buffer is also not subject to automatic resizing by ASMM. (docs.oracle.com)

4. Large Pool

The large pool is an optional area used to avoid fragmentation caused by allocating large contiguous blocks of memory from the shared pool.

It is mainly used for the following:

Session memory in a shared server configuration
Parallel execution message buffers
RMAN I/O buffers
Certain large memory allocations

When ASMM is used, the large pool is automatically adjusted within SGA_TARGET. When LARGE_POOL_SIZE is set to a value greater than 0, that value is treated as the lower limit under ASMM. (docs.oracle.com)

5. Java Pool

The Java pool is used to execute Java code in the Java Virtual Machine within Oracle Database.

In environments that do not use Java functionality inside the database, such as Java stored procedures, the Java pool is relatively less important. In an ASMM environment, if JAVA_POOL_SIZE is specified, its value becomes the lower limit during automatic adjustment. (docs.oracle.com)

6. In-Memory Area

The In-Memory area is an SGA area that stores columnar data used by Oracle Database In-Memory.

The standard buffer cache stores data in row format, whereas the In-Memory Column Store also stores data in columnar format, which is suitable for analytical processing.

Data files
    │
    ├─ Buffer Cache : Row format
    │
    └─ In-Memory Area: Columnar format

Under the Oracle Database 19c licensing model, the full Oracle Database In-Memory feature is a separately licensed option for Enterprise Edition.

Enterprise Edition also provides the “Database In-Memory Base Level,” which allows the In-Memory feature to be used with certain restrictions. With Base Level, the In-Memory area is limited to a maximum of 16 GB per CDB, among other conditions. Database In-Memory Base Level is not available with Standard Edition 2. Because licensing conditions may change, always check the Oracle Database Licensing Information User Manual that applies at the time of deployment. (docs.oracle.com)

PGA Structure and Role

The PGA is private memory used by server processes and background processes.

+----------------------------------------------------------+
|                         PGA                              |
+----------------------------------------------------------+
| Private SQL Area                                         |
|  ├ Cursor execution state                                |
|  ├ Bind variables                                        |
|  └ SQL execution control information                     |
+----------------------------------------------------------+
| SQL Work Areas                                            |
|  ├ Sort operations                                       |
|  ├ Hash joins                                            |
|  ├ Bitmap operations                                     |
|  └ Other memory-intensive SQL operators                  |
+----------------------------------------------------------+
| Process-specific management information                  |
+----------------------------------------------------------+

SQL work areas in the PGA are used for operations such as:

Sorting for ORDER BY
Aggregation for GROUP BY
Hash joins
Bitmap processing
Window functions and analytical processing
Certain index creation operations

Oracle Database classifies work area execution into the following three modes.

Execution Mode	Status
Optimal	The required work can primarily be completed in memory
One-pass	The work area is smaller than the optimal size and one additional pass over the input data is required
Multi-pass	The work area is even smaller and multiple additional passes are required

One-pass execution is not completely in-memory processing. Because the operation is performed with less than the optimal amount of memory, additional reads and writes occur, increasing response time. Multi-pass execution causes an even greater performance degradation and should be avoided whenever possible. (docs.oracle.com)

Sufficient PGA
   └─ Optimal
        └─ Processing with little additional I/O

Insufficient PGA
   ├─ One-pass
   │    └─ Additional processing using the temporary tablespace
   │
   └─ Multi-pass
        └─ Multiple additional passes and significant delay

PGA_AGGREGATE_TARGET Is Not a Limit

PGA_AGGREGATE_TARGET is the target amount of PGA memory used by the entire instance.

However, it is not an absolute limit.

PGA_AGGREGATE_TARGET mainly controls tunable work areas used for sorting, hash joins, and similar operations. When untunable PGA memory used by PL/SQL, Java, or other components increases, actual PGA usage may exceed PGA_AGGREGATE_TARGET. (docs.oracle.com)

The parameter used to set an absolute limit is PGA_AGGREGATE_LIMIT.

When PGA_AGGREGATE_LIMIT is exceeded, Oracle Database aborts calls from sessions consuming large amounts of untunable PGA memory. If memory usage still does not decrease, the relevant sessions are terminated. (docs.oracle.com)

Differences Between AMM and ASMM

Oracle Database provides several methods for managing the SGA and PGA.

AMM: Automatic Memory Management

AMM uses the following parameters:

MEMORY_TARGET
MEMORY_MAX_TARGET

When the combined target for the SGA and instance PGA is set with MEMORY_TARGET, Oracle Database dynamically redistributes memory between the SGA and PGA. (docs.oracle.com)

+--------------------- MEMORY_TARGET ----------------------+
|                                                         |
|       SGA                     Instance PGA              |
|  +-------------+          +------------------+           |
|  |             | ←Adjust→ |                  |           |
|  +-------------+          +------------------+           |
|                                                         |
+---------------------------------------------------------+

Advantages of AMM

The SGA and PGA can be managed together
The allocation between the SGA and PGA changes according to the workload
It is easy to manage in small-scale and test environments

AMM Considerations

On Linux, AMM cannot be used together with HugePages. When AMM is used, the SGA is primarily allocated through /dev/shm and is not allocated from HugePages. (docs.oracle.com)

ASMM: Automatic Shared Memory Management

With ASMM, the SGA and PGA are configured separately.

SGA_TARGET
SGA_MAX_SIZE

PGA_AGGREGATE_TARGET
PGA_AGGREGATE_LIMIT

When the total SGA size is specified with SGA_TARGET, Oracle Database automatically adjusts the sizes of the buffer cache, shared pool, large pool, Java pool, and other components.

The PGA is managed separately by using PGA_AGGREGATE_TARGET. (docs.oracle.com)

+------------------- SGA_TARGET --------------------+
| Buffer Cache ←→ Shared Pool ←→ Other Components  |
+---------------------------------------------------+

+-------------- PGA_AGGREGATE_TARGET ---------------+
| Target for the PGAs of individual Oracle processes|
+---------------------------------------------------+

Advantages of ASMM

The DBA can explicitly design the allocation between the SGA and PGA
Major components within the SGA are automatically adjusted
HugePages can be used on Linux
It is easier to determine whether the SGA or PGA has increased

ASMM Considerations

Memory is not automatically transferred between the SGA and PGA as it is with AMM
The DBA must evaluate the allocation between the SGA and PGA according to the workload
Allocating too much memory to the SGA may reduce the memory available to the PGA and operating system

Which Should Be Used on Linux?

When using a large SGA and HugePages on Linux, select ASMM instead of AMM.

However, you should not automatically assume that “ASMM must always be used on Linux” or that “HugePages is mandatory in every environment.”

Make the decision based on the following conditions.

Condition	Management Method to Consider
Small test environment	AMM or ASMM
Windows environment	AMM is also an option
HugePages is used on Linux	ASMM
Large production environment	Primarily consider ASMM
Explicit management of the SGA and PGA is required	ASMM

Oracle’s Linux documentation explains that AMM must be disabled when HugePages is used. It is also necessary to avoid consuming all physical memory with HugePages and to retain sufficient memory for normal pages. (docs.oracle.com)

Individual Parameters When Using ASMM

Under ASMM, the following major components are automatically adjusted within SGA_TARGET.

DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
STREAMS_POOL_SIZE

When an individual parameter is set to a value greater than 0, that value is generally used as a lower limit during automatic adjustment rather than as a fixed size. (docs.oracle.com)

For example, consider the following configuration.

SGA_TARGET       = 16G
DB_CACHE_SIZE    = 8G
SHARED_POOL_SIZE = 2G

In this case, ASMM operates approximately under the following constraints.

Total SGA       : 16 GB
Buffer Cache    : Minimum 8 GB
Shared Pool     : Minimum 2 GB
Remaining Memory: Automatically allocated according to the workload

If individual parameters are set excessively high, the range in which Oracle Database can automatically adjust memory becomes smaller.

Unless a clear minimum guaranteed size is required, avoid setting unnecessarily high lower limits for individual components.

Symptoms of Memory Shortages and Items to Check

Symptom	Suspected Area	First Items to Check
High physical reads	Buffer cache	SQL, wait events, and `V$DB_CACHE_ADVICE`
High number of hard parses	Shared pool or application	Bind variables, `parse count (hard)`, and reasons cursors cannot be shared
High TEMP I/O	PGA	One-pass, Multi-pass, and `V$PGA_TARGET_ADVICE`
High `log buffer space` waits	Redo log buffer or LGWR I/O	Redo generation, LGWR waits, and storage performance
ORA-04031	Relevant pool within the SGA	Heap name in the error message, trace files, and memory configuration
ORA-04030	Process private memory	OS memory, process PGA, and OS limits
ORA-04036	PGA limit exceeded	`PGA_AGGREGATE_LIMIT` and high-consumption sessions

Practical Memory Monitoring SQL

Required Privileges and Considerations in a CDB Environment

To query dynamic performance views, one of the following privileges is generally required:

SYSDBA
SELECT_CATALOG_ROLE
Individual SELECT privileges on the required V_$ views
SELECT ANY DICTIONARY

In production environments, do not grant unnecessarily powerful privileges. Grant only the privileges required for monitoring.

The SGA and instance PGA are instance-level information. In a CDB environment, connect to CDB$ROOT when checking the entire instance. When checking PDB-level memory limits, also review CON_ID and the initialization parameters configured for the PDB. Cumulative values in views such as V$PGASTAT are accumulated from instance startup. (docs.oracle.com)

1. Check the Overall SGA Summary

SHOW SGA;

The SQL*Plus SHOW SGA command displays an overview of the SGA using categories such as:

Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers

This is a SQL*Plus command, not a SQL statement.

2. Check Memory-Related Parameters

SELECT
    name,
    display_value,
    isdefault,
    issys_modifiable
FROM
    v$parameter
WHERE
    name IN (
        'memory_target',
        'memory_max_target',
        'sga_target',
        'sga_max_size',
        'db_cache_size',
        'shared_pool_size',
        'large_pool_size',
        'java_pool_size',
        'streams_pool_size',
        'log_buffer',
        'pga_aggregate_target',
        'pga_aggregate_limit'
    )
ORDER BY
    name;

This SQL statement can be used to determine whether AMM or ASMM is being used.

Typical AMM Configuration

MEMORY_TARGET > 0

Typical ASMM Configuration

MEMORY_TARGET        = 0
SGA_TARGET           > 0
PGA_AGGREGATE_TARGET > 0

3. Check the Current Sizes of SGA Components

SELECT
    component,
    ROUND(current_size / 1024 / 1024, 2) AS current_mb,
    ROUND(min_size     / 1024 / 1024, 2) AS min_mb,
    ROUND(max_size     / 1024 / 1024, 2) AS max_mb,
    ROUND(user_specified_size / 1024 / 1024, 2)
        AS user_specified_mb
FROM
    v$sga_dynamic_components
WHERE
    current_size > 0
ORDER BY
    current_size DESC;

V$SGA_DYNAMIC_COMPONENTS can be used to check the current size of dynamic SGA components and the minimum and maximum sizes recorded since instance startup. (docs.oracle.com)

4. Check the SGA Resize History

SELECT
    component,
    oper_type,
    oper_mode,
    ROUND(initial_size / 1024 / 1024, 2) AS initial_mb,
    ROUND(target_size  / 1024 / 1024, 2) AS target_mb,
    ROUND(final_size   / 1024 / 1024, 2) AS final_mb,
    status,
    start_time,
    end_time
FROM
    v$sga_resize_ops
ORDER BY
    start_time DESC
FETCH FIRST 20 ROWS ONLY;

This query shows which components were expanded or reduced by automatic memory management.

If expansion and reduction occur repeatedly, check whether the total SGA is insufficient or whether lower limits configured for individual components are preventing effective automatic adjustment.

5. Check the Effect of Expanding the Buffer Cache

SELECT
    size_for_estimate AS cache_size_mb,
    size_factor,
    estd_physical_reads,
    estd_physical_read_factor,
    estd_physical_read_time
FROM
    v$db_cache_advice
WHERE
    name = 'DEFAULT'
AND block_size = (
        SELECT TO_NUMBER(value)
        FROM v$parameter
        WHERE name = 'db_block_size'
    )
ORDER BY
    size_for_estimate;

The main columns to check are:

Column	Description
`CACHE_SIZE_MB`	Cache size being evaluated
`SIZE_FACTOR`	Ratio relative to the current size
`ESTD_PHYSICAL_READS`	Estimated number of physical reads
`ESTD_PHYSICAL_READ_FACTOR`	Estimated physical read ratio relative to the current value
`ESTD_PHYSICAL_READ_TIME`	Estimated physical read time

If increasing the buffer cache results in little reduction in the estimated number of physical reads, the benefit of expanding the cache is likely to be limited. (docs.oracle.com)

6. Check Shared Pool Advisory Information

SELECT
    shared_pool_size_for_estimate AS shared_pool_mb,
    shared_pool_size_factor,
    estd_lc_size,
    estd_lc_memory_objects,
    estd_lc_time_saved,
    estd_lc_time_saved_factor
FROM
    v$shared_pool_advice
ORDER BY
    shared_pool_size_for_estimate;

V$SHARED_POOL_ADVICE can be used to estimate values such as the parsing time that would be saved by the library cache if the shared pool size were changed. (docs.oracle.com)

7. Check Hard Parse Activity

SELECT
    name,
    value
FROM
    v$sysstat
WHERE
    name IN (
        'parse count (total)',
        'parse count (hard)',
        'execute count',
        'session cursor cache hits'
    )
ORDER BY
    name;

When parse count (hard) is high, check not only the shared pool size, but also whether the application uses bind variables and cursor caching.

The reasons why SQL statements that appear identical cannot be shared can be investigated using views such as V$SQL_SHARED_CURSOR. Oracle documentation also explains how to use this view to determine why SQL statements are not being shared. (docs.oracle.com)

8. Check PGA Usage

SELECT
    name,
    CASE
        WHEN unit = 'bytes'
            THEN ROUND(value / 1024 / 1024, 2)
        ELSE value
    END AS value,
    CASE
        WHEN unit = 'bytes'
            THEN 'MB'
        ELSE unit
    END AS unit
FROM
    v$pgastat
WHERE
    name IN (
        'aggregate PGA target parameter',
        'aggregate PGA auto target',
        'total PGA allocated',
        'total PGA inuse',
        'maximum PGA allocated',
        'total freeable PGA memory',
        'over allocation count',
        'global memory bound',
        'cache hit percentage',
        'extra bytes read/written'
    )
ORDER BY
    name;

The statistic name current PGA allocated shown in the original article is not used in V$PGASTAT in Oracle Database 19c. The current allocated amount is checked using total PGA allocated. (docs.oracle.com)

The particularly important statistics are:

Statistic Name	What It Shows
`total PGA allocated`	The amount of PGA currently allocated
`maximum PGA allocated`	The maximum PGA allocation since startup
`aggregate PGA auto target`	PGA available for automatically managed work areas
`over allocation count`	Number of times additional memory had to be allocated because the PGA target could not be maintained
`global memory bound`	Maximum amount that can be allocated to an individual work area
`extra bytes read/written`	Amount of data requiring additional processing because it could not be processed in Optimal mode

When over allocation count increases, PGA_AGGREGATE_TARGET may be insufficient. However, this is a cumulative value since startup, so check the difference over a defined period rather than making a decision from a single snapshot. (docs.oracle.com)

9. Check the Number of Optimal, One-pass, and Multi-pass Executions

SELECT
    name,
    value
FROM
    v$sysstat
WHERE
    name IN (
        'workarea executions - optimal',
        'workarea executions - onepass',
        'workarea executions - multipass'
    )
ORDER BY
    name;

If Multi-pass executions are occurring, an insufficient PGA may be causing significant performance degradation.

However, these values are also cumulative since instance startup. You must check changes during the period when the problem occurred and review V$SQL_WORKAREA for the affected SQL statements. (docs.oracle.com)

10. Check the Effect of Changing PGA_AGGREGATE_TARGET

SELECT
    ROUND(pga_target_for_estimate / 1024 / 1024)
        AS target_mb,
    pga_target_factor,
    estd_pga_cache_hit_percentage,
    estd_overalloc_count,
    ROUND(estd_extra_bytes_rw / 1024 / 1024)
        AS estd_extra_rw_mb
FROM
    v$pga_target_advice
ORDER BY
    pga_target_for_estimate;

V$PGA_TARGET_ADVICE can be used to predict the following values if PGA_AGGREGATE_TARGET is changed:

PGA cache hit percentage
Over-allocation count
Additional read and write volume
Estimated processing time

Consider a value near the point at which ESTD_OVERALLOC_COUNT becomes 0 and the improvement from additional memory begins to level off. (docs.oracle.com)

11. Check Processes Consuming Large Amounts of PGA

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    p.spid,
    ROUND(p.pga_used_mem  / 1024 / 1024, 2) AS used_mb,
    ROUND(p.pga_alloc_mem / 1024 / 1024, 2) AS allocated_mb,
    ROUND(p.pga_max_mem   / 1024 / 1024, 2) AS max_mb
FROM
    v$process p
LEFT JOIN
    v$session s
ON
    s.paddr = p.addr
ORDER BY
    p.pga_alloc_mem DESC
FETCH FIRST 20 ROWS ONLY;

When PGA usage is high across the instance, identify which processes or sessions are consuming the memory.

V$PROCESS_MEMORY can be used to check PGA consumption by categories such as SQL, PL/SQL, and Java. (docs.oracle.com)

Safe Memory Tuning Procedure

Change memory parameters using the following procedure.

Step 1: Record the Values Before the Change

SELECT
    name,
    display_value
FROM
    v$parameter
WHERE
    name IN (
        'sga_target',
        'sga_max_size',
        'pga_aggregate_target',
        'pga_aggregate_limit'
    )
ORDER BY
    name;

Step 2: Check Memory on the Operating System

On Linux, check information using commands such as:

free -m
vmstat 1
grep -i huge /proc/meminfo

Check the following points:

Available memory, not only free memory
Swap-in and swap-out activity
Total and free HugePages
Memory usage of other Oracle instances and applications
Increase in the number of processes
Memory limits imposed by containers or cgroups

Step 3: Check Oracle Advisors

Entire SGA : V$SGA_TARGET_ADVICE
Buffer Cache: V$DB_CACHE_ADVICE
Shared Pool : V$SHARED_POOL_ADVICE
PGA         : V$PGA_TARGET_ADVICE

Step 4: Do Not Change Multiple Parameters at the Same Time

For example, if insufficient PGA memory is suspected, change only PGA_AGGREGATE_TARGET first.

ALTER SYSTEM SET pga_aggregate_target = 4G
SCOPE = BOTH;

The value is only an example. The actual setting must be determined based on physical memory, concurrent connections, SQL workload, and advisor results.

Step 5: Check the Differences After the Change

・Processing time
・Wait events
・TEMP usage
・Physical reads
・Hard parses
・One-pass and Multi-pass executions
・Maximum PGA usage
・OS swapping

Step 6: Restore the Previous Value if Performance Does Not Improve

ALTER SYSTEM SET pga_aggregate_target = <previous value>
SCOPE = BOTH;

Even for parameters that can be changed online, memory may not be immediately released when a component needs to shrink, and Oracle’s internal resize operation may take time to complete. Check the status of changes using views such as V$SGA_RESIZE_OPS.

ORA-04031 Causes and Solutions

Error Message

ORA-04031: unable to allocate ... bytes of shared memory

ORA-04031 occurs when the required shared memory cannot be allocated.

It does not necessarily occur only in the shared pool. Check the heap name and pool name displayed in the error message to determine whether the shortage occurred in the shared pool, large pool, Streams pool, In-Memory area, or another memory area. (docs.oracle.com)

Main Causes

The entire SGA is too small
A specific pool, such as the shared pool, is too small
Large numbers of SQL statements cannot be shared
Large numbers of child cursors are generated
A large contiguous memory allocation cannot be obtained
A memory leak or Oracle Database defect
Another SGA area, such as the Streams pool, is insufficient

Example Checks

SELECT
    component,
    current_size,
    min_size,
    max_size
FROM
    v$sga_dynamic_components
ORDER BY
    current_size DESC;

SELECT
    request_misses,
    request_failures,
    last_failure_size
FROM
    v$shared_pool_reserved;

Approach to Resolving the Problem

1. Check the alert log and trace files
2. Check the pool name shown in ORA-04031
3. Check SQL sharing and the number of child cursors
4. Check the SGA and relevant pool sizes
5. Check the advisors
6. Increase SGA_TARGET or the relevant pool if necessary
7. Consider requesting an investigation from Oracle Support if the problem recurs

About ALTER SYSTEM FLUSH SHARED_POOL

ALTER SYSTEM FLUSH SHARED_POOL;

This command flushes unpinned objects from the shared pool.

However, it should not be executed casually as a normal permanent solution.

After execution, many SQL statements must be parsed again, which may temporarily increase CPU usage and hard parses. If the cause of the shared pool shortage is literal SQL, large numbers of child cursors, invalidations, or a memory leak, the problem will recur even after the shared pool is flushed.

Treat it as a temporary recovery or diagnostic measure only after understanding its impact, and investigate the root cause.

ORA-04030 Causes and Solutions

Error Message

ORA-04030: out of process memory when trying to allocate ...

ORA-04030 occurs when an Oracle process cannot allocate private memory.

Oracle’s official error description identifies exhaustion of operating system memory or reaching a per-process private memory limit as the main causes. (docs.oracle.com)

Main Causes

Insufficient physical memory on the operating system
Insufficient virtual memory, including swap space
Per-process memory limits
ulimit or cgroup limits
A single process consuming a large amount of PGA
Large PL/SQL collections
Inefficient sorting or hash processing
Excessive parallel execution
Memory leaks in Oracle Database or the application

Important Consideration

Increasing PGA_AGGREGATE_TARGET without investigation is dangerous when ORA-04030 occurs.

Increasing PGA_AGGREGATE_TARGET allows more PGA to be allocated to individual work areas and may make an operating system memory shortage worse.

First, check the following:

・Which Oracle process encountered the error
・The PGA usage of that process
・Memory usage across the host
・Operating system process limits
・The executed SQL or PL/SQL
・Concurrent execution and degree of parallelism
・The alert log and trace files

ORA-04036 Causes and Solutions

Error Message

ORA-04036:
PGA memory used by the instance or PDB exceeds PGA_AGGREGATE_LIMIT

ORA-04036 occurs when PGA memory usage by the instance or PDB exceeds PGA_AGGREGATE_LIMIT. (docs.oracle.com)

Check the following:

SHOW PARAMETER pga_aggregate_limit

SELECT
    name,
    value,
    unit
FROM
    v$pgastat
WHERE
    name IN (
        'total PGA allocated',
        'maximum PGA allocated',
        'total PGA inuse'
    );

Before simply increasing the limit, identify the sessions, SQL statements, PL/SQL code, or parallel executions that consumed large amounts of PGA.

Preventing Operating System Memory Exhaustion and Swapping

Do not configure the combined SGA and PGA targets close to the physical memory limit.

Memory is also required for:

The operating system kernel
PGA memory for Oracle processes
Oracle Grid Infrastructure
ASM
File system cache
Monitoring agents
Backup software
Security software
Other databases and applications

Oracle’s official best practices also explain that the combined allocation for the SGA and PGA should be less than physical memory and that sufficient memory must be retained for the operating system and other processes. (docs.oracle.com)

However, there is no fixed ratio that can be applied to every environment, such as “always reserve 20% of physical memory for the operating system.”

Oracle’s PGA tuning guide presents an example of initially reserving 20% for the operating system and other components, but this is only an initial sizing example. The actual design must consider the operating system, number of connections, PGA fluctuations, HugePages, ASM, backup software, monitoring software, and other components. (docs.oracle.com)

Physical Memory
   │
   ├─ Operating system, kernel, and normal pages
   ├─ Oracle SGA
   ├─ Oracle PGA
   ├─ Grid Infrastructure and ASM
   ├─ Backup and monitoring processes
   └─ Reserved memory

Continuous swapping at the operating system level may significantly degrade Oracle Database response times. It is important to design the system so that the entire SGA remains in physical memory. (docs.oracle.com)

Differences Between Editions

Basic memory management features such as the SGA, PGA, AMM, and ASMM are core Oracle Database features and can be used with both Enterprise Edition and Standard Edition 2.

However, In-Memory-related features have edition and licensing restrictions.

Feature	Enterprise Edition	Standard Edition 2
Basic SGA and PGA features	Available	Available
AMM	Available	Available
ASMM	Available	Available
Automatic PGA memory management	Available	Available
Oracle Database In-Memory	Generally requires a separately licensed option	Not available
Database In-Memory Base Level	Available with restrictions	Not available

Licensing conditions may differ depending on the contract, cloud service, and Release Update. Final decisions must be based on the applicable contract and the latest official Oracle licensing information. (docs.oracle.com)

Frequently Asked Questions

Q1. Does a Larger Buffer Cache Always Improve Performance?

Within a certain range, increasing the buffer cache may reduce physical reads.

However, if the workload reads large numbers of blocks that are not reused, or if the required data is already sufficiently cached, increasing the cache may provide only limited benefits.

Use V$DB_CACHE_ADVICE to check the estimated number of physical reads at larger cache sizes. (docs.oracle.com)

Q2. Should DB_CACHE_SIZE and SHARED_POOL_SIZE Be Set to 0 When Using ASMM?

If no special lower limit is required, Oracle Database can be allowed to prioritize automatic adjustment.

However, when a known minimum size is required by a particular application, a lower limit can be configured using an individual parameter.

Values specified for individual parameters are generally treated as minimum guaranteed sizes in an ASMM environment. (docs.oracle.com)

Q3. Can PGA Usage Exceed PGA_AGGREGATE_TARGET?

Yes.

PGA_AGGREGATE_TARGET is a target, not an absolute limit. In addition, not all PGA memory can be controlled by this parameter. (docs.oracle.com)

Use PGA_AGGREGATE_LIMIT when an absolute upper limit is required.

Q4. Does TEMP Tablespace Usage Mean That the PGA Is Insufficient?

Not necessarily.

When the volume of data being processed is extremely large, One-pass execution may occur even when the PGA is configured appropriately.

Use the following information together when making a decision:

workarea executions - onepass
workarea executions - multipass
extra bytes read/written
over allocation count
V$PGA_TARGET_ADVICE
The execution plan and processed data volume of the relevant SQL statement

In particular, when Multi-pass execution occurs continuously, suspect insufficient PGA memory or excessive concurrent execution. (docs.oracle.com)

Q5. Should the Shared Pool Be Flushed When ORA-04031 Occurs?

Flushing the shared pool is a temporary diagnostic measure, not a permanent solution.

After the flush, SQL reparsing may become concentrated.

Investigate root causes such as:

SQL statements that cannot be shared
Large numbers of child cursors
Insufficient shared pool or another SGA area
Cursor invalidation caused by DDL and other operations
Application design
Oracle Database defects or memory leaks

Summary: Principles of Oracle Memory Tuning

The following points are important when tuning Oracle Database memory.

Understand the SGA as shared memory and the PGA as process-specific private memory
Do not increase the buffer cache simply because physical I/O is high
When hard parses are high, investigate the shared pool size and SQL shareability separately
Check the number of Optimal, One-pass, and Multi-pass executions for the PGA
Understand that PGA_AGGREGATE_TARGET is a target rather than an upper limit
Use PGA_AGGREGATE_LIMIT for an absolute limit
Use ASMM instead of AMM when using HugePages on Linux
Determine memory sizes based on advisor estimates and actual processing time
Save and compare statistics before and after memory changes
Do not increase the SGA or PGA to the point where operating system swapping occurs

Appropriate Memory Tuning
       =
Oracle Statistics
       +
Operating System Memory Status
       +
SQL and Workload Analysis
       +
Before-and-After Comparison

Rather than determining memory sizes based on intuition or fixed ratios, use information from V$DB_CACHE_ADVICE, V$SHARED_POOL_ADVICE, V$PGA_TARGET_ADVICE, and the actual workload. This approach helps ensure stable Oracle Database operation.

※This article covers Oracle Database 19c. Behavior and availability may differ depending on the Release Update, operating system, edition, licensing, and CDB/PDB configuration. Before making changes to a production environment, check the Oracle documentation for the applicable version.

[reference]
Oracle Database Database Performance Tuning Guide, 19c

Complete Guide to Oracle Memory Architecture: SGA, PGA Structures, Management Methods, and Monitoring

Proper memory management is essential to achieving stable operation and high performance in Oracle Database.This article…