Transcript Optimizing PGA Memory

Chapter 19
Optimizing PGA Memory
1
PGA Memory

PGA memory is private memory for session data
–
Temporary work areas


–
–

2
Intermediate result sets
Sorting
Program Stack
Variables
Configuring PGA just as critical as SGA
IO and PGA Memory






3
Ideally, all session work is done in memory
If not enough memory, operations need to
complete using temporary disk space
Optimal Performance – all operations in memory
Single-pass operation – one operation to disk
Multi-pass operation – several operations to disk
As more operations written to disk performance
declines significantly
PGA Memory Management

Oracle 9i and below, used parameters:
–
–

Oracle 10g parameter
–
–

PGA_AGGREGATE_TARGET
WORKAREA_SIZE_POLICY
Oracle 11g parameter
–
4
SORT_AREA_SIZE
HASH_AREA_SIZE
MEMORY_TARGET (includes PGA and SGA)
PGA Memory Management (cont.)

Understand the 1 GB rule of the PGA
–
–


5

If PGA is < 1 GB, each process limited to 200 MB
If PGA is > 1 GB, each process can allocate 20% of
PGA
Increasing value within first GB may not yield
much performance improvement
Increasing to beyond 1 GB may yield better
improvement
Other PGA allocation rules can be complex
Measuring PGA Performance

See V$PGASTAT – significant statistics include:
–
–
–
–
–

See V$SESSTAT to see session PGA statistics
–
6
Aggregate PGA target parameter
Aggregate PGA auto target
Total PGA inuse
Total PGA allocated
Total freeable PGA memory
–
Session PGA memory
Session PGA memory max
Measuring PGA Performance (cont.)


Some events measure IO to temporary segments
Look for the following events:
–
–


7
Direct path read temp
Direct path write temp
If these events have high values, increase
PGA_AGGREGATE_TARGET
Can also see these events in Enterprise Manager
Measuring Work Area Activity


Includes sort and hash operations
Primary tools include
–
Explain Plan / DBMS_XPLAN

–
V$SQL_WORKAREA

8
Shows “TempSpc” usage estimate
Shows actual usage for SQL statements still in memory
Sizing the PGA


See V$PGA_TARGET_ADVICE
Key columns include
–
–
–


9
PGA_TARGET_FOR_ESTIMATE
PGA_TARGET_FACTOR
ESTD_PGA_CACHE_HIT_PERCENTAGE
Accurately determined “to be required” memory
Within Enterprise Manager, see Memory Advisors
Overriding PGA setting



Set WORKAREA_SIZE_POLICY=MANUAL
Can be set at session level
Effective for one-time operations
ALTER SESSION SET workarea_size_policy = manual;
ALTER SESSION SET sort_area_size=524288000;
ALTER SESSION SET sort_area_size=524288000;

10
The above is correct! Set it twice to be sure!