Intermediate SQL Color Coded SQL, UNIX and Database Essays

13Feb/113 tool for Linux

A very brief update.

One of the smaller (and nicer) part of ORA_MEM package is utility that shows graphically what active db processes are doing at the moment … be it running SQL, accessing db object or waiting for something.

As an added benefit, it also shows some important OS statistics, such as process state or ‘spot’ CPU utilization for your database sessions.


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 …


How AIX Paging Space works. Part 1: Why your program memory footprint gets bloated sometimes

If you recall AIX memory saga from my previous “AIX memory” posts (Process memory, SGA Memory), one of the points was that during the lifetime of the process (or shared memory segment) at any given moment each allocated memory page could reside in one of the two places:

  • Either the page is located in a physical RAM
  • OR the page is located in paging space

In memory location is, obviously, preferred. If I was a memory page I would WANT to be there 🙂 In paging space ? – not so much, but, of course I could be forced there if I’m not popular enough and AIX needs memory for other things.


How to reduce the size of AIX Memory. Quickly

This seems to be a rather odd exercise – why would you ever agree to have less memory on your system to run programs ?

But, of course it makes sense in a few special cases – testing how much memory your programs really need as well as validating how the system will behave if less memory is available (not every system is privileged to start with 32Gb+ of RAM).


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.

How ORACLE Uses Memory on AIX. Part 3: Locking SGA

In the previous post we discussed how ORACLE allocates shared memory for SGA in AIX and one of the conclusions was that AIX does not give all the requested memory to ORACLE instance right away but merely promises it.

While this technique allows to use memory more efficiently and you (at least temporarily), can request more memory for processes and shared segments than what AIX physically has, it also has a rather unpleasant consequence – when we get to the limit of physical memory, AIX will have no choice but to start paging memory.

Paging is not necessarily a bad thing – moving older and not-so-often used data out of memory is something that will be done rather routinely – this is how AIX keeps a healthy system. However, when SGA memory starts to page out (and, more importantly, page back in) things can go bad quickly as, well, ORACLE does not really expect SGA to be a disk based area … (ORACLE would have called it “SDA” if that was the case 😉 )

You probably know that in the vast majority of configurations, it is strongly advised to size SGA so that it fits entirely into physical memory and never pages out. The question becomes: how can we accomplish that on AIX?


How ORACLE Uses Memory on AIX. Part 2: SGA

In the previous post we discussed memory usage by ORACLE processes and, if you remember, it took us some effort to get to their actual memory usage.

In this post we are going to talk about the other large memory area that ORACLE instance uses – System Global Area or SGA.

In many respects, finding out SGA memory usage is going to be simpler as we have to deal with only one large entity (AIX shared memory segment) instead of many small memory chunks in separate ORACLE processes. But as we will see, this process is still rather “involved” for AIX has a few tricks up its sleeve for shared memory as well.

How much memory is used by ORACLE SGA. Really …

Let’s start with a simple question.

What happens if we try to allocate 12 Gb SGA on a machine with only 8 Gb of physical memory?


How ORACLE Uses Memory on AIX. Part 1: Processes

In this post I am going to talk about how ORACLE allocates and uses memory when running on AIX, but I will also talk about the power of approximation and how it can sometimes be misused for ill purposes 😉

On the outset, ORACLE/AIX memory “deal” seems simple enough – obviously, ORACLE will use memory when it runs and many AIX commands (such as vmstat or ps) will show memory usage both system wide and specific to particular process. But, as always, the devil is in the details and the effect of those “details” may be far from subtle.

So, why don’t we go ahead and find that devil, shall we ?

The straightforward (but naive) approach

Let’s say that we have an ORACLE instance running on one of our AIX servers. How do we find out how much memory it is using ?