Proper memory management is essential to achieving stable operation and high performance in Oracle Database.
This article explains the structure of Oracle’s memory areas—SGA, PGA, UGA, and software code areas—along with detailed descriptions of memory management methods (AMM, ASMM, manual), monitoring techniques, and practical SQL examples using dynamic views, all supported by easy-to-understand text diagrams.
- 1. Basic Memory Structure in Oracle
- 2. Overview of Memory Management Methods
- 3. Text Diagram: Memory Management Decision Flow
- 4. SGA and PGA Components & Roles
- 5. Parameter Examples for Each Management Method
- 6. Monitoring Views for Memory Usage
- 7. V$MEMORY_DYNAMIC_COMPONENTS Column Definitions
- 8. Benefits and Risks of Memory Configuration
- 9. Text Diagram: Memory Allocation by Management Method
- Conclusion
1. Basic Memory Structure in Oracle
When an Oracle instance starts, the following memory areas are allocated:
[Oracle Database Memory Structure]
+------------------------------------------------------+
| Software Code Areas (Executable Code Area) |
+------------------------------------------------------+
| ┌────────────┬──────────┬───────────┐ |
| │ SGA │ PGA │ UGA │ |
| │ (Shared) │ (Private)│ (User Info)│ |
| └────────────┴──────────┴───────────┘ |
| |
| ・SGA: Shared across all server/background processes |
| ・PGA: Private to each server/background process |
| ・UGA: Holds session-specific info, varies by config |
+------------------------------------------------------+
- SGA (System Global Area)
A shared memory area storing cached data, SQL parse results, and control information. - PGA (Program Global Area)
Private memory for operations like sorting and hashing, allocated per process. - UGA (User Global Area)
Session-specific memory; located in the PGA (dedicated server) or SGA (shared server). - Software Code Area
Read-only memory area storing Oracle executable code.
2. Overview of Memory Management Methods
Oracle supports the following types of memory management:
2.1 AMM (Automatic Memory Management)
With MEMORY_TARGET, Oracle automatically manages both SGA and PGA.
ALTER SYSTEM SET MEMORY_TARGET = 2G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 4G SCOPE=SPFILE;
2.2 ASMM (Automatic Shared Memory Management)
Specify SGA_TARGET and PGA_AGGREGATE_TARGET separately.
Only SGA is auto-managed; PGA uses the defined target.
ALTER SYSTEM SET SGA_TARGET = 1.5G SCOPE=BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 512M SCOPE=BOTH;
ALTER SYSTEM SET SGA_MAX_SIZE = 2G SCOPE=SPFILE;
2.3 Manual Memory Management
Use fixed parameters like SHARED_POOL_SIZE, DB_CACHE_SIZE, etc.
Not recommended due to operational overhead.
3. Text Diagram: Memory Management Decision Flow
[Memory Management Method Flow]
User decides configuration
↓
┌────────────────────┐
│ Memory Management │
└────────────────────┘
↓ ↓ ↓
AMM ASMM+PGA Manual
(MEMORY_) (SGA_) (manual params)
↓ ↓ ↓
Oracle dynamically allocates memory (for AMM/ASMM)
4. SGA and PGA Components & Roles
4.1 SGA Components
| Component | Description |
|---|---|
| Shared Pool | Stores SQL parse results, PL/SQL, and dictionary info |
| Database Buffer Cache | Caches datafile blocks |
| Redo Log Buffer | Temporarily holds redo before writing to logs |
| Java Pool | Memory for Java stored procedures and JVM |
| Large Pool | Used by RMAN, parallel queries, shared server |
| Streams Pool | Memory for Oracle Streams (rarely used today) |
4.2 PGA Overview
- Used for sorts, hash joins, and work area memory
4.3 UGA Overview
- Stores session data, cursors, etc.
- Located in PGA (dedicated server) or SGA (shared server)
5. Parameter Examples for Each Management Method
| Management Type | Parameters |
|---|---|
| AMM | MEMORY_TARGET, MEMORY_MAX_TARGET |
| ASMM | SGA_TARGET, SGA_MAX_SIZE, PGA_AGGREGATE_TARGET |
| Manual | SHARED_POOL_SIZE, DB_CACHE_SIZE, LOG_BUFFER, etc. |
6. Monitoring Views for Memory Usage
Use the following dynamic performance views for monitoring:
| View Name | Description |
|---|---|
V$SGAINFO | Overall SGA memory allocation |
V$PGASTAT | PGA usage statistics |
V$SGASTAT | Detailed SGA allocations |
V$MEMORY_DYNAMIC_COMPONENTS | Dynamic SGA components, current sizes and history |
SQL Example:
SELECT COMPONENT, CURRENT_SIZE FROM V$MEMORY_DYNAMIC_COMPONENTS;
Command execution example
SQL> SELECT COMPONENT, CURRENT_SIZE FROM V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
---------------------------------------- ------------
shared pool 234881024
large pool 16777216
java pool 16777216
streams pool 0
unified pga pool 0
SGA Target 922746880
memoptimize buffer cache 0
DEFAULT buffer cache 587202560
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 50331648
Data Transfer Cache 0
In-Memory Area 0
In Memory RW Extension Area 0
In Memory RO Extension Area 0
PGA Target 620756992
ASM Buffer Cache 0
22 rows selected.
7. V$MEMORY_DYNAMIC_COMPONENTS Column Definitions
This view summarizes memory resizing operations for dynamic SGA components. All sizes are in bytes.
| Column Name | Data Type | Description |
|---|---|---|
| COMPONENT | VARCHAR2(64) | Component name |
| CURRENT_SIZE | NUMBER | Current size |
| MIN_SIZE | NUMBER | Minimum size since startup |
| MAX_SIZE | NUMBER | Maximum size since startup |
| USER_SPECIFIED_SIZE | NUMBER | Value from user-defined parameter |
| OPER_COUNT | NUMBER | Number of operations since startup |
| LAST_OPER_TYPE | VARCHAR2(13) | Last operation (e.g., GROW, SHRINK) |
| LAST_OPER_MODE | VARCHAR2(9) | Mode (e.g., IMMEDIATE) |
| LAST_OPER_TIME | DATE | Time of last operation |
| GRANULE_SIZE | NUMBER | Size of memory granule |
8. Benefits and Risks of Memory Configuration
✅ Benefits of Proper Configuration
- Faster SQL parsing (Shared Pool)
- Reduced disk I/O (Buffer Cache)
- Stable transaction processing (Redo Log Buffer)
- Stable parallel/RMAN operations (Large Pool)
⚠️ Risks of Misconfiguration
- Frequent SQL reparsing → High CPU load
- Excessive disk I/O → Slow performance
- Redo flush delays → Commit bottlenecks
- Insufficient PGA → Sort failures, excessive TEMP usage
9. Text Diagram: Memory Allocation by Management Method
[Memory Allocation by Method]
+-----------------------------------+
| MEMORY_TARGET = 4G |
+-----------------------------------+
| [ AMM - AUTO ] |
| +--------------+-------------+ |
| | SGA | PGA | |
| +--------------+-------------+ |
+-----------------------------------+
+--------------------------------+
| [ ASMM + PGA_AGGREGATE ] |
| SGA: dynamic / PGA: fixed |
+--------------------------------+
+--------------------------------+
| [ Manual Management ] |
| Each area set individually |
+--------------------------------+
Conclusion
Memory management is one of the most critical components in Oracle Database performance.
By utilizing AMM or ASMM, administrators can offload the complexity of manual tuning and let Oracle dynamically adjust memory for changing workloads.
However, improper memory configuration can lead to severe performance degradation or transactional errors.
It is essential to monitor views regularly and validate parameter settings to ensure optimal and stable database operation.
[reference]
5.4 Managing Memory
コメント