When describing Oracle features to folks that are new to the platform or coming from other database platforms, I found it’s best to keep it simple, but take on the important aspects of the technology.  I’m going to take on a few more posts on Oracle internals from the perspective of the individual new to Oracle.

To start, I’ve been having some long discussions, as well as documenting how Oracle memory works and how we monitor it when you don’t have the diagnostic and tuning pack at your disposal.  With the exclusion of these management packs, you have to be intentional in your queries to provide the data, without violating the licensing by using the %_HIST_% views.

Oracle and Memory

Oracle manages its memory in two main areas: the System Global Area (SGA) and the Program Global Area (PGA).

The SGA is a shared memory area that contains various pools and caches used by the database. It includes the buffer cache, Java pool, Streams pool (used for replication tools like Change Data Capture), and other specialized caches and pools that support different database functions.

The PGA, on the other hand, is allocated separately for each User Global Area (UGA). These allocations of memory in the PGA stores session-specific variables, including operations such as sorting, hashing, and using PL/SQL tables during the execution of queries and transactions.

Monitoring Memory

Monitoring memory in Oracle—specifically the SGA (System Global Area) and PGA (Program Global Area) is typically done from a top-down perspective, focusing on two key settings: the target and the limit.

• The target defines the desired amount of memory Oracle should aim to use.

• The limit sets the maximum memory that can be allocated.

These values can be quickly shown with the “SHOW PARAMETER” command:

SHOW PARAMETER <parameter name or search by text>;

The parameter name values:

• PGA_AGGREGATE_TARGET
• PGA_AGGREGATE_LIMIT
• SGA_TARGET
• SGA_MAX_SIZE

For the SGA, memory pools can dynamically resize as needed, up to the defined limit, (i.e. max size). This flexibility allows Oracle to optimize memory usage for caches, pools, and other components. The PGA, on the other hand, allocates memory per user session for operations like sorting and hashing. It generally tries to stay within the target but may exceed it temporarily during periods of high demand.

If either memory area hits its limit and can’t allocate what it needs—for example, the SGA can’t expand a pool or the PGA can’t support a sort operation—Oracle logs this internally. This information is captured in the advisory system and, without Automatic Workload Repository (AWR), is only available in system memory views like V$ and GV$.

I want to monitor memory usage, specifically when the advisor notes:

• Memory usage exceeding the limit

• Recommendations to increase the target value for either SGA or PGA

This way, we preserve valuable insights before they age out of the system buffer.

While many system views are similar, they require different queries to extract relevant details for each memory area since SGA and PGA serve different purposes. Based on my experience reporting on Oracle memory health, here are the three most important things a DBA or developer needs to track:

1. How often memory usage exceeded the SGA or PGA limits, especially when PGA demand went beyond its configured maximum.

2. Instances of SGA thrashing (frequent resizing or reassignment between caches) and PGA spills to temp tablespace when the allocated memory wasn’t sufficient.

3. Drops in cache hit ratios that indicate memory settings may be too low, prompting deeper investigation using top SQL analysis or parameter review.

The How for SGA

We can easily demonstrate the amount of total memory, as well as what is allocated to what cache and pool with the following:

V$SGASTAT

V$SGAINFO

V$SGA_TARGET_ADVICE

select name||' = '||(bytes/1024/1024) as “Pool in MB” from V$SGAINFO;

Pool in MB

---------------------------------------------------------------------------
Fixed SGA Size = 5.2982940673828125
Redo Buffers = 4.3203125
Buffer Cache Size = 752
In-Memory Area Size = 0
Vector Memory Area Size = 0
Shared Pool Size = 736
Large Pool Size = 16
Java Pool Size = 0
Streams Pool Size = 0
Shared IO Pool Size = 80
Data Transfer Cache Size = 0
Granule Size = 16
Maximum SGA Size = 1529.6186065673828125
Startup overhead in Shared Pool = 209.46738433837890625
Free SGA Memory Available = 16

The above simply lists the name of the memory area and the MB allocated.  This could be displayed for total memory, ensuring we only display the pools and caches that are part of the SGA if we wanted to show each later on.

