When Oracle Uses PGA Instead of SGA for Large Table Scans

Most Oracle DBAs learn early in their careers that the System Global Area (SGA) is the primary memory structure used for caching data blocks. The assumption is simple: data blocks are read from disk, placed in the buffer cache, and reused by future sessions.

However, that assumption doesn’t always hold true in production.

In many real-world workloads , especially analytics queries, reporting jobs, or large batch processing-- . Oracle intentionally bypasses the buffer cache and reads data directly into the Program Global Area (PGA). This behaviour surprises many DBAs when they notice unusual wait events like direct path read, or when frequently queried tables seem to generate repeated disk I/O.

Understanding when Oracle chooses PGA over SGA, particularly during large table scans, is critical for performance tuning. The decision affects:

• Buffer cache efficiency
• Disk I/O patterns
• Query execution performance
• Memory utilization across the instance

In this article, we will walk through why Oracle sometimes skips the buffer cache, how direct path reads work, and what DBAs can do to optimize memory usage for large-table workloads.

Understanding Direct Path Reads in Oracle

One of the key reasons Oracle uses PGA instead of SGA is the Direct Path Read mechanism.

In a typical buffer cache read:

1. Oracle reads blocks from disk
2. Blocks are placed in the SGA buffer cache
3. Sessions read those blocks from cache

With direct path reads, Oracle changes the flow. Instead of caching blocks in SGA, the database:

1. Reads blocks from disk
2. Places them directly into the session’s PGA memory
3. Processes them immediately

This bypasses the buffer cache entirely. This behavior is commonly observed with the wait event:

direct path read This event indicates that Oracle is performing physical I/O directly into session memory.

Why Oracle Does This

Direct path reads are used when caching blocks would provide little long-term benefit.

Typical scenarios include:

• Large full table scans
• Parallel query execution
• Sort operations that exceed memory
• Read-ahead operations for large datasets

From a database engine perspective, filling the buffer cache with massive amounts of one-time data would simply push out useful cached blocks. Instead, Oracle processes the data in PGA and discards it when finished.

Why Full Table Scans Often Bypass the Buffer Cache

When Oracle executes a full table scan on a very large table, it must decide whether caching those blocks makes sense. If the table size is significantly larger than the buffer cache, caching becomes inefficient.

Oracle may therefore choose:

Direct path reads → into PGA

Common triggers that include this are as follows :

1. Very Large Tables

If the table size exceeds the available buffer cache, caching blocks would only cause cache churn.

Oracle instead reads blocks directly into PGA.

2. Parallel Query Execution

Parallel query slaves frequently use direct path reads because:

• Each slave processes separate data ranges
• Caching those blocks would not benefit other sessions

3. Memory Pressure

If the database detects heavy memory pressure in SGA, Oracle may decide that bypassing the cache is more efficient.

Operational Impact

While this approach protects the buffer cache, it also means:

Data blocks are not reused
Each query may trigger fresh disk reads

This can become problematic if the same large table is scanned repeatedly.

Controlling Large Table Caching in Oracle

Oracle introduced a parameter that gives DBAs some control over this behavior:

DB_BIG_TABLE_CACHE_PERCENT_TARGET

This parameter allows Oracle to reserve a portion of the buffer cache specifically for large table scans.

Example configuration:

ALTER SYSTEM SET DB_BIG_TABLE_CACHE_PERCENT_TARGET = 30;

In this case:

• 30% of the buffer cache becomes eligible for large table scan caching

• Frequently scanned large tables may remain in memory

• Disk I/O can be reduced for repeated scans

When This Helps

This parameter is particularly useful for:

• Data warehouse workloads
• Reporting systems
• Analytics queries scanning the same large fact tables

When It May Not Help

If the table is only scanned occasionally, caching those blocks wastes memory.

In such cases, direct path reads are actually the better design choice.

When PGA Usage Becomes Critical

Apart from table scans, Oracle heavily relies on PGA for temporary operations during query execution.

These include:

Sorting

Large sorts require significant memory. If the sort area exceeds available PGA memory, Oracle writes temporary data to the TEMP tablespace, which can slow queries dramatically.

Hash Joins

Hash joins use PGA to build in-memory hash tables. Large joins may exceed memory and spill to disk.

Aggregations and Grouping

Operations such as:

GROUP BY

DISTINCT

ORDER BY

all rely heavily on PGA.

Example PGA Configuration

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 4G;

This parameter defines the target memory budget for PGA usage across all sessions.

Insufficient PGA memory leads to:

• TEMP tablespace usage
• Disk I/O
• Query slowdowns

Monitoring Direct Path Reads and Large Table Scans

DBAs should periodically monitor how often Oracle bypasses the buffer cache.

A simple query:

SELECT name, value
FROM v$sysstat
WHERE name IN (
'table scans (direct read)',
'table scans (long tables)',
'physical reads direct'
);

Key indicators:

