Why Oracle Uses PGA Instead of SGA for Large Table Scans and How to Optimize It
Alireza Kamrani
02/27/2025
When selecting from large tables, Oracle might use PGA instead of SGA, depending on memory settings, query complexity, and table size. Understanding how full table scans, SGA caching, and PGA memory allocation interact can help optimize performance.
Direct path read:
The session is waiting for a direct read to complete. A direct read is a physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.
If asynchronous I/O is supported (and in use), then Oracle can submit I/O requests and continue processing. Oracle can then pick up the results of the I/O request later and wait on "direct path read" until the required I/O completes.
If asynchronous I/O is not being used, then the I/O requests block until completed but these do not show as waits at the time the I/O is issued. The session returns later to pick up the completed I/O data but can then show a wait on "direct path read" even though this wait will return immediately.
Hence this wait event is very misleading because:
• The total number of waits does not reflect the number of I/O requests
• The total time spent in "direct path read" does not always reflect the true wait time.
This style of read request is typically used for:
• Sort I/O (when a sort does not fit in memory)
• Parallel Query slaves
• Read ahead (where a process may issue an I/O request for a block it expects to need in the near future)
Why Oracle Uses PGA Instead of SGA in Large Table Queries
1. Full Table Scans and Direct Path Reads (Bypassing SGA)
• When performing a full table scan on a very large table, Oracle may choose direct path reads, bypassing the SGA (Buffer Cache) and reading data directly into PGA.
• This happens when:
• The table is too large to fit into DB_CACHE_SIZE.
• The query requests a parallel execution plan.
• The system is under memory pressure, and Oracle determines that using PGA is more efficient than filling up SGA with temporary data.
• Result: Data is not cached in SGA, meaning future queries on the same table will not benefit from cache reuse, leading to repeated disk I/O.
2. Impact of DB_BIG_TABLE_CACHE_PERCENT_TARGET
Oracle introduced the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter to control large table caching in SGA.
• By default, large table scans use direct path reads (bypassing the SGA).
• If DB_BIG_TABLE_CACHE_PERCENT_TARGET is set, Oracle will attempt to cache full table scan results in a special area of the buffer cache, reducing repeated disk I/O.
Example Configuration:
ALTER SYSTEM SET DB_BIG_TABLE_CACHE_PERCENT_TARGET = 30;
• Effect: Reserves 30% of the buffer cache for full table scans, reducing reliance on direct path reads and keeping frequently scanned large tables in memory.
3. When PGA is Used More Than SGA
Oracle moves sorting and temporary operations to PGA instead of SGA in the following cases:
(a) Sort and Hash Joins on Large Datasets
• If a query involves sorting, aggregations, or hash joins, Oracle may store intermediate results in PGA rather than SGA.
• If the PGA_AGGREGATE_TARGET is too small, Oracle will spill temporary data to TEMP tablespace, slowing down performance.
Optimization:
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 4G; --adjust based on your workload.
(b) Large Full Table Scans Without Proper Caching
• If DB_BIG_TABLE_CACHE_PERCENT_TARGET is not set, Oracle bypasses the buffer cache for large tables, forcing data to be processed in PGA or TEMP tablespace.
• This is inefficient for frequently accessed large tables.
Solution: Enable Large Table Caching
ALTER SYSTEM SET DB_BIG_TABLE_CACHE_PERCENT_TARGET = 25;
• Now, Oracle will try to keep large table scans in memory, avoiding repeated disk reads.
4. Should You Keep Large Tables in SGA or Let Oracle Use PGA?
• Frequently scanned large tables
Recommendation: Enable DB_BIG_TABLE_CACHE_PERCENT_TARGET.
• One-time full table scans (ad hoc queries)
Recommendation: Allow PGA direct path reads.
• Large table joins with sorting/hashing
Recommendation: Increase PGA_AGGREGATE_TARGET.
• System has low memory
Recommendation: Use direct path reads to reduce SGA pressure.
• Keeping large tables in SGA (DB_BIG_TABLE_CACHE_PERCENT_TARGET) is beneficial only if they are frequently accessed.
• For one-time scans or infrequent queries, direct path reads using PGA are better, as they prevent SGA pollution (filling cache with unnecessary data).
5. Monitoring Full Table Scan Behavior
To check if Oracle is using direct path reads (PGA) instead of SGA:
SELECT name, value FROM v$sysstat WHERE name IN ('table scans (direct read)', 'table scans (long tables)', 'physical reads direct');
• High "physical reads direct" → Indicates full table scans are bypassing the buffer cache (using PGA).
• High "table scans (long tables)" → Oracle is scanning large tables frequently.
If direct path reads are too high and performance is suffering, consider increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET.
Conclusion
• Oracle uses PGA instead of SGA for large table scans when full table scans are performed via direct path reads.
• Setting DB_BIG_TABLE_CACHE_PERCENT_TARGET helps keep frequently accessed large tables in memory to avoid repeated I/O.
• For queries involving sorting, hashing, or aggregations, PGA tuning is critical (PGA_AGGREGATE_TARGET should be increased to prevent excessive disk usage).
• Use SGA for frequently accessed tables and PGA for complex sorting and temporary operations.
Alireza Kamrani, Database Consultant/ACE
No comments:
Post a Comment