Optimizing Oracle PGA & SGA with Target Advice Queries

Proper memory management is crucial for Oracle Database performance. PGA (Program Global Area) and SGA (System Global Area) play a key role in optimizing query performance, reducing disk I/O, and improving overall system efficiency. Oracle provides advisory views to help determine the ideal PGA & SGA target sizes.

This article provides step-by-step SQL queries to analyze PGA & SGA usage, get tuning recommendations, and optimize memory allocation.

1️⃣ Checking Current PGA & SGA Allocations

Before making changes, check the current memory allocation:

COL "Parameter Name" FORMAT A30
COL "Size (GB)" FORMAT 9999.99

SELECT
    NAME AS "Parameter Name",
    ROUND(VALUE/1024/1024/1024, 2) AS "Size (GB)"
FROM V$PARAMETER
WHERE NAME IN ('sga_target', 'pga_aggregate_target', 'memory_target');

Output Example:

Parameter Name	Size (GB)
sga_target	16.00
pga_aggregate_target	4.00
memory_target	20.00

2️⃣ Analyzing SGA Target Advice

The following query provides SGA tuning recommendations based on workload analysis:

COL "SGA Size Factor" FORMAT 99.99
COL "SGA Size (GB)" FORMAT 9999.99
COL "Estimated DB Time (sec)" FORMAT 99999999
COL "Estimated Physical Reads (GB)" FORMAT 9999999.99

SELECT
    SGA_SIZE_FACTOR AS "SGA Size Factor",
    ROUND(SGA_SIZE/1024, 2) AS "SGA Size (GB)",
    ESTD_DB_TIME AS "Estimated DB Time (sec)",
    ROUND(ESTD_PHYSICAL_READS/1024/1024/1024, 2) AS "Estimated Physical Reads (GB)"
FROM V$SGA_TARGET_ADVICE
ORDER BY SGA_SIZE_FACTOR;

Output Example:

SGA Size Factor	SGA Size (GB)	Estimated DB Time (sec)	Estimated Physical Reads (GB)
0.5	8.00	250000	1200.45
1.0	16.00	180000	800.32
1.5	24.00	150000	600.78

🔹 Interpretation: A larger SGA size can reduce disk I/O and improve performance.

3️⃣ Analyzing PGA Target Advice

The following query provides PGA tuning recommendations, converting bytes to GB:

COL "PGA Target Factor" FORMAT 99.99
COL "PGA Target (GB)" FORMAT 9999.99
COL "Estimated Cache Hit %" FORMAT 99.99
COL "Estimated Overallocations" FORMAT 9999999

SELECT
    PGA_TARGET_FACTOR AS "PGA Target Factor",
    ROUND(PGA_TARGET_FOR_ESTIMATE/1024/1024/1024, 2) AS "PGA Target (GB)",
    ESTD_PGA_CACHE_HIT_PERCENTAGE AS "Estimated Cache Hit %",
    ESTD_OVERALLOC_COUNT AS "Estimated Overallocations"
FROM V$PGA_TARGET_ADVICE
ORDER BY PGA_TARGET_FACTOR;

Output Example:

PGA Target Factor	PGA Target (GB)	Estimated Cache Hit %	Estimated Overallocations
0.5	2.00	85.60	1000
1.0	4.00	92.30	50
1.5	6.00	96.10	5

🔹 Interpretation: A higher PGA allocation results in fewer overallocations and improved cache hit ratio.

4️⃣ Checking Memory Target Advice (If AMM is Enabled)

If Automatic Memory Management (AMM) is enabled, use this query to analyze the overall memory_target settings:

COL "Memory Size Factor" FORMAT 99.99
COL "Memory Size (GB)" FORMAT 9999.99
COL "Estimated DB Time" FORMAT 99999999
COL "Estimated Physical Reads (GB)" FORMAT 9999999.99

SELECT
    MEMORY_SIZE_FACTOR AS "Memory Size Factor",
    ROUND(MEMORY_SIZE/1024, 2) AS "Memory Size (GB)",
    ESTD_DB_TIME AS "Estimated DB Time",
    ROUND(ESTD_PHYSICAL_READS/1024/1024/1024, 2) AS "Estimated Physical Reads (GB)"
FROM V$MEMORY_TARGET_ADVICE
ORDER BY MEMORY_SIZE_FACTOR;

Output Example:

Memory Size Factor	Memory Size (GB)	Estimated DB Time	Estimated Physical Reads (GB)
0.5	10.00	240000	1500.22
1.0	20.00	180000	1000.78
1.5	30.00	140000	700.45

5️⃣ Handling PGA_AGGREGATE_LIMIT

If PGA_AGGREGATE_LIMIT is set, it acts as a hard limit on PGA memory usage. When the total PGA memory usage exceeds this limit, Oracle will terminate sessions consuming excessive memory.

How to Check PGA_AGGREGATE_LIMIT

SHOW PARAMETER PGA_AGGREGATE_LIMIT;

Recommendations:

🔹 Ensure the value is set appropriately based on workload.

🔹 If critical sessions are getting terminated, consider increasing PGA_AGGREGATE_LIMIT.

🔹 Monitor with:

SELECT name, value FROM v$parameter WHERE name = 'pga_aggregate_limit';

🔹 If using automatic memory management (AMM), let Oracle dynamically manage it.

Best Practices for PGA & SGA Tuning

🔹 SGA Optimization: Increase SGA if physical reads are high and DB response time is long.
🔹 PGA Optimization: Increase PGA if overallocations are frequent and cache hit ratio is low.
🔹 AMM Consideration: If using memory_target, let Oracle manage the memory dynamically.
🔹 Gradual Adjustments: Avoid making large changes at once—adjust parameters incrementally and monitor impact.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Optimizing Oracle PGA & SGA with Target Advice Queries