The performance of an Oracle database is heavily dependent on its memory configuration. However, the SGA and PGA often feel like a black box—tune it too large, and you risk wasting resources; tune it too small, and you risk a crash. Have you ever been stumped by an ORA-04031 error? Don’t worry, this journey into memory exploration will guide you out of the confusion. This article will start from the internal principles of SGA and PGA, culminating in a quantifiable, data-driven optimization methodology to end the guesswork in memory tuning.
I. SGA and PGA: The Heart and Brain of the Database
In the world of Oracle, memory is the lifeline of performance. All data processing, SQL parsing, and transaction recording rely on memory. The two most important players in this arena are the SGA (System Global Area) and the PGA (Program Global Area).
1. SGA (System Global Area): The Shared Central Hub
The SGA is a large block of shared memory allocated when an Oracle instance starts up, accessible to all database processes. You can think of it as a company’s central operations hall, where various departments (processes) share resources and work together.
- Concept Deep Dive -> Principle Analysis
- Database Buffer Cache: This is the largest and most crucial component of the SGA. It’s the busiest “public reading room” in our operations hall analogy. When a user needs to read data, Oracle first checks here. If the data is found (a cache hit), it’s returned directly from memory, avoiding costly disk I/O. If not found, the data is read from disk and placed into the Buffer Cache for future use. A well-configured Buffer Cache can significantly improve read/write performance.
- Shared Pool: This is the company’s “central conference room and archive.” It primarily caches two things: execution plans for SQL and PL/SQL code (Library Cache) and data dictionary information (Data Dictionary Cache). When an SQL statement is submitted, Oracle first looks in the Shared Pool for an identical execution plan. If one exists, it’s reused, saving the cost of reparsing and optimization—a process known as a “soft parse.” A heavily fragmented or undersized Shared Pool can lead to numerous “hard parses,” which severely consume CPU.
- Log Buffer: This is the company’s “outgoing mail tray.” Redo information generated by all DML operations (INSERT, UPDATE, DELETE) is quickly written to this buffer and then written in batches to the redo log files by the LGWR process. It generally doesn’t need to be very large, but an appropriately sized Log Buffer is vital for high-concurrency DML systems.
- Large Pool: This is an optional “multi-purpose hall” reserved for operations that require large, contiguous chunks of memory, such as RMAN backups and parallel queries. Separating it from the Shared Pool helps prevent fragmentation.
- Tuning Practice / Problem Diagnosis A common misconception is that a larger SGA always equals better performance. However, an oversized SGA can not only waste physical memory but also introduce negative effects due to management overhead. We need data to guide our decisions.
2. PGA (Program Global Area): The Private Office
Unlike the “shared” nature of the SGA, the PGA is a private memory area allocated for each server process, and they do not interfere with each other. You can imagine it as a private office assigned to each employee in the company.
-
Concept Deep Dive -> Principle Analysis When your SQL needs to perform sorting (
ORDER BY,GROUP BY) or hash joins, these operations can be completed efficiently in memory (Optimal Execution) if enough PGA memory is allocated. If the PGA is insufficient, Oracle must use the temporary tablespace on disk, leading to “one-pass” or even “multi-pass” executions, which drastically degrade performance. -
Tuning Practice / Problem Diagnosis Another common mistake is to think that the bigger the PGA, the better. But remember, the total PGA consumption is
pga_aggregate_targetmultiplied by the number of concurrent processes. An out-of-control PGA configuration can easily exhaust all server memory. We need to find a balance between performance and resource consumption.
II. Automatic or Manual? AMM vs. ASMM
Oracle provides two main automatic memory management mechanisms to free DBAs from tedious manual parameter tuning.
- Automatic Memory Management (AMM): Enabled by setting the
MEMORY_TARGETandMEMORY_MAX_TARGETparameters. Oracle dynamically adjusts memory allocation between the SGA and PGA. It’s like “full self-driving”—simple and convenient, suitable for systems with relatively stable workloads. - Automatic Shared Memory Management (ASMM): Enabled by setting
SGA_TARGETandPGA_AGGREGATE_TARGET. Oracle only dynamically adjusts memory among the internal components of the SGA (like Buffer Cache, Shared Pool), while the PGA size is controlled independently byPGA_AGGREGATE_TARGET. This is more like “assisted driving,” giving the DBA more control over the total SGA and PGA sizes.
In my personal experience, I recommend using ASMM in the vast majority of production environments. It strikes a better balance between stability and flexibility, avoiding the intense memory “contention” between SGA and PGA that can occur under extreme loads with AMM, thus providing more predictable performance.
- Practical Demo: How to check the current memory management mode?
-- Oracle 19c
-- Check the values of MEMORY_TARGET, SGA_TARGET, and PGA_AGGREGATE_TARGET
SHOW PARAMETER TARGET;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_big_table_cache_percent_target string 0
db_flashback_retention_target integer 720
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
parallel_servers_target integer 768
pga_aggregate_target big integer 10G
sga_target big integer 40G
target_pdbs integer 0
-- Logical check:
-- 1. If MEMORY_TARGET is set to a non-zero value, it's in AMM mode.
-- 2. If MEMORY_TARGET is 0 and SGA_TARGET is non-zero, it's in ASMM mode.
-- 3. If all three are 0, it's in manual management mode (highly discouraged).
III. Data-Driven Decisions: Let the Advisor Views Guide You
Guesswork has no place in database optimization. Oracle provides a series of powerful “Advisor Views” that offer precise tuning recommendations based on real workload data collected since the instance started.
1. SGA Target Advisor (v$sga_target_advice)
This view predicts the impact of different SGA_TARGET sizes on the database’s total physical reads (and consequently, DB Time).
- Practical Demo
-- Predict the benefit of adjusting SGA size
-- ESTD_DB_TIME is the estimated DB Time, ESTD_PHYSICAL_READS are estimated physical reads
SELECT
sga_size,
sga_size_factor,
estd_db_time,
estd_physical_reads
FROM
v$sga_target_advice
ORDER BY
sga_size_factor;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_PHYSICAL_READS
---------- --------------- ------------ -------------------
15360 .375 57884978 9.8523E+10
20480 .5 40363977 4.5410E+10
25600 .625 31660469 1.8920E+10
30720 .75 29475772 1.2152E+10
35840 .875 28610035 9647829746
40960 1 27139096 5163132691
46080 1.125 26354776 2766922809
51200 1.25 26270645 2397242508
56320 1.375 26251648 2397242508
61440 1.5 26248934 2162836284
66560 1.625 26248934 1770438200
71680 1.75 26246220 1345512379
76800 1.875 26243506 1181324760
81920 2 26243506 1181324760
14 rows selected.
Interpreting the Results: Focus on the row where SGA_SIZE_FACTOR is 1; this is your current configuration. See if increasing SGA_SIZE (SGA_SIZE_FACTOR > 1) significantly reduces ESTD_DB_TIME. If doubling the SGA only decreases DB Time by 1%, the return on investment is low. Conversely, if reducing the SGA by 20% causes DB Time to spike, your current SGA configuration may be insufficient.
2. PGA Target Advisor (v$pga_target_advice)
This view helps assess whether the PGA_AGGREGATE_TARGET is set appropriately.
- Practical Demo
-- Evaluate PGA size setting
-- ESTD_PGA_CACHE_HIT_PERCENTAGE: Estimated PGA cache hit percentage
-- ESTD_OVERALLOC_COUNT: Estimated number of PGA overallocations
SELECT
pga_target_for_estimate / 1024 / 1024 AS target_mb,
pga_target_factor,
advice_status,
estd_pga_cache_hit_percentage,
estd_overalloc_count
FROM
v$pga_target_advice;
TARGET_MB PGA_TARGET_FACTOR ADV ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- --- ----------------------------- --------------------
1280 .125 ON 53 1694600
2560 .25 ON 53 1694587
5120 .5 ON 55 1521626
7680 .75 ON 66 1036977
10240 1 ON 99 127685
12288 1.2 ON 100 461
14336 1.4 ON 100 141
16384 1.6 ON 100 4
18432 1.8 ON 100 0
20480 2 ON 100 0
30720 3 ON 100 0
40960 4 ON 100 0
61440 6 ON 100 0
81920 8 ON 100 0
14 rows selected.
Interpreting the Results: The goal is to get ESTD_PGA_CACHE_HIT_PERCENTAGE as close to 100% as possible, while keeping ESTD_OVERALLOC_COUNT (PGA memory overallocation, which can cause system instability) at 0. If the hit percentage for your current configuration (PGA_TARGET_FACTOR=1) is low, consider increasing PGA_AGGREGATE_TARGET based on the recommendations for rows with a larger factor.
3. Other Key Views
v$pgastat: Provides a snapshot of current PGA memory usage.
-- Check PGA memory usage statistics
SELECT name, value/1024/1024 as value_mb, unit FROM v$pgastat;
NAME VALUE_MB UNIT
--------------------------------------- ---------- -------
aggregate PGA target parameter 10240 bytes
aggregate PGA auto target 640 bytes
global memory bound 1023.99414 bytes
total PGA inuse 11122.7998 bytes
total PGA allocated 11959.834 bytes
maximum PGA allocated 18023.3662 bytes
total freeable PGA memory 501.375 bytes
MGA allocated (under PGA) 1236 bytes
maximum MGA allocated 1236 bytes
process count .00057888
max processes count .000677109
PGA memory freed back to OS 716819546 bytes
total PGA used for auto workareas .96875 bytes
maximum PGA used for auto workareas 6710.36914 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 28.21875 bytes
over allocation count .401830673
bytes processed 294570439 bytes
extra bytes read/written 3046394.24 bytes
cache hit percentage .000094385 percent
recompute count (total) 6.36017418
21 rows selected.
-- Key Metrics:
-- 'aggregate PGA target parameter': Current PGA_AGGREGATE_TARGET setting
-- 'total PGA allocated': Total PGA currently allocated
-- 'maximum PGA allocated': Peak PGA allocation since instance startup
This view helps you determine if the peak PGA usage is approaching or exceeding your target value.
v$sga_dynamic_components: Shows how the sizes of SGA components have changed under ASMM.
-- View dynamic resizing of SGA components
SELECT
component,
current_size / 1024 / 1024 AS current_size_mb,
min_size / 1024 / 1024 AS min_size_mb,
max_size / 1024 / 1024 AS max_size_mb,
last_oper_type || ' ' || last_oper_mode AS last_operation
FROM
v$sga_dynamic_components
WHERE
current_size > 0;
COMPONENT CURRENT_SIZE_MB MIN_SIZE_MB MAX_SIZE_MB LAST_OPERATION
------------------------ --------------- ----------- ----------- ----------------
shared pool 7680 6912 8704 GROW DEFERRED
large pool 640 640 640 STATIC
java pool 128 128 128 STATIC
streams pool 128 128 256 SHRINK DEFERRED
DEFAULT buffer cache 32128 30976 32896 SHRINK DEFERRED
Shared IO Pool 128 128 128 STATIC
6 rows selected.
This helps you understand how ASMM is reallocating memory internally.
IV. Classic Troubleshooting: Dissecting ORA-04031
ORA-04031: unable to allocate X bytes of shared memory is one of the most dreaded errors for a DBA. It typically points to insufficient space or severe fragmentation in the Shared Pool.
- Root Cause Analysis:
- Shared Pool is too small: An improperly set
SGA_TARGETleaves the Shared Pool with insufficient memory for current SQL parsing and data dictionary caching needs. - Severe Fragmentation: A large volume of SQL without bind variables (leading to hard parses) creates many non-shareable execution plans. These quickly fill up and fragment the Shared Pool, making it impossible to find a large enough contiguous block of memory for new requests, even if the total free space is adequate.
- Bugs or Memory Leaks: In rare cases, it could be an Oracle bug.
- Shared Pool is too small: An improperly set
- Troubleshooting and Resolution:
- Immediate Fix:
ALTER SYSTEM FLUSH SHARED_POOL;can provide temporary relief, but it’s a bandage solution. It clears all cached execution plans, causing performance fluctuations as they are rebuilt. - Permanent Solution:
- Promote Bind Variables: This is paramount. Review application code to ensure all SQL uses bind variables, reducing hard parses at the source. You can query
v$sqlareato find SQL with lowEXECUTIONSbut highVERSION_COUNT. - Proper Configuration: Increase
SGA_TARGETbased on recommendations fromv$sga_target_advice. - Use Cursor Caching: Setting the
SESSION_CACHED_CURSORSparameter can cache session-level cursors, reducing the overhead of soft parses.
- Promote Bind Variables: This is paramount. Review application code to ensure all SQL uses bind variables, reducing hard parses at the source. You can query
- Immediate Fix:
Conclusion
Oracle memory management is not arcane magic. The key lies in understanding the synergy between SGA and PGA and shifting from experience-based tuning to a data-driven approach.
- Understanding is Fundamental: Knowing the roles of the Buffer Cache, Shared Pool, and PGA is the prerequisite for making sound decisions.
- Advisors are the Scientific Method: Stop guessing. Learn to use views like
v$sga_target_adviceandv$pga_target_adviceto let Oracle provide data-backed tuning suggestions. - Solving Problems is the Goal: Use typical issues like
ORA-04031as a starting point to trace back and identify bottlenecks and inefficiencies in your system’s memory usage.
Memory optimization is a continuous cycle of monitoring, analysis, and adjustment, not a one-time task. Incorporate the methods from this article into your daily operations, and your database will reward you with more stable and robust performance.