Can ORACLE use both memory_target and AIX large pages at the same time ?

来源：互联网发布：java的equals方法编辑：程序博客网时间：2024/05/17 02:32

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, ORACLEsuggested 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:

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
The memory_target was set to 1000 Mb. This memory is supposed to be shared between SGA and PGA, and yet SGAhas 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: SGAmemory 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.

0 0