Monitoring PGA Usage by Username in Oracle Database

calendar Aug 21, 2025 / · 10 min read · oracle database sql memory pga dba performance tuning v$session v$sesstat v$statname  ·
Share on: linkedin copy

Monitoring PGA Usage by Username in Oracle Database

Purpose

V$SESSTAT is the view that holds one statistic value per session, and two of its statistics describe how much Program Global Area (PGA) memory a session uses right now and the most it has used. The script below joins V$SESSTAT to V$STATNAME, filters the sessions of one database user, and keeps only the PGA statistics. The result is a short list: one pair of rows for each session that user has open.

The PGA is the memory that sits behind work such as sorts and hash joins. When a server is short of memory, the first practical question is often "which connections are holding it, and who owns them?" This query answers that question for one user at a time. It does not rank users against each other and it does not apply a threshold. It shows you the numbers, and you decide whether they look normal for that workload.

This guide explains what the PGA is, what each line of the query does, how to read the two rows per session, and what to check next when a value looks too large.

Code

 1select
 2    st.sid "SID",
 3    sn.name "TYPE",
 4    ceil(st.value / 1024 / 1024) "MB"
 5from v$sesstat st,
 6     v$statname sn
 7where st.statistic# = sn.statistic#
 8  and sid in (
 9        select sid
10        from v$session
11        where username like '&user'
12      )
13  and upper(sn.name) like '%PGA%'
14order by st.sid, st.value desc;
15/

Code Breakdown

Columns selected

  • st.sid is the session identifier. Oracle's reference describes the SID column of V$SESSTAT as the "Session identifier".
  • sn.name is the name of the statistic, shown under the heading TYPE. V$STATNAME holds the name for each statistic number, and Oracle states that the names in the NAME column "remain stable across Oracle Database releases, and they can be relied on by customer scripts". That is why the script filters on the name and not on a statistic number.
  • ceil(st.value / 1024 / 1024) turns bytes into megabytes and rounds up to the next whole number. The statistic descriptions give both PGA values in bytes, so the division by 1024 twice is correct. Because of ceil, a small non-zero value shows as 1 MB, so the column does not show fine detail for tiny sessions.

Views used

  • v$sesstat displays user session statistics. Its columns are SID, STATISTIC# and VALUE.
  • v$statname displays decoded statistic names for the statistics shown in V$SESSTAT and V$SYSSTAT. The join st.statistic# = sn.statistic# attaches a name to each number.
  • v$session displays session information for each current session. The script uses it only to turn a user name into a list of session IDs.

Filters

  • sid in (select sid from v$session where username like '&user') limits the output to the sessions of the user you name. The &user placeholder is filled in with the value you supply before the query runs. Because the test is like, you can also give a pattern with the % wildcard, so the filter is not strictly limited to a single user.
  • upper(sn.name) like '%PGA%' keeps only statistics whose name contains "PGA". In Oracle's 19c statistic descriptions, two names match: session pga memory and session pga memory max.

Ordering

order by st.sid, st.value desc groups the rows by session and puts the larger value first within each session.

What the Output Looks Like

Every session returns two rows. Oracle describes the two statistics this way:

  • session pga memory: "Current PGA size for the session (in bytes)."
  • session pga memory max: "Peak PGA size for the session (in bytes)."

The peak value is never smaller than the current value, so with the descending sort the session pga memory max row normally comes first for each SID, and the session pga memory row follows. If the two rows are close, the session is near its highest use now. If the current row is much smaller than the peak row, the session grew once and then gave memory back, or at least stopped using it.

Oracle also notes that these two statistics are useful only in V$SESSTAT. They have no meaning in V$SYSSTAT, so do not look for an instance-wide total there.

What the PGA Is

Oracle's Concepts guide defines the PGA as "memory specific to an operating process or thread that is not shared by other processes or threads on the system." The same page adds that "Because the PGA is process-specific, it is never allocated in the SGA." One PGA exists for each server process and background process, and the total of all of them is the instance PGA.

Inside the PGA are work areas. Oracle calls a work area "a private allocation of PGA memory used for memory-intensive operations." A sort operator uses a sort area to sort a set of rows, and a hash join uses a hash area to build a hash table. These are the operations that most often make one session larger than another.

PGA, UGA and sessions

This script reports per-session numbers, so the link between a session and the PGA matters. Oracle defines the User Global Area (UGA) as session memory: "memory allocated for session variables, such as logon information, and other information required by a database session." Where the UGA lives depends on the connection type. With a dedicated server connection, the UGA is stored in the PGA. With a shared server connection, Oracle stores the UGA in the SGA, because the PGA belongs to one process and the session must stay available to any shared server process.

