When defining memory usage to an Oracle instance you can specify an “SGA target” and a separate “PGA target”; alternatively you could specify a single “Memory target”. I’ve not seen many people using the second option and there are reasons why it’s not a good idea – like the side effect it has on the use of large/huge pages – but a thread appeared on the Oracle Developers’ forum recently asking why the Buffer Cache Advisory section of an AWR report would suggest increasing the buffer cache when the Memory Statistics section showed the PGA+SGA usage to be just 6GB out a declared memory_target of 8GB – why was Oracle “wasting” (or losing) 2GB of memory that the report said could be put to good use.
My first thought was that there was a reporting error – maybe a coding error, maybe a problem of inconsistent definition, so my first piece of advice was to poke around for a little more data; and while that was going on I thought I’d run up an instance specifying a memory_target to see what funny numbers might appear.
My first attempt at instance startup failed because I was using a small machine and had allocated more than half the memory to huge pages – and Oracle didn’t want to use huge pages when the memory target was set, so it ran into various resource problems and let me know that I would need to change a few O/S settings 0 so I reduced the memory_target to something small enough to allow the instance to start.
This is where my memory/sga/pga settings ended when the instance finally started (cut-n-paste with some of the output deleted):
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 1808M
memory_target big integer 1808M
pga_aggregate_target big integer 0
sga_target big integer 0
The 1,808MB was what I’d set in the startup file, and here’s a little corroboration of the figures:
SQL> show sga
Total System Global Area 1895823408 bytes
Fixed Size 9136176 bytes
Variable Size 1107296256 bytes
Database Buffers 771751936 bytes
Redo Buffers 7639040 bytes
The top line is the sum of the next 4 figures, and the numbers add up to 1,808 MB (with an error of 2,000 bytes) but isn’t it interesting that the total s reported as the Total System Global Area – apparently leaving nothing for the PGA.
How does this compare with information from the next available AWR report.
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
------------ ------------
Host Mem (MB): 3,692.4 3,692.4
SGA use (MB): 1,072.0 1,072.0
PGA use (MB): 334.9 381.0
% Host Mem used for SGA+PGA: 38.10 39.35
Suddenly the SGA is only 1,072MB, while the PGA is now reporting 334MB – which means that somewhere something is failing to report the roughly 400MB. Maybe the “Dynamic Memory Components” section of the AWR will help (I’ve deleted all the zero lines here):
Begin Snap Current Min Max Oper Last Op
Component Size (Mb) Size (Mb) Size (Mb) Size (Mb) Count Typ/Mod
--------------- ----------- ----------- ----------- ----------- ------ -------
DEFAULT buffer 704.00 704.00 704.00 752.00 0 SHR/IMM
PGA Target 736.00 736.00 736.00 736.00 0 STA/
SGA Target 1,072.00 1,072.00 1,072.00 1,072.00 0 STA/
Shared IO Pool 48.00 48.00 .00 48.00 0 GRO/IMM
java pool 16.00 16.00 16.00 16.00 0 STA/
large pool 16.00 16.00 16.00 16.00 0 STA/
shared pool 272.00 272.00 272.00 272.00 0 STA/
------------------------------------------------------
That seems to be a “Current Size” total of 2,864MB – but luckily we can spot that the presence of the SGA Target in the list means we’re double counting, and the total excluding that line is 1,792MB. That’s a shortfall of 16MB from our declared 1,808MB, but that’s okay because with this small memory target Oracle is operating in 16MB granules and one granule has been reserved for the fixed memory and public redo log buffers.
So we haven’t lost a big chunk of the memory_target – what we (or the OP) are seeing is a variation in the purpose of the two different parts of the AWR. The Memory Statistics tell us about PGA being used (at the moment of the snapshot) by current sessions, not about the memory that is reserved as an “internal” PGA target. Maybe some investigation of the Memory Resize Operations would show us that Oracle has reached this distribution of memory between SGA and PGA over a period of time as a fairly stable position with some movement of granules back and forth between the two. We could also check the summary of Process Memory that appears in the AWR.
Geek stuff
I had overlooked the possibility of simply looking at the Dynamic Memory Components section of the AWR to check whether the “missing” memory was allocated to the PGA,, so in the original thread on the developer forum I supplied a query that I wrote many years ago to run as sys against the x$ksmge (granule map) when suitable v$ didn’t exist, so just for completeness here’s that query with the results:
set linesize 156
set trimspool on
set pagesize 60
set tab off
column indx format 999
column component format a20
column cursize format 9,999
column gransize format 999,999,999,999
column grantype format 999
column granstate format a10
column ct format 9,999
column total_memory format 999,999,999,999
break on report
compute sum of total_memory on report
select
sct.indx, sct.component, sct.cursize,
ge.gransize, ge.grantype, ge.granstate, ct,
ge.gransize * sct.cursize total_memory
from
x$kmgsct sct,
(
select
ge.grantype, ge.granstate, ge.gransize,
count(*) ct
from
x$ksmge ge
group by
ge.grantype, ge.granstate, ge.gransize
) ge
where
ge.grantype(+) = sct.grantype
and sct.cursize != 0
order by
sct.indx, sct.component, ge.granstate
;
INDX COMPONENT CURSIZE GRANSIZE GRANTYPE GRANSTATE CT TOTAL_MEMORY
---- -------------------- ------- ---------------- -------- ---------- ------ ----------------
0 shared pool 18 16,777,216 1 ALLOC 18 301,989,888
1 large pool 1 16,777,216 2 ALLOC 1 16,777,216
2 java pool 1 16,777,216 3 ALLOC 1 16,777,216
5 SGA Target 67
7 DEFAULT buffer cache 43 16,777,216 9 ALLOC 43 721,420,288
15 Shared IO Pool 3 16,777,216 17 ALLOC 3 50,331,648
20 PGA Target 46
----------------
sum 1,107,296,256
As you can see, this reports the SGA and PGA targets in terms of granules available, but neither reports any granules actually allocated. However, adding the 46 PGA Target granules of 16MB to the total memory of (approx) 1.1GB we can see that Oracle is clearly aware of the full 1,808 MB (less the one fixed granule), and the oddity of “lost memory” was simply about the choice of what to report.
Memory Target and O/S
Just for completeness – here’s a pair of extracts from the alert log of the instance startup. The first with memory_target set, the second using the sga_target and pga_aggregate_target and leaving the memory_target unset. I’ve removed a load of lines which were simply the timestamps (separated by microseconds):
Starting ORACLE instance (normal) (OS id: 2025)
****************************************************
/dev/shm will be used for creating SGA
Large pages will not be used. Only standard 4K pages will be used
****************************************************
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Per process system memlock (soft) limit = 128G
Expected per process system memlock (soft) limit to lock
instance MAX SHARED GLOBAL AREA (SGA) into memory: 1808M
Available system pagesizes:
4K, 2048K
Supported system pagesize(s):
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
4K Configured 462853 462853 NONE
Reason for not supporting certain system pagesizes:
2048K - Dynamic allocate and free memory regions
Note the warning: Large pages will not be used. Only standard 4K pages will be used. That’s half the machine memory unavailable, and lots of memory used by per-process memory maps
Starting ORACLE instance (normal) (OS id: 2968)
****************************************************
Sys-V shared memory will be used for creating SGA
****************************************************
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Per process system memlock (soft) limit = 128G
Expected per process system memlock (soft) limit to lock
instance MAX SHARED GLOBAL AREA (SGA) into memory: 1122M
Available system pagesizes:
4K, 2048K
Supported system pagesize(s):
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
4K Configured 4 4 NONE
2048K 1200 561 561 NONE
Summary
While I’ve highlighted a couple of details about the differences between memory target and sga/pga target, the main point of this note comes back to something I’ve said many times in the past. When you’re looking at reports (AWR / Statspack / home-grown) you do need to understand the meaning of the figures in the report, and when they don’t seem to make sense you need to cross-check with other sources so that you can be confident that you understand what the figures are showing.
In this case the “Host Memory” usage figures for the PGA probably reflect the current (and instantaneous) usage by Oracle processes, not the granules allocated to the instance by the operating system. There are other parts of the AWR report that tell you how the granules are split between the SGA and PGA, and while I haven’t shown it, there’s a section of the report (Memory Resize Operations) that tells you if the instance has been under pressure to move memory granules between the SGA and PGA.
Oracle Scratchpad 
Comments and related questions are welcome.