select name, bytes/1024/1024 MB from V$SGAINFO
where name like 'Fixed%'
or name like '%Pool%';

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   5.29829407
Shared Pool Size                        736
Large Pool Size                          16
Java Pool Size                            0
Streams Pool Size                         0
Shared IO Pool Size                      80

If we use V$SGASTAT, we can then query each of the containers and go into the PDB allocation for multitenant:

select con_id, sum(bytes/1024/1024) "Memory Allocated" from V$SGASTAT
where pool='shared pool'
group by CON_ID
order by con_id;

 

    CON_ID Memory Allocated
---------- ----------------
         0       170.695183
         1       419.215485
         2       16.4068298
         3       129.682503

We could join this to V$CONTAINERS to display the name vs. the CON_ID and then filter by the type of pool or cache, etc.  This could be further enhanced by doing more joins to provide more value to the reader.

SGA Advice

The DBA/developer will want to decide if it is valuable to resize the SGA.  The advisor table can provide that value for the database:

select * from V$SGA_TARGET_ADVICE
where sga_size_factor>=1
order by sga_size_factor;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE     CON_ID
---------------------- --------------------- -------------------------------- --------------------- ----------
      1536               1          258                   1               34906             832                   576          0
      1920            1.25          258                   1               34906            1152                   576          0
      2304             1.5          258                   1               34906            1536                   576          0
      2688            1.75          258                   1               34906            1920                   576          0
      3072               2          258                   1               34906            2240                   576          0

The above has the goal of displaying information on the SGA, including high level buffer and shared pool for anything over the current sizing, letting the DBA know what benefits could be seen if they increased memory allocation to the SGA in reads and time.

The How for PGA

If a DBA were to track data over time, from the buffer into a table of their choosing, the following views for PGA would be valuable:

V$PGASTAT

V$PGA_TARGET_ADVICE

We can display the PGA in use with the following query:

SELECT round(sum(VALUE)/1024/1024,0) as TOTAL_PGA_USAGE_MB 
FROM  V$PGASTAT where NAME in ('total PGA inuse');

TOTAL_PGA_USAGE_MB
------------------
               183

PGA Advice

These values could be compared over time to V$PGA_TARGET_ADVICE when the cache hit is below 100% and when a threshold for over-allocation count is reached, (that means the process had to go to the TEMP tablespace to complete processing of sorts, hash, etc.)

Basic PGA advice queries might look something like the following:

SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice
WHERE estd_pga_cache_hit_percentage<100
AND pga_target_factor<=1;

OR just pull everything and then review what is important:

SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
        64             99                    9
       128             99                    9
       256            100                    0
       384            100                    0
       512            100                    0
       614            100                    0
       717            100                    0
       819            100                    0
       922            100                    0
      1024            100                    0
      1536            100                    0
      2048            100                    0
      3072            100                    0
      4096            100                    0

The top query simply captures all information from the V$PGA_TARGET_ADVICE, where the estimated cache hit percentage was under 100%, (any time that the PGA had to use TEMP tablespace to perform sorts and hashed, etc. vs. able to run all processing inside PGA) and this happened when the size of the PGA was either at the target size or greater, meaning that the current size isn’t adequate.

When would the DBA want to investigate?  As always, the answer is, “it depends”.  If the cache hit is lower than 100% for OLTP workloads, I would most likely investigate.  If the workload is OLAP, (analytics) then more usage of TEMP is often expected, no matter if it’s a single pass or multi-pass to TEMP and would drop the cache hit down to lower percentages.  If the usage is justified or expected, then it can be ignored.

What are some of the causes for intermittent hits to the most optimal use of PGA?

• Poor statistics on one or more tables.
• Out of the ordinary queries that required significant sorting or hashing that forced temp usage.
• Normal processing that was expected to use more PGA than the DBA or developer was willing to offer resources to.
• A bug (materialized view merge override parameter settings) that cause additional pressure on the PGA to use TEMP tablespace.

Due to this, a lower than 100% value on the PGA cache hit may be justified.