So the PGA is tied to a server process, not to a session as such. On a dedicated server connection the two line up one to one, and the statistic is a direct reading of that session's process. On a shared server system, the session statistic should be read with care, and the process view (see below) is the better place to look for process memory.

Key Points

  • The numbers are bytes, converted to MB. Both PGA statistics are reported in bytes. The ceil call rounds every value up.
  • Two rows per session. One row is current use and one is the peak since the session started. Read them as a pair.
  • The filter is a pattern. like '&user' accepts wildcards. If you type a user name with % in it, you may match more than one user.
  • Only sessions that exist now appear. V$SESSION shows each current session, including idle ones. A session that has already ended is gone, and so is its peak value.
  • No ranking and no limit. The script lists one user's sessions. It does not sort users by memory, and it does not flag anything as too high.
  • The view is dynamic. V$ views are the dynamic performance views. They show current instance state, so a result is a snapshot of one moment.

Insights and Best Practices

Run it for the user you already suspect

The query needs a user name, so it fits best after another signal has pointed at an account: an application service user, a batch user, a reporting user. Run it for that user and compare the sessions with each other. One session far above its siblings is a better lead than a high number alone.

Compare current with peak

A large peak with a small current value says the session had a heavy moment and released the memory. A large current value that sits close to the peak says the memory is still in use. The first case points to a past event, such as one big sort. The second points to something happening now, so look at what the session is running.

Look at the instance-wide picture too

Oracle's PGA_AGGREGATE_TARGET parameter "specifies the target aggregate PGA memory available to all server processes attached to the instance." Its documented default is 10 MB or 20% of the size of the SGA, whichever is greater, and you can change it with ALTER SYSTEM. The Concepts guide says individual PGAs can grow as needed up to a target size you set with an initialization parameter, and that the database tunes work area sizes automatically when automatic PGA memory management is enabled. Comparing the sum of your per-session values with this target tells you whether the per-session picture is part of a larger pressure.

Check the work area policy

The WORKAREA_SIZE_POLICY parameter controls how work areas are sized. Its default is AUTO. With AUTO, work areas used by memory-intensive operators are sized automatically from the PGA memory in use, the target in PGA_AGGREGATE_TARGET, and what each operator needs. With MANUAL, sizing follows the *_AREA_SIZE parameters, and Oracle warns that this "may result in sub-optimal performance and poor PGA memory utilization." If a system shows odd PGA behavior, confirm the setting first.

Use the process view for process-level numbers

V$PROCESS displays information about the currently active processes. It has columns PGA_USED_MEM ("PGA memory currently used by the process (in bytes)"), PGA_ALLOC_MEM (memory currently allocated by the process, including free PGA memory not yet released to the operating system) and PGA_MAX_MEM ("Maximum PGA memory ever allocated by the process (in bytes)"). Because the PGA belongs to a process, this view is the natural cross-check for the numbers the script returns.

Repeat the query to see a trend

One run shows one moment. If a session looks large, run the query again after a few minutes and compare. Rising current values for the same SID mean growth. Flat values mean the session has settled.

When to Run This

  • When an application user complains about slowness and you want to see how much PGA that user's sessions hold.
  • When operating-system memory looks high on the database server and you want to link the load to a database account.
  • When you are checking a batch job that runs large sorts or hash joins and want its peak PGA.
  • After changing PGA_AGGREGATE_TARGET or WORKAREA_SIZE_POLICY, to see how the sessions of a key user behave.
  • When you need a quick answer for one user and do not need a full report.

Troubleshooting Common Issues

The query returns no rows. The user name in &user may be wrong, or that user has no session open right now. Check the spelling against the USERNAME column of V$SESSION, or use % with part of the name.

You see rows for more users than expected. The like test accepts wildcards. A pattern such as APP% matches every user whose name starts with those letters. Use the exact name to match one account.

Values look like 0 or 1. The ceil call rounds up, and small values are shown as 1 MB. For finer detail, remove the division and read the raw byte value from the VALUE column.

Only one of the two rows appears for a session. Check that the join and the %PGA% filter are unchanged. Both statistics have "pga" in the name and both should match.

The numbers do not add up to what the operating system reports. The PGA statistics cover PGA memory only. The V$PROCESS columns PGA_ALLOC_MEM and PGA_USED_MEM differ because allocated memory can include free PGA memory not yet returned to the operating system. Use the process view for the comparison.

The session list changes between runs. V$SESSION shows each current session, so new connections appear and ended ones vanish. Use the SID and compare only sessions present in both runs.

References

Posts in this series