Introduction
Memory is one of the most critical resources in an Oracle Database. A well-tuned memory configuration reduces disk I/O, improves SQL execution speed, minimizes parsing overhead, and enhances overall database performance.
Oracle divides its memory into two major components:
- System Global Area (SGA) – Shared memory used by all database processes.
- Program Global Area (PGA) – Private memory allocated to each server process.
Improper memory sizing can lead to issues such as ORA-04031, excessive physical reads, high CPU usage, disk-based sorting, poor SQL performance, and increased application response time.
In this guide, we’ll explore Oracle’s memory architecture, explain every SGA and PGA component, discuss common performance issues, and provide practical tuning techniques used in production environments.
Oracle Memory Architecture
Oracle memory is broadly divided into:
Oracle Database Memory
+---------------------------+
| Oracle Instance |
+---------------------------+
|
-----------------------------------------
| |
+--------------------+ +--------------------+
| SGA | | PGA |
| Shared Memory | | Private Memory |
| Shared by Sessions | | Per Server Process |
+--------------------+ +--------------------+
SGA (System Global Area)
The SGA is allocated when the Oracle instance starts and is shared among all users and background processes.
Its main goal is to:
- Cache frequently accessed data
- Reduce disk I/O
- Improve SQL execution
- Store SQL execution plans
- Manage redo information
PGA (Program Global Area)
The PGA is allocated separately for each server process.
It stores:
- Session variables
- Sort operations
- Hash joins
- Bitmap merge operations
- Cursor information
Unlike the SGA, the PGA is not shared.
Components of the SGA
1. Shared Pool
The Shared Pool stores:
- Parsed SQL statements
- Execution plans
- Data Dictionary Cache
- Library Cache
- PL/SQL code
Benefits
- Reduces hard parsing
- Improves SQL reuse
- Saves CPU resources
Symptoms of an Undersized Shared Pool
- ORA-04031
- High hard parse rate
- Library cache misses
- Increased CPU usage
Monitoring
SELECT * FROM V$SGASTAT
WHERE POOL='shared pool';
2. Database Buffer Cache
The Buffer Cache stores recently accessed data blocks in memory.
Instead of reading blocks from disk repeatedly, Oracle retrieves them from the cache.
Benefits
- Reduces physical reads
- Improves response time
- Minimizes disk I/O
Common Problems
- High physical reads
- Buffer cache contention
- Low cache hit ratio
Monitoring
SELECT
physical_reads,
db_block_gets,
consistent_gets
FROM V$SYSSTAT;
3. Redo Log Buffer
Stores redo entries before LGWR writes them to the online redo logs.
Too Small?
May cause:
- log buffer space waits
- Increased commit latency
Monitor
SELECT *
FROM V$SYSSTAT
WHERE NAME LIKE '%redo%';
4. Large Pool
Large Pool is optional but highly recommended.
Used for:
- RMAN backup
- Shared Server
- Parallel Query
- I/O server processes
Benefits:
- Prevents Shared Pool fragmentation
5. Java Pool
Stores memory for Java execution inside Oracle.
Required only if Java Stored Procedures or JVM features are used.
6. Streams Pool
Used by:
- Oracle Streams
- Oracle GoldenGate Integrated Capture
- Advanced Queuing
Components of the PGA
Unlike the SGA, PGA is allocated per server process.
Sort Area
Used during:
- ORDER BY
- GROUP BY
- DISTINCT
If insufficient:
Oracle performs disk-based sorting.
Hash Area
Used by:
- Hash Join
- Hash Aggregation
Insufficient PGA causes temporary tablespace usage.
Bitmap Merge Area
Used during bitmap index processing.
Session Memory
Stores:
- Session variables
- PL/SQL variables
- Runtime information
Cursor Area
Stores execution state of SQL cursors.
Automatic Memory Management (AMM)
Oracle can automatically manage both SGA and PGA.
Parameters:
MEMORY_TARGET
MEMORY_MAX_TARGET
Advantages:
- Easy administration
- Dynamic memory allocation
- Suitable for most environments
Automatic Shared Memory Management (ASMM)
Oracle manages only the SGA automatically.
Parameters:
SGA_TARGET
SGA_MAX_SIZE
PGA_AGGREGATE_TARGET
Preferred in many production systems because it gives DBAs more control over PGA sizing.
Manual Memory Management
Every memory component is configured manually.
Examples:
SHARED_POOL_SIZE
DB_CACHE_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
LOG_BUFFER
Recommended only for specialized workloads.
Key Dynamic Performance Views
|
View |
Purpose |
|
V$SGA |
Displays SGA allocation |
|
V$SGASTAT |
SGA component statistics |
|
V$PGASTAT |
PGA statistics |
|
V$PROCESS_MEMORY |
Process memory usage |
|
V$MEMORY_TARGET_ADVICE |
AMM sizing advice |
|
V$SGA_TARGET_ADVICE |
SGA sizing advice |
|
V$ACTIVE_SESSION_HISTORY |
Memory-related waits |
|
V$SYSSTAT |
System statistics |
Common Memory Bottlenecks
ORA-04031
Cause
Unable to allocate memory from Shared Pool.
Fix
- Increase Shared Pool
- Flush Shared Pool (temporary)
- Reduce hard parsing
- Use bind variables
High Physical Reads
Cause
Buffer Cache too small or inefficient SQL.
Fix
- Tune SQL
- Add indexes
- Increase DB_CACHE_SIZE if justified
Excessive Hard Parsing
Cause
Applications not using bind variables.
Fix
- Use bind variables
- Increase Shared Pool
- Enable cursor sharing where appropriate
PGA Spills to Disk
Cause
PGA too small.
Symptoms
- Temporary tablespace growth
- Slow hash joins
- Slow sorts
Fix
Increase:
PGA_AGGREGATE_TARGET
Memory Advisory Views
SGA Advice
SELECT *
FROM V$SGA_TARGET_ADVICE;
Shows estimated performance improvement for different SGA sizes.
PGA Advice
SELECT *
FROM V$PGA_TARGET_ADVICE;
Displays expected cache hit percentages for various PGA sizes.
Useful Monitoring Queries
Current SGA Size
SELECT *
FROM V$SGA;
Shared Pool Usage
SELECT *
FROM V$SGASTAT
WHERE POOL='shared pool';
PGA Statistics
SELECT *
FROM V$PGASTAT;
Process Memory
SELECT *
FROM V$PROCESS_MEMORY;
Buffer Cache Statistics
SELECT
NAME,
VALUE
FROM V$SYSSTAT
WHERE NAME IN
('physical reads',
'db block gets',
'consistent gets');
Performance Tuning Workflow
User reports slow application
│
▼
Collect AWR Report
│
▼
Check Memory Statistics
│
▼
Review Wait Events
│
▼
Analyze SGA & PGA Usage
│
▼
Identify Bottleneck
│
▼
Tune Memory Parameters
│
▼
Validate Performance
Best Practices
- Use ASMM or AMM unless manual tuning is required.
- Keep optimizer statistics current before adjusting memory.
- Monitor AWR and ASH regularly for memory-related waits.
- Avoid increasing SGA or PGA without evidence from advisory views.
- Use bind variables to reduce hard parsing.
- Review V$SGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE before resizing memory.
- Monitor temporary tablespace usage to detect PGA shortages.
- Validate changes under production-like workloads.
Real-World Scenario
A financial application experienced slow reporting during month-end processing.
Investigation
- AWR showed high direct path read temp waits.
- V$PGASTAT indicated a low PGA cache hit percentage.
- Large hash joins were spilling to the temporary tablespace.
Resolution
- Increased PGA_AGGREGATE_TARGET from 8 GB to 16 GB.
- Optimized SQL joins and updated optimizer statistics.
- Reviewed execution plans to reduce unnecessary sorting.
Outcome
- Temporary tablespace usage decreased significantly.
- Report execution time dropped from 28 minutes to under 6 minutes.
- CPU utilization became more stable during peak reporting.
Conclusion
Oracle memory tuning is about allocating the right amount of memory to the right components based on workload characteristics, not simply increasing RAM. A properly sized SGA reduces disk I/O and parsing overhead, while an optimized PGA improves sort and join performance by minimizing disk spills.
By regularly monitoring AWR reports, advisory views, dynamic performance views, and wait events, DBAs can make informed tuning decisions that improve database responsiveness, scalability, and overall system stability.
Comments
Post a Comment