Intermediate SQL Color Coded SQL, UNIX and Database Essays

26Mar/106

How ORACLE Uses Memory on AIX. Part 4: Having Fun with 11g Memory_target

This is going to be a long post but don’t be discouraged: most of it will involve snapshots and screen examples, so it shouldn’t be too bad … 🙂

Anyway, here is the short recap from the previous 3 posts (Part 1, Part 2, Part 3):

  • ORACLE Instance Memory consists of 2 parts: process memory and shared (SGA) memory
  • Process memory is a bunch of memory segments allocated in individual ORACLE processes and their collective size is (attempted to be) managed by pga_aggregate_target parameter. AIX improves process memory usage by identifying sharable segments (such as program or shared library text) and not duplicating them for each individual process.
  • SGA memory is allocated as a single AIX shared memory segment (which, in reality, turns out to be a bunch of smaller VMM segments) and (in ORACLE 10g) is managed by sga_target and sga_max_size parameters. AIX, by default, helps with shared memory usage by allocating it only as needed. However, you can overwrite this behavior and force AIX to allocate all the shared memory at once and, additionally, put a “pin” on it in order to prevent paging.

If you read these descriptions carefully, you would notice that process and SGA memory, while being two parts of the same coin, are very different from each other: they are allocated by AIX differently, they are managed by ORACLE differently and they, in a sense, almost feel different.

In other words, while these two memory regions are related to each other, they are by no means close relatives: one is a big chunk of almost static memory that lives completely independently from any process and the other: an amorphous and ever changing “haze” of small memory pieces that are completely “privatized” by individual processes …

Still, memory is memory is memory and the ultimate question for every DBA is:

The Ultimate Question ...

How to size ORACLE memory properly so that the database runs efficiently on this particular machine ?

In other words, what we really want to know is the Total Memory that is taken by instance. And the fact that we have to (artificially) deal with two separate memory regions here brings a couple of complications …

The first complication is rather cosmetic: Obviously, dealing with one thing is easier than with two. Yet, sizing two things is still pretty straightforward (plus, it gives us more control), so we can almost dismiss it as merely an inconvenience … if not for the second complication …

The second complication is more fundamental: process and SGA memory regions have rather different purposes: SGA memory is mostly used to cache database blocks while process memory (that is manageable by ORACLE) is used for various temporary areas: sorting, hashing etc … It is unlikely that both SGA and PGA will be simultaneously used to the max at any given time. Yet they are usually both sized to the max as it is likely that they may be used to the max individually at one time or another.

I guess you see where I’m going with this – with Berlin Wall between SGA and PGA memory pools, it is quite possible that one pool will be starving while the other – only lightly used. Traditionally, this situation has been addressed by increasing physical memory of the system to fit both pools (which is a definition of waste), but perhaps there is a better way …

And a better way is indeed what ORACLE introduced with 11g – an ability to manage these two pools together and “shift” memory to the part where it is most needed.

But enough with the theory. Let’s see exactly what ORACLE did.

Static SGA/PGA in ORACLE 10g

Let’s start with establishing a baseline – before we see how the memory is “shifted” between SGA and PGA in 11g, let’s see how it is NOT shifted in ORACLE 10g (so that we can better appreciate this new 11g feature).

Here is how we are going to do that:

-- We request 2 Gb for SGA and 2 Gb for PGA
SQL> SHOW parameter target

...
pga_aggregate_target                 big INTEGER 2G
sga_target                           big INTEGER 2G

-- Then we are going to create a rather large 3 Gb table
-- (each record takes one block, so we need to allocate 393216 8K blocks to get to 3 Gb )
-- that we can use to both fill up SGA (by full table scanning it)
-- and PGA (by sorting large character data)

SQL> CREATE TABLE t (n, c) NOLOGGING PARALLEL PCTFREE 90
AS SELECT level, CAST(level AS CHAR(2000))
FROM dual CONNECT BY level <= 393216
/

Here is what SGA memory looks like before we start the test. Notice that only 227 Mb (or ~ 10%) out of requested 2 Gb SGA is allocated.

