Intermediate SQL Color Coded SQL, UNIX and Database Essays

19Aug/105

Can ORACLE 11g memory_target work with AIX large pages ?

One of my readers recently asked me if ORACLE 11g can use memory_target along with AIX large pages … (Thanks Randolf!)

At the first blush this seems to be impossible. The main reason, of course, is that memory_target and large pages are used for the purposes that are, in fact, completely opposite!

  • The goal of 11g memory_target is to flow memory efficiently between ORACLE SGA and PGA to the place where it is most needed. This means that SGA may reduce its size from time to time, releasing memory to the operating system
  • AIX large pages, on the other hand, are designed to never leave physical memory

I hope, you can see a contradiction here …


As if this was not enough, when you actually try to load SGA into large pages (by setting lock_sga=TRUE) and also enable memory_target, ORACLE would give you a rather direct negative reply:

ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET AND LOCK_SGA cannot be SET together

So, given these reasons (and this evidence), it is only natural to conclude that memory_target and large pages are simply incompatible with each other and cannot be used together.

But, not so fast … As with any complex software, ORACLE has plenty of back doors, “support only” options and hidden parameters that can alter database behavior, sometimes dramatically.

One of these back doors can be used here and it’s described in detail in Metalink note: 728444.1. In essence, a bug that was introduced in ORACLE 10.2.0.4 prevented AIX large pages from being used by SGA. As a workaround, ORACLE suggested to use external environment variable for setting SGA page size before starting the database as in:

AIX> export ORACLE_SGA_PGSZ=16m

While this accomplished the goal of allocating large pages for the SGA, it also had one unintended consequence: large pages could now be used along with memory_target.

But wait … Is this necessarily a problem ? Why do I believe that this was not what ORACLE had in mind ?

To see why, let’s go back to the original requirement for memory_target: use memory efficiently by moving it between SGA and PGA as needed. Notice that this requires memory to be allocated and deallocated dynamically and, as a result, size of SGA can grow and shrink with time.

Let’s test whether this is really the case.

First, we will run this test on a “normal” system where SGA is allocated out of regular pages. Initially our database is set with the following parameters:

  • memory_target=1000M
  • sga_target=700M
  • pga_aggregate_target=200M

This is what SGA memory looks right after startup:

---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
   1011804  m       9.50       0.00       0.00       9.50
   1008e45  m      12.00       0.00       0.00      12.00
   120dc65  m      13.19       0.00       0.00      13.19
   12012a4  m     130.88       0.00       0.00     130.88
---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
    TOTAL:        165.56       0.00       0.00     165.56

Requested SGA: 1000.01          Segments: 4

Notice several things here:

  1. AIX allocated only ~ 165Mb out of 1000Mb that was requested. This is expected as, by default, AIX only “really” allocates memory that has been used and, well, most of the RAM has NOT been used yet
  2. The memory_target was set to 1000 Mb. This memory is supposed to be shared between SGA and PGA, and yet SGA has allocated enough shared segments to potentially grab 100% of memory_target (4 segments, 256 Mb MAX each). Of course, the likelihood of that happening is pretty small

Ok, let’s add some load to our database to use more SGA pages …

-- Disable 11g direct reads for full table scans, and read all blocks from a large table
SQL> ALTER SESSION SET events '10949 trace name context forever, level 1';
SQL> SELECT COUNT(1) FROM t;

… and see where it can lead us:

---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
   1011804  m       9.50       0.00       0.00       9.50
   12012a4  m     159.06       0.00       0.00     159.06
   120dc65  m     200.00       0.00       0.00     200.00
   1008e45  m     256.00       0.00       0.00     256.00
---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
    TOTAL:        624.56       0.00       0.00     624.56

As you can see, SGA allocated more pages and is now using ~ 625 Mb, most of it in buffer cache. Let’s now use the memory_target main feature and redirect this memory into PGA

