Memory_target

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.