AIX> omem_shared.sh

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
6886f  s       0.00       0.00       0.00       0.00
988b1  s       8.56       0.00       0.00       8.56
89e3   s       8.56       0.00       0.00       8.56
d0718  s       8.56       0.00       0.00       8.56
7036c  s       8.56       0.00       0.00       8.56
287a7  s       8.56       0.00       0.00       8.56
7876d  s      39.42       0.00       0.00      39.42
206a6  s      57.70       0.00       0.00      57.70
8743   s      87.14       0.00       0.00      87.14
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:        227.08       0.00       0.00     227.08

Requested SGA: 2048.01          Segments: 9

Let’s now use SGA to the fullest. We could have done it by running a full table scan on our table, but, in this case, we have an even easier way to fill up the SGA – collect table statistics:

SQL> EXEC dbms_stats.gather_table_stats(USER, 't', estimate_percent => NULL);

After statistic collection conveniently filled up database buffer cache, here is what our SGA looks like:

AIX> omem_shared.sh

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
6886f  s       0.00       0.00       0.00       0.00
206a6  s      64.03       0.00       0.00      64.03
8743   s      91.21       0.97       0.00      91.21
7876d  s     255.88       0.00       0.00     255.88
d0718  s     255.94       0.00       0.00     255.94
988b1  s     255.94       0.00       0.00     255.94
89e3   s     255.94       0.00       0.00     255.94
287a7  s     255.94       0.00       0.00     255.94
7036c  s     255.94       0.00       0.00     255.94
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       1690.81       0.97       0.00    1690.81

Requested SGA: 2048.01          Segments: 9

Notice that SGA is now is using ~ 80% of the requested capacity (the unallocated portion is reserved for things other than buffer cache).

Ok, now let’s test the sorting. To maximize requirements for PGA, we are going to start 10 parallel sessions, each of which will be doing a full sort of the table:

AIX> cat order.sql

set autotrace traceonly

SELECT * FROM t ORDER BY c DESC;
exit

AIX> cat order.ksh

#! /usr/bin/ksh

integer i=0
while ((i < 10));
do
echo "Starting sqlplus: $i";
sqlplus user/password @order.sql &
(( i = i + 1));
done

AIX> order.ksh

And after a few minutes of work, here is what SGA and PGA memory looks like:

AIX> omem_shared.sh;omem_proc.sh

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
6886f  s       0.00       0.00       0.00       0.00
206a6  s      64.03       0.00       0.00      64.03
8743   s     100.84       0.00       0.00     100.84
7876d  s     255.88       0.00       0.00     255.88
d0718  s     255.94       0.00       0.00     255.94
988b1  s     255.94       0.00       0.00     255.94
89e3   s     255.94       0.00       0.00     255.94
287a7  s     255.94       0.00       0.00     255.94
7036c  s     255.94       0.00       0.00     255.94
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       1700.43       0.00       0.00    1700.43

Requested SGA: 2048.01          Segments: 9

---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
...
192624     210.45       0.00     210.45 oracletest10
430150     210.45       0.00     210.45 oracletest10
606286     210.45       0.00     210.45 oracletest10
712936     210.45       0.00     210.45 oracletest10
794752     210.45       0.00     210.45 oracletest10
802920     210.45       0.00     210.45 oracletest10
626762     210.46       0.00     210.46 oracletest10
782428     226.45       0.00     226.45 oracletest10
348270     226.46       0.00     226.46 oracletest10
663740     226.52       0.00     226.52 oracletest10
---------- ---------- ---------- ---------- ------------------------
Vsid      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
TOTAL:    2269.94       0.00    2229.70 Processes: 23

Notice that while PGA grew up to the 2 Gb limit (and slightly beyond that), the allocation of SGA did not change a bit. If anything it actually grew by a few additional Mbs.

Now, to be completely honest to ourselves, let’s try a couple of experiments.

First, let’s resize sga_target down – perhaps, it will give us more breathing room for sorting …

SQL> ALTER SYSTEM SET sga_target = 1G;

And then, let’s rerun sorting operations again …

AIX> order.ksh