SQL> ALTER SYSTEM SET sga_target = 200M;
SQL> ALTER SYSTEM SET pga_aggregate_target = 700M;

.. and this is what SGA looks like after these operations are completed:

---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
   1008e45  m       0.00       0.00       0.00       0.00
   1011804  m       9.50       0.00       0.00       9.50
   120dc65  m     108.00       0.00       0.00     108.00
   12012a4  m     108.44       0.00       0.00     108.44
---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
    TOTAL:        225.94       0.00       0.00     225.94

Notice that most of the memory in SGA was deallocated to presumably benefit PGA areas …

Just to confirm that we’ve done everything correctly, let’s check SGA and PGA sizes as reported by ORACLE:

SQL> SELECT component, current_size FROM v$memory_dynamic_components
  2  WHERE component LIKE '%Target';

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
SGA Target                                                          314572800
PGA Target                                                          734003200

And, as you can see, ORACLE number for SGA roughly matches what AIX actually allocates.

This is how memory_target is supposed to work. Let’s see whether anything changes if we add AIX large pages on top of it …

# I'm enabling the workaround and starting the database
AIX> export ORACLE_SGA_PGSZ=16m
AIX> dbstart.pl

Let’s look at the state of ORACLE SGA just after the startup:

---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
   1178b93  L     240.00     240.00       0.00     240.00
   11cb41d  L     256.00     256.00       0.00     256.00
   103d181  L     256.00     256.00       0.00     256.00
   13c9d3f  L     256.00     256.00       0.00     256.00
---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
    TOTAL:       1008.00    1008.00       0.00    1008.00

As was expected with large pages – ALL SGA segments are allocated to the MAX and SGA is using 1 Gb of memory, or, theoretically 100% of memory_target. Does it mean that PGA is really 0% and processes do not use memory ? Of course, not … Below is the process snapshot from our instance and, as you can see, some memory is definitely being used (and will be used even more if we add some load to them):

---------- ---------- ---------- ---------- ------------------------
   Vsid      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
   5832868       3.01       0.00       3.01 ora_diag_test11
  60293328       3.01       0.00       3.01 ora_psp0_test11
  18809280       3.01       0.00       3.01 ora_reco_test11
  65274060       3.02       0.00       3.02 ora_mman_test11
  59637856       3.02       0.00       3.02 ora_vktm_test11
  28573750       3.05       0.00       3.05 ora_qmnc_test11
  66715900       3.13       0.00       3.13 ora_mmnl_test11
  20709740       3.18       0.00       3.18 ora_q000_test11
  14942550       3.38       0.00       3.38 ora_dbrm_test11
  25755826       3.51       0.00       3.51 ora_dia0_test11
  52953116       3.57       0.00       3.57 ora_ckpt_test11
  37224472       3.90       0.00       3.90 ora_smon_test11
  13959586       6.03       0.00       6.03 ora_fbda_test11
  46268428       7.57       0.00       7.57 ora_dbw0_test11
  22741498       8.05       0.00       8.05 ora_pmon_test11
  12714338       8.83       0.00       8.83 ora_mmon_test11
  51183838      18.26       0.00      18.26 ora_lgwr_test11
        -1     200.16       0.00       0.00 TEXT SEGMENT
---------- ---------- ---------- ---------- ------------------------
   Vsid      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
    TOTAL:     287.71       0.00      87.55 Processes: 17

Let’s do the same exercises with this memory as in the “normal” example.

We will first run some load to “increase” SGA … This is the state of SGA after the load is completed:

---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
   1178b93  L     240.00     240.00       0.00     240.00
   11cb41d  L     256.00     256.00       0.00     256.00
   103d181  L     256.00     256.00       0.00     256.00
   13c9d3f  L     256.00     256.00       0.00     256.00
---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
    TOTAL:       1008.00    1008.00       0.00    1008.00

And, as you can see, there is really no change …

And after that, we will switch SGA and PGA sizes and check SGA again:

