Forum Stats

  • 3,770,919 Users
  • 2,253,183 Discussions
  • 7,875,654 Comments

Discussions

Why would Oracle *not* allocate all memory assigned via MEMORY_TARGET

I have an instance that has been assigned 8 G of memory. From the init.ora:

memory_max_target 8589934592

memory_target 8589934592

No other memory parameters have been set. The database has been running since July 14th, yet AWR only shows it having allocated about 6GB of RAM to the SGA/PGA:


The Buffer Pool Advisor shows that database performance would be much better if another couple of Gigs were tacked onto it. I'm trying to think of some reason the server would decide not to (or be unable to) use the full amount of memory assigned via MEMORY_TARGET and I'm coming up blank. This is Oracle 12.2.0.1.0 Enterprise running on Solaris.

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,795 Gold Crown
    Accepted Answer

    Just a quick check -

    although the table of result you've reported doesn't show a figure for Total Memory in the PGA Target line, it does show 134 granules ini the CURSIZE column. At 16MB per granule this would equate to 2,144MB; bring the final total to 6128MB, which is 16MB short of 6GB).

    Is the memory_target on that system 6GB (we expect a one granule "deficit" in this output).

    Regards

    Jonathan Lewis

    Smuckers
«1

Answers

  • saratpvv
    saratpvv Member Posts: 907 Gold Badge

    Are you facing any bottleneck in the database?

    In general  advisors are calculating their recommendations from statistics cumulated since the beginning of the instance and they are just an estimation. so it just mislead you.

    Seems you enabled AMM so  oracle instance dynamically tunes all memory components to optimize performance as the workload changes.

  • Smuckers
    Smuckers Member Posts: 43 Red Ribbon

    Re: a bottleneck -- the AWR report 'Top 10 Foreground Waits' are predominantly User I/O waits. The Buffer Pool Advisor indicates that physical reads would be significantly reduced (on the order of 60%) with more memory assigned to the buffer cache.

    However -- that's more of a side issue than the question I'm asking in this thread. When MEMORY_TARGET is set to X, Oracle should grab X memory. Pretty much every AWR I've ever looked at -- this is what happens. For this instance, Oracle is grabbing 25% less memory than what the target value is (~6GB instead of the 8GB assigned to MEMORY_TARGET). I can't find any reason why Oracle would do that and I'm asking if anyone here has an idea of why it might be happening.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,604 Red Diamond

    From https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MEMORY_TARGET.html

    MEMORY_TARGET specifies the Oracle systemwide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.

    It refers to both SGA and PGA.

  • Smuckers
    Smuckers Member Posts: 43 Red Ribbon

    Yep. From the AWR screenshot in my initial post:

    SGA Use (MB) Begin: 5,328.0 End: 5,328

    PGA Use (MB) Begin: 541.5  End: 634

     

    PGA + SGA at both the beginning and end add up to a hair under 6GB.

    MEMORY_TARGET is set to 8589934592 or 8G.


    This is the crux of my question. For what reasons might PGA + SGA not equal the MEMORY_TARGET?

  • JohnWatson2
    JohnWatson2 Member Posts: 4,331 Silver Crown

    Perhaps the instance sometimes needs 2GB of PGA.

    Furthermore, it may be that using more SGA memory would not have any beneficial effect: Reducing physical reads does not necessarily save any DB time.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,795 Gold Crown

    My first thought would be that there's a reporting error which is something to do with the differences between Solaris and other variants of Unix.

    When you set the memory target (as opposed to the SGA target) Oracle creates "granules" as the unit of memory that can be moved between memory areas - including the PGA.

    If I had to guess about the discrepancy I'd guess that when Oracle is looking at the PGA allocated and PGA used it's not allowing for the fact that a process may have (logically) allocate a couple of megabtes but had to physically allocate a whole granule - so the PGA percentage may be massively under reported.

    If you can connect as sys, try running the following query to see how many granules you have, what size they are, and which function has how many granules. The total may be 8GB (less one granule):

    set linesize 156
    set trimspool on
    set pagesize  60
    
    spool memory_granules
    
    column indx             format 999
    column component        format a28
    column grantype         format 999
    column cursize          format 9,999
    column gransize         format 999,999,999,999
    column ct               format 9,999
    column name             format a40
    
    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
    order by
            sct.indx, sct.component, ge.granstate
    ;
    
    
    

    To copy fixed format text into the edit box, click on the paragraph symbol (backwards p) to the left of the edit box, select the quotes option to get a drop down, and select the "code block" option from that. Then copy the text into the edit box.



    Regards

    Jonathan Lewis

    Smuckers
  • saratpvv
    saratpvv Member Posts: 907 Gold Badge


    Also query the below view - what is current,min,max size allocated dynamically.


    V$MEMORY_DYNAMIC_COMPONENTS

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,604 Red Diamond
    edited Jul 29, 2021 4:00PM

    This is the crux of my question. For what reasons might PGA + SGA not equal the MEMORY_TARGET?

    SGA is fixed shared memory - used by more than one process.

    PGA refers to a process's private memory (e.g. stack space, data segment, etc). PGA has a typical base size (e.g. 2MB for a Win32 process thread). But can and do vary dynamically per process, and can do so significantly. The max amount of memory used by a process is not known upfront for a process that may or may not perform different services (such as executing SQL and PL/SQL code).

  • User_GSQTY
    User_GSQTY Member Posts: 55 Bronze Badge
    edited Jul 29, 2021 7:05PM


    You mentioned : "From the init.ora" , is it possible that you also have pfile that contains different values .

    You should query v$parameter to see actual value of these 2 parameters and verify if pfile is being used instead.

  • Smuckers
    Smuckers Member Posts: 43 Red Ribbon

    I don't have SYS access to the system in question, but I ran the query supplied by Jonathan Lewis against a database that I do have SYS access to and received the following:

    INDX COMPONENT                    CURSIZE         GRANSIZE GRANTYPE GRANSTATE            CT     TOTAL_MEMORY
    ---- ---------------------------- ------- ---------------- -------- ---------------- ------ ----------------
       0 shared pool                      149       16,777,216        1 ALLOC               149    2,499,805,184
       1 large pool                         1       16,777,216        2 ALLOC                 1       16,777,216
       2 java pool                          8       16,777,216        3 ALLOC                 8      134,217,728
       3 streams pool                       1       16,777,216        4 ALLOC                 1       16,777,216
       4 unified pga pool                   0
       5 SGA Target                       250
       6 memoptimize buffer cache           0
       7 DEFAULT buffer cache              82       16,777,216        9 ALLOC                82    1,375,731,712
       8 KEEP buffer cache                  0
       9 RECYCLE buffer cache               0
      10 DEFAULT 2K buffer cache            0
      11 DEFAULT 4K buffer cache            0
      12 DEFAULT 8K buffer cache            0
      13 DEFAULT 16K buffer cache           0
      14 DEFAULT 32K buffer cache           0
      15 Shared IO Pool                     8       16,777,216       17 ALLOC                 8      134,217,728
      16 Data Transfer Cache                0
      17 In-Memory Area                     0
      18 In Memory RW Extension Area        0
      19 In Memory RO Extension Area        0
      20 PGA Target                       134
      21 ASM Buffer Cache                   0
                                                                                                ----------------
    sum                                                                                            4,177,526,784
    

    Basically the SUM figure returned matches the SGA value from AWR for that database but no value for the PGA is shown. So -- the idea that the AWR report might have the PGA memory wrong is still on the table, but this query doesn't appear to help in determining whether that is the case.