Do we see any difference ?

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
6886f  s       0.00       0.00       0.00       0.00
206a6  s      64.05       0.00       0.00      64.05
8743   s     204.82       0.00       0.00     204.82
7876d  s     255.88       0.00       0.00     255.88
7036c  s     255.94       0.00       0.00     255.94
89e3   s     255.97       0.00       0.00     255.97
d0718  s     255.99       0.00       0.00     255.99
287a7  s     256.00       0.00       0.00     256.00
988b1  s     256.00       0.00       0.00     256.00
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       1804.65       0.00       0.00    1804.65

Requested SGA: 2048.01          Segments: 9

---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
...
430270     226.46       0.00     226.46 oracletest10
630896     226.46       0.00     226.46 oracletest10
704630     226.46       0.00     226.46 oracletest10
712934     226.46       0.00     226.46 oracletest10
782530     226.46       0.00     226.46 oracletest10
606294     226.47       0.00     226.47 oracletest10
331908     226.48       0.00     226.48 oracletest10
614590     226.48       0.00     226.48 oracletest10
495834     226.48       0.00     226.48 oracletest10
348260     226.49       0.00     226.49 oracletest10
---------- ---------- ---------- ---------- ------------------------
Vsid      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
TOTAL:    2380.06       0.00    2339.40 Processes: 23

Well, not really … The PGA is still allocated at roughly the (PGA) limit and SGA is still holding steady at its own limit.

And now let’s resize PGA up and see if it has any effect on SGA:

SQL> ALTER SYSTEM SET pga_aggregate_target = 3G;
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
6886f  s       0.00       0.00       0.00       0.00
206a6  s      64.05       0.00       0.00      64.05
8743   s     204.82       0.00       0.00     204.82
7876d  s     255.88       0.00       0.00     255.88
7036c  s     255.94       0.00       0.00     255.94
89e3   s     255.97       0.00       0.00     255.97
d0718  s     255.99       0.00       0.00     255.99
287a7  s     256.00       0.00       0.00     256.00
988b1  s     256.00       0.00       0.00     256.00
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       1804.65       0.00       0.00    1804.65

Requested SGA: 2048.01          Segments: 9

---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
...
    598022     322.46       0.00     322.46 oracletest10
    655444     322.46       0.00     322.46 oracletest10
    766080     322.47       0.00     322.47 oracletest10
    811070     322.59       0.00     322.59 oracletest10
    700652     322.61       0.00     322.61 oracletest10
    835836     322.62       0.00     322.62 oracletest10
    434266     322.80       0.00     322.80 oracletest10
    450806     322.85       0.00     322.85 oracletest10
    671748     322.91       0.00     322.91 oracletest10
    626856     322.96       0.00     322.96 oracletest10
---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
    TOTAL:    3342.19       0.00    3301.52 Processes: 23

And, once again, the SGA did not budge and stayed fully allocated while PGA used more RAM and SGA, technically, only needed 1 Gb (remember, we told it so in previous exercise).

Ok, after running a number of similar experiments, the following picture emerges:

Parameter Sizing … Result Comment
sga_target UP SGA size increases As long as sga_max_size > sga_target
sga_target DOWN Nothing happens The new SGA size will be picked up at the next database reboot
pga_aggregate_target UP (Combined) PGA size increases  
pga_aggregate_target DOWN (Combined) PGA size decreases Eventually …

Notice a couple of things here:

  • PGA is really pretty dynamic, even in 10g – it can be sized UP and DOWN and this operation is dynamic and almost immediate (depending on workload)
  • SGA is somewhat dynamic as well – it can be sized UP (and is almost immediate if there is a pressing need), but it cannot be sized DOWN – the memory SGA grabs will stay with SGA forever (or, at least, until the next database restart)

But, a more important note is that – there is really no communication and no relation between SGA and PGA in ORACLE 10g, in other words:

The wall between SGA and PGA in ORACLE 10g is rock solid

Let’s see if anything changed with ORACLE 11 …

(Hopefully) Dynamic SGA/PGA in ORACLE 11g

With 11g, the SGA/PGA is presumably managed as one memory area, so to keep things honest, we are going to set 11g memory_target to the same size as combined SGA+PGA in our 10g example and we are going to repeat the tests:

-- We request 4 Gb combined for SGA and PGA
SQL> SHOW parameter target

...
memory_target                        big INTEGER 4G
pga_aggregate_target                 big INTEGER 0
sga_target                           big INTEGER 0