SQL> ALTER SYSTEM SET sga_target = 200M;
SQL> ALTER SYSTEM SET pga_aggregate_target = 700M;

---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
   1178b93  L     240.00     240.00       0.00     240.00
   11cb41d  L     256.00     256.00       0.00     256.00
   103d181  L     256.00     256.00       0.00     256.00
   13c9d3f  L     256.00     256.00       0.00     256.00
---------- -- ---------- ---------- ---------- ----------
   Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
    TOTAL:       1008.00    1008.00       0.00    1008.00

And once again, there is no change …

Just to confirm that we’ve done everything correctly, let’s check SGA and PGA sizes as reported by ORACLE:

SQL> SELECT component, current_size
  FROM v$memory_dynamic_components
  WHERE component LIKE '%Target';

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
SGA Target                                                          314572800
PGA Target                                                          734003200

ORACLE thinks that its SGA is only using 300Mb of RAM … and this is clearly a lie!

The bottom line

Let’s go back to our original question: Can ORACLE use both memory_target and AIX large pages at the same time ?

As you have seen, the answer is: Yes, but this is also the case where the letter of the answer defeats the spirit of the answer …

When large pages are used, all the benefits that memory_target is supposed to provide are gone: SGA memory is no longer dynamic and will never be released to be a part of PGA.

So, can you use them both: Yes. Is it of any use: No.

And that’s the bottom line.

Comments (5) Trackbacks (0)
  1. And if you have ever seen how much kernel CPU heavy dynamic memory management really consumes, you’ll realize why hugepages were designed to be fixed in memory and never paged.

    That way one can be sure that CPU is not going to be taken away to do memory maintenance at a time when it is most in demand.

    Always remember: memory management is not a “free” operation: it requires heavy CPU and memory bandwidth usage. You initiate that in an already overloaded system and I can guarantee you an immediate thrashing.

    Have a look here for an introduction to what it all means:
    http://en.wikipedia.org/wiki/Thrashing_%28computer_science%29

  2. Noons, thank you for your comment – I agree – memory management is definitely not free.

    However, since large pages are, well … large, and hence there are fewer of them (for the same amount of memory), wouldn’t managing them be easier for the system (than, say, managing small pages) ? Do you see where I am going here ? đŸ™‚

    Anyway, my post was about MEMORY_TARGET feature in ORACLE that is all about “dynamic memory” (which should “flow” between SGA and PGA). When large pages are used, memory becomes “static” and cannot flow … in other words, using MEMORY_TARGET with large pages while technically possible, is useless.

    Cheers,
    Maxym Kharchenko

  3. Maxym:

    Very interesting indeed. What version of 11g and AIX were you testing with? We’re upgrading to 11.2.0.2 and I’m trying to decide whether to use memory_target or not. Currently we’re using 10.2.0.4 on AIX 6.1 with large pages and SGA locked. What commands are you using to show memory use? I would like to see which instance process are using small, medium, and large pages.

    Thanks!
    John

  4. Hello John.

    We were running all kinds of configurations: AIX 5.3, 6.1 and ORACLE 10.2, 11.2.0.1, 11.2.0.2 (I have to say – “were” … as I recently changed jobs and my environment is mostly Linux now).

    The decision of whether to use memory target depends mostly on whether you want to allow dynamic memory resize ops for your db instance. Most of the shops disallow that as it can lead to system freezes when memory is “resized out of” shared pool and database suddenly needs to (re)parse lots of of cursors. Memory_target might be ok though for development or non-critical systems as it simplifies memory management.

    Also, keep in mind that memory target and large pages do not mix very well, essentially to maximize their respective benefits you have to pick either one or the other …

    As for the tools, svmon is probably the best tool to look into details of AIX memory usage – feel free to search this website for examples of svmon use.

    Cheers,
    Maxym Kharchenko


Leave a comment

No trackbacks yet.