PGA Memory Analysis (pga.sql)

What This Script Does

Provides comprehensive analysis of PGA (Program Global Area) memory usage including system-wide PGA statistics across RAC instances and detailed session-specific PGA usage for a particular user. This script helps monitor memory consumption and identify sessions with high PGA usage.

The Script

rem  pga.sql
rem
ttitle 'PGA Memory Statistics'
rem
set pagesize 999
rem
column name  format a40
column value format 999,999,999,999,999
rem
select inst_id
      ,name
      ,value
      ,unit
from gv$pgastat
order by name, inst_id
/
rem
rem
ttitle 'Session PGA Usage by User'
rem
set linesize 133
rem
col TYPE format a25
col MB format 999,999
rem
select  st.sid "SID"
       ,sn.name "TYPE"
       ,ceil(st.value / 1024 / 1024) "MB"
  from  v$sesstat st
       ,v$statname sn
 where  st.statistic# = sn.statistic#
   and   sid in  (select sid    from v$session where username like '&user')
   and  upper(sn.name) like '%PGA%'
 order by ceil(st.value / 1024 / 1024) desc, st.sid, st.value desc
/

Usage

-- Analyze PGA statistics and specific user's sessions
@pga.sql
-- Enter: SCOTT (for specific user)
-- Enter: % (for all users)

-- Monitor specific application user
@pga.sql
-- Enter: APP_USER (for application user analysis)

Parameters

The script prompts for:

user: Username pattern for session analysis (% for all users)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on GV$PGASTAT
SELECT on V$SESSTAT
SELECT on V$STATNAME
SELECT on V$SESSION

Sample Output

Part 1: System-Wide PGA Statistics

                                PGA Memory Statistics

INST_ID NAME                                          VALUE UNIT
------- ---------------------------------------- ----------- --------
      1 aggregate PGA auto target              1,073,741,824 bytes
      1 aggregate PGA target parameter         2,147,483,648 bytes
      1 bytes processed                       12,345,678,901 bytes
      1 cache hit percentage                            95.5 percent
      1 extra bytes read/written                   234,567,890 bytes
      1 maximum PGA allocated                    1,234,567,890 bytes
      1 maximum PGA used for auto workareas        567,890,123 bytes
      1 maximum PGA used for manual workareas       45,678,901 bytes
      1 over allocation count                                12
      1 process count                                       156
      1 total PGA allocated                     1,123,456,789 bytes
      1 total PGA inuse                           987,654,321 bytes
      1 total PGA used for auto workareas         678,901,234 bytes
      1 total PGA used for manual workareas        34,567,890 bytes
      2 aggregate PGA auto target              1,073,741,824 bytes
      2 aggregate PGA target parameter         2,147,483,648 bytes
      [Additional instance 2 statistics...]

Part 2: Session PGA Usage by User

                                Session PGA Usage by User

  SID TYPE                           MB
----- ------------------------- -------
 1234 session pga memory             145
 1234 session pga memory max          167
 1234 session uga memory              23
 1234 session uga memory max          28
 2345 session pga memory              89
 2345 session pga memory max          112
 2345 session uga memory              15
 2345 session uga memory max          19

Key PGA Statistics Explained

Memory Allocation

total PGA allocated: Current total PGA memory allocated
total PGA inuse: Currently active PGA memory
maximum PGA allocated: Peak PGA allocation since startup
aggregate PGA target parameter: PGA_AGGREGATE_TARGET setting

Workarea Statistics

total PGA used for auto workareas: Memory for automatic workarea operations
total PGA used for manual workareas: Memory for manual workarea operations
maximum PGA used for auto workareas: Peak automatic workarea usage

Performance Metrics

cache hit percentage: PGA cache effectiveness
bytes processed: Total data processed through PGA
extra bytes read/written: Additional I/O due to PGA constraints
over allocation count: Times PGA limit was exceeded

Session-Level Metrics

session pga memory: Current session PGA usage
session pga memory max: Peak session PGA usage
session uga memory: User Global Area memory
session uga memory max: Peak UGA usage

Common Use Cases

Memory Usage Monitoring

-- Monitor overall PGA usage across RAC
@pga.sql
-- Analyze total PGA allocated vs target

Session Memory Analysis

-- Identify high PGA consuming sessions
@pga.sql
-- Enter: specific username
-- Look for sessions with high MB values

Memory Tuning Analysis

-- Evaluate PGA_AGGREGATE_TARGET effectiveness
@pga.sql
-- Compare target vs actual allocation

Performance Troubleshooting

-- Investigate memory-related performance issues
@pga.sql
-- Check over allocation count and cache hit percentage

Application Memory Profiling

-- Profile specific application memory usage
@pga.sql
-- Enter: application username pattern

Warning Signs to Monitor

High Memory Usage

Total PGA allocated approaching target parameter
Sessions with consistently high PGA usage
Frequent over allocation counts

Poor Performance Indicators

Low cache hit percentage (< 90%)
High extra bytes read/written
Large difference between allocated and inuse memory

Workarea Issues

Excessive manual workarea usage
High maximum workarea values relative to target

Memory Optimization Tips

PGA Target Tuning

-- If over allocation count is high:
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = <higher_value>;

Session Management

-- For sessions with excessive PGA usage:
-- Review SQL statements for memory-intensive operations
-- Consider query optimization or session limits

Workarea Management

-- Enable automatic workarea management:
ALTER SYSTEM SET WORKAREA_SIZE_POLICY = AUTO;

Performance Impact

Minimal Impact: Reads memory statistics with no locking
Real-time Data: Shows current memory state
RAC Comprehensive: Covers all instances for complete picture

gvsess - Active session analysis
gvcpu - CPU usage statistics
gvio - I/O statistics analysis
gvbp - Buffer pool statistics