-- And, we are creating the same exact 3 Gb test table as with 10g database
SQL> CREATE TABLE t (n, c) NOLOGGING PARALLEL PCTFREE 90  PCTUSED 10
AS SELECT level, CAST(level AS CHAR(2000))
FROM dual CONNECT BY level <= 393216
/

Let’s look at the state of SGA memory initially:

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
988b1  s       0.00       0.00       0.00       0.00
6886f  s       0.00       0.00       0.00       0.00
206a6  s       0.00       0.00       0.00       0.00
287a7  s       0.00       0.00       0.00       0.00
8743   s       0.00       0.00       0.00       0.00
68a4f  s       0.00       0.00       0.00       0.00
608ce  s       0.06       0.00       0.00       0.06
98911  s       4.18       0.00       0.00       4.18
58ae9  s       9.56       0.00       0.00       9.56
f091c  s       9.56       0.00       0.00       9.56
d8919  s       9.56       0.00       0.00       9.56
58a69  s       9.56       0.00       0.00       9.56
e08fe  s       9.56       0.00       0.00       9.56
d0718  s      22.00       0.00       0.00      22.00
d0a78  s      24.96       0.00       0.00      24.96
d0938  s      62.19       0.00       0.00      62.19
387e5  s     110.36       0.00       0.00     110.36
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:        271.58       0.00       0.00     271.58

Requested SGA: 4096.01         Segments: 17

Notice the first major change – VMM segments are now allocated for the entire 4 Gb memory_target – that is, ORACLE gives itself an option to use all of the instance memory for the SGA (of course, it is extremely unlikely that this will ever happen). Also notice that potential MAX size of 7 VMM segments that are all (or mostly) zeroes (7 * 256 Mb = 1792 Mb) almost exactly matches current dynamic value of PGA Target.

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

COMPONENT       CURRENT_SIZE
--------------- ------------
SGA Target        2566914048
PGA Target        1728053248

Now let’s give it a spin and load our test table into memory …

Here we are actually faced with a slight problem: Neither dbms_stats.gather_table_stats() nor SELECT /*+ full(t) */ * FROM t seem to fill the 11g cache completely – instead, only a small portion of cache is used and memory allocation remains largely the same (this is consistent with ORACLE documentation that states that full table scan blocks are kept at the end of buffer cache and recycled).

By itself, this is a hell of the new feature, but it does screw our test … 😉 So, as a workaround, we are going to take a slightly longer road and fill database cache by lots of smaller “a few blocks only” SQLs:

SQL> CREATE INDEX t_idx ON t(n) NOLOGGING PARALLEL;

DECLARE
TYPE tC_t IS TABLE OF CHAR(2000);
tC tC_t;
i NUMBER;
BEGIN
i := 1;
while i <= 393216 loop
SELECT c bulk collect INTO tC FROM t WHERE n BETWEEN i AND i+1000;
i := i+1000;
END loop;
END;
/

Ok, now we are talking: buffer cache is fully allocated (again, the unallocated portion is reserved for shared pool etc) …

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
988b1  s       0.00       0.00       0.00       0.00
6886f  s       0.00       0.00       0.00       0.00
206a6  s       0.00       0.00       0.00       0.00
287a7  s       0.00       0.00       0.00       0.00
8743   s       0.00       0.00       0.00       0.00
68a4f  s       0.00       0.00       0.00       0.00
608ce  s       0.06       0.00       0.00       0.06
d0718  s      22.01       0.00       0.00      22.01
98911  s     111.92       0.00       0.00     111.92
387e5  s     138.38       0.00       0.00     138.38
d0938  s     239.83       0.00       0.00     239.83
58ae9  s     255.81       0.00       0.00     255.81
f091c  s     255.81       0.02       0.00     255.81
d8919  s     255.81       0.00       0.00     255.81
58a69  s     255.81       0.00       0.00     255.81
e08fe  s     255.81       0.00       0.00     255.81
d0a78  s     255.82       0.00       0.00     255.82
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       2047.09       0.02       0.00    2047.09

and we are ready to test memory shifting. Let’s start sorting sessions …

AIX> order.ksh