physical reads direct High values suggest Oracle is frequently performing direct path reads.
table scans (long tables) Indicates large table scans are common.
table scans (direct read) Shows how often scans bypass the buffer cache.

If these metrics are unexpectedly high, it may be worth reviewing:

• Table size
• Query patterns
• Cache configuration
• Big table cache settings

Choosing Between SGA and PGA for Large Table Workloads

There is no universal rule here. The best approach depends on workload patterns.

Frequently Scanned Large Tables

Recommendation: Enable large table caching. DB_BIG_TABLE_CACHE_PERCENT_TARGET

One-Time Analytical Queries

Recommendation: Allow direct path reads. This avoids polluting the buffer cache.

Large Joins and Sorting Operations

Recommendation: Increase PGA memory using parameter  - PGA_AGGREGATE_TARGET

Memory-Constrained Systems

Recommendation: Prefer PGA-based direct path reads to preserve buffer cache efficiency.

 

Quick Takeaways

• Oracle sometimes bypasses the buffer cache and reads data directly into PGA.

• This behavior typically appears as the direct path read wait event.

• Large table scans often trigger direct path reads to avoid SGA pollution.

• The parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET allows caching of frequently scanned large tables.

• PGA tuning is critical for operations like sorts, hash joins, and aggregations.

• Insufficient PGA memory leads to TEMP tablespace usage and slower queries.

• Monitoring physical reads direct helps identify direct path read activity.

DBA Perspective

In real production environments, direct path reads are often misinterpreted during performance analysis. Many DBAs see a high number of direct path read waits and immediately assume there is an I/O bottleneck. In reality, this wait event can be misleading.

When asynchronous I/O is enabled, Oracle may issue multiple I/O requests and continue processing. The session later waits briefly for completion, which shows up as a direct path read wait even if the actual I/O latency was minimal.

Another common mistake is increasing the buffer cache size unnecessarily.

If your workload consists mostly of large scans, increasing SGA will not help much. Those scans will still bypass the cache.

Instead, the focus should be on:

• PGA sizing
• Query design
• Parallel execution settings OR
• TEMP tablespace performance

Also, keep an eye on TEMP usage spikes during heavy reporting workloads. That is often a sign that PGA is undersized for the workload.

Mini Case Study: Reporting System Causing Heavy Disk I/O

A financial reporting system ran hourly aggregation queries against a 2 TB transaction table.

The DBA team noticed that there are extremely high disk reads and Repeated full table scans, Even there was huge TEMP tablespace usage

Investigation revealed that :

• Direct path reads were being used
• The same table was scanned every hour
• Buffer cache reuse was minimal

Solution:

1. Enabled large table caching

DB_BIG_TABLE_CACHE_PERCENT_TARGET = 20

2. Increased PGA memory

PGA_AGGREGATE_TARGET = 8G

Result:

• Disk I/O dropped significantly
• Query runtime improved by nearly 40%
• TEMP tablespace pressure disappeared

Conclusion

Oracle’s memory architecture is designed to balance performance, scalability, and resource efficiency. While the buffer cache plays a critical role in most OLTP workloads, it isn’t always the best place to store data blocks during large analytical queries.

That’s where direct path reads and PGA memory come into play.

By allowing Oracle to read data directly into process memory, the database avoids filling the buffer cache with massive datasets that may never be reused. This approach protects cache efficiency and improves performance for many large-scale workloads.

However, DBAs must understand when this behavior is beneficial and when it becomes a bottleneck. Frequent scans of the same large tables may justify enabling big table caching, while heavy sorting or hashing workloads often require careful PGA tuning.

Monitoring system statistics, reviewing execution plans, and understanding query patterns are essential parts of optimizing this behavior.

If you manage Oracle databases that support analytics, batch processing, or reporting workloads, it’s worth reviewing how your system handles large table scans and PGA usage.

A small configuration change can often make a big difference in performance.

Some FAQs

1. Why does Oracle bypass the buffer cache for large table scans?

Oracle avoids caching blocks that are unlikely to be reused. Direct path reads allow Oracle to process large datasets without polluting the buffer cache.

2. What does the “direct path read” wait event mean?

It indicates that Oracle is performing physical reads directly into PGA memory rather than the SGA buffer cache.

3. When should I enable DB_BIG_TABLE_CACHE_PERCENT_TARGET?

Enable it when large tables are scanned frequently and repeated disk I/O becomes a performance issue.

4. What happens if PGA_AGGREGATE_TARGET is too small?

Sorts and hash joins spill to the TEMP tablespace, causing extra disk I/O and slower queries.

5. Does increasing the buffer cache reduce direct path reads?

Not always. Large table scans may still bypass the cache depending on query type, execution plan, and memory strategy.

How does your Oracle environment handle large table scans? Have you seen performance improvements after tuning PGA or big table caching?

Share your experience in the comments--  real production stories always help the community learn.