AIX> omem_shared.sh;omem_proc.sh

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
988b1  s       0.00       0.00       0.00       0.00
6886f  s       0.00       0.00       0.00       0.00
206a6  s       0.00       0.00       0.00       0.00
287a7  s       0.00       0.00       0.00       0.00
8743   s       0.00       0.00       0.00       0.00
68a4f  s       0.00       0.00       0.00       0.00
608ce  s       0.06       0.00       0.00       0.06
d0718  s      22.01       0.00       0.00      22.01
98911  s     111.92       0.00       0.00     111.92
387e5  s     126.69       0.00       0.00     126.69
d0938  s     239.83       0.00       0.00     239.83
58ae9  s     255.81       0.00       0.00     255.81
f091c  s     255.81       0.00       0.00     255.81
d8919  s     255.81       0.00       0.00     255.81
58a69  s     255.81       0.00       0.00     255.81
e08fe  s     255.81       0.00       0.00     255.81
d0a78  s     255.82       0.00       0.00     255.82
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       2035.40       0.00       0.00    2035.40

Requested SGA: 4096.01         Segments: 17

---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
...
360640      73.43       0.00      73.43 oracletest11
401612      73.45       0.00      73.45 oracletest11
---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
TOTAL:    2194.68       0.00    2124.40 Processes: 41

Well, the result is a bit disappointing – the SGA size was reduced, but NOT with a bang: a grand total of 12 Mb – from 2047 Mb to 2035 Mb – it is hardly worth mentioning …

Still, let’s see if we can influence ORACLE to make a “right” decision here by setting sga_target to a smaller value …

AIX> ORDER.ksh

SQL> ALTER SYSTEM SET sga_target =1G;

The command executed in a split second (which means that nothing was really done). And from the look of it there was indeed no immediate benefit. If anything, SGA gained memory yet again …

...
e08fe  s     255.81       0.00       0.00     255.81
d0a78  s     255.82       0.00       0.00     255.82
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       2168.62       0.00       0.00    2168.62

Requested SGA: 4096.01         Segments: 17

---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------

...
659590     169.03       0.00     169.03 oracletest11
884956     169.17       0.00     169.17 oracletest11
---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
TOTAL:    1996.31       0.00    1926.03 Processes: 30

This is our last resort: let’s size PGA target up to give more memory to PGA explicitly:

SQL> ALTER SYSTEM SET pga_aggregate_target=3G;

And now we finally have results that we expected all along: SGA was sized WAY down to 713 Mb, while PGA was allowed to grow beyond 2 Gb.

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
d8919  s       0.00       0.00       0.00       0.00
e08fe  s       0.00       0.00       0.00       0.00
6886f  s       0.00       0.00       0.00       0.00
f091c  s       0.00       0.00       0.00       0.00
287a7  s       0.00       0.00       0.00       0.00
988b1  s       0.00       0.00       0.00       0.00
206a6  s       0.00       0.00       0.00       0.00
8743   s       0.00       0.00       0.00       0.00
58a69  s       0.00       0.00       0.00       0.00
d0938  s       0.00       0.00       0.00       0.00
68a4f  s       0.00       0.00       0.00       0.00
608ce  s       0.06       0.00       0.00       0.06
d0718  s      22.01       0.00       0.00      22.01
98911  s     111.92       0.00       0.00     111.92
387e5  s     131.93       0.00       0.00     131.93
58ae9  s     191.86       0.00       0.00     191.86
d0a78  s     255.82       0.00       0.00     255.82
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:        713.61       0.00       0.00     713.61

Requested SGA: 4096.01         Segments: 17

---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------

...
643158     143.79       0.00     143.79 oracletest11
655438     741.33       0.00     741.33 ora_m000_test11
---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
TOTAL:    2489.36       0.00    2419.08 Processes: 32

Results of these tests seem to indicate the following:

  • Shifting memory between SGA and PGA in ORACLE 11g does work
  • However, by default, this process is very slow and gradual. Memory is not constantly moved back and forth between SGA and PGA (that would kill performance for sure …)
  • Still, if necessary it is possible to override default ORACLE behavior and “move” memory to the other pool immediately.
  • If immediate changes are needed, sizing target region UP (rather than sizing the other region DOWN) will give you results much faster

The last point begs for an explanation and I believe this is a result of ORACLE being lazy (or, perhaps, smart) … In other words, ORACLE would not size memory region DOWN immediately, because it does not have to (remember, that PGA and SGA targets are minimum requirements). On the other hand, sizing SGA (or PGA) UP makes ORACLE do it at once (and, in my tests, at least for SGA, it happened with NO workload present). In this case, ORACLE really has to do it as a new minimum requirement has to be met.

What happens when you set pre_page_sga=TRUE with memory_target?

Honestly, what happens is rather weird: without manual tweaking, both SGA and PGA memory are allocated to the MAX, at least, according to svmon:

---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
...
10680  s     256.00       0.00       0.00     256.00
8a63   s     256.00       0.00       0.00     256.00
---------- -- ---------- ---------- ---------- ----------
Vsid    Pg   InMem       Pin       Paging     Virtual
---------- -- ---------- ---------- ---------- ----------
TOTAL:       4096.00       0.00       0.00    4096.00

Requested SGA: 4096.01         Segments: 17

---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ------------------------
...
659592     169.60       0.00     169.60 oracletest11
618614     169.73       0.00     169.73 oracletest11
---------- ---------- ---------- ---------- ------------------------
 PID      InMem      Paging     Virtual          COMMAND
---------- ---------- ---------- ---------- ---------------
TOTAL:    1440.36       0.00    1370.05 Processes: 28

However, when manually tweaked (i.e. when you increase pga_aggregate_target), the database behaves exactly like it does with pre_page_sga=FALSE. In this particular example, part of SGA memory is de-allocated (and “shifted” to PGA).

What happens when you set lock_sga=TRUE with memory target?

This one is simple. When you attempt to start the database, it gives you this nice error:

SQL>startup OPEN
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET AND LOCK_SGA cannot be SET together

Which also means that you cannot use memory_target with AIX large pages.

It must come as no surprise though as you cannot have it both ways

  • Either your want SGA to be entirely in physical memory (and untouchable)
  • OR your want SGA to be friendly and, in some cases, give way for PGA … (which involves quite a bit of “touching” 😉 )

This concludes my memory story … I hope that these modest explanations have been useful and gave you some insight of how ORACLE uses AIX memory.

If you want to know more, please, refer to these other excellent documents that describe memory behavior for AIX as well as other UNIX operating systems.

Useful Links

Tanel Poder on Memory_Target in Linux

Tom Kyte on Memory Target

Tanel Poder on ORACLE Memory Usage in Solaris

AIX White Paper on Multiple AIX Page Support

Overview of AIX Process Memory Regions

AIX Performance Presentation by Steve Nasypany that includes an excellent overview of memory usage

Comments (6) Trackbacks (0)
  1. Hi,
    Many thanks for your jobs. Please whre can I download your shell scripts ?
    Than

  2. Thanks Than
    All the scripts should be available in the TOOLS section. Let me know if any particular script is missing.

  3. Thanks for your elaborations.
    Do u have a plan to test this on other platform like hp, solaris, linux, etc?

  4. pls post scripts u are using in this example

  5. Anton,

    You can find the scripts here: http://intermediatesql.com/tools/

    Regards,
    Maxym Kharchenko

  6. Thanks for your explanation on this topic.

    I noted a discrepancy between AIX reported memory and Oracle’s sum of v$process.pga_alloc_mem.

    $ ./omem.sh $ORACLE_SID
    =========================================================================
    Memory used by ORACLE instance: xxxxxx
    =========================================================================

    SGA:
    In Memory (RSS): 15390 Mb
    Requested (Virtual): 15390 Mb
    Segments: 113

    Processes:
    In Memory (RSS): 9757 Mb
    Requested (Virtual): 9666 Mb
    Processes: 506

    Total:
    In Memory (RSS): 25147 Mb
    Requested (Virtual): 25056 Mb

    SQL> select count(*), round(sum(p.pga_alloc_mem) / 1024 / 1024,0) as mb from v$process p;

    COUNT MB
    ———- ———-
    494 2048

    SQL>

    Have you encountered this and know the reason?

    Regards,
    Albert Nelson A.


Leave a comment

Trackbacks are disabled.