Donald K. Burleson Oracle Tips

Understanding Oracle RAM memory paging

Most system tuners rely on the “pi” column in vmstats to signal when the server is swapping RAM memory.  However, there is more to the story.

There are times when the “pi” column will be non-zero, even though there is no swapping.  For example, when a process starts the first 2 pages of the program are loaded in memory. If we start a 20M executable we don't need to load all 20 meg into RAM, we just want to load those pieces we will use. Hence, UNIX will load things as necessary later and rely on the principle of spatial locality to minimize the amount of pages in our working set.

To manage the memory segments, the UNIX kernel builds a memory map of the entire program when it starts. Included in this map is a note on whether the storage is “in memory” or “on swap disk”.  

As the program starts it begins accessing some of those pages not in memory and these get paged in - on demand.   Hence, you may see vmstat page-in’s when a large number of programs are starting.

So during normal operation we may see various points in time when paging in happens a lot - no problem - a program is starting new or is now accessing parts of code that it had not used before.

Paging out “po” happens throughout the day for various mysterious OS reasons. Presumably if we have the memory we shouldn't need to page out ever. As processes die we free the pages and they can be used by new processes. However we do see paging out for filesystem buffers quite a bit, so we typically ignore pages out at an average level.

So if paging in “pi” is ok and paging out “po” is ok, then how do we tell when a server is swapping memory?

The answer is to check the scan rate.  When a server begins to run low on memory the page stealing daemon (or the page-scanning daemon) kicks in to high gear. When we get below some predetermined threshold of free memory (a kernel parameter specifies this) the paging daemon begins to panic. It starts paging out all sorts of pages on the NRU (or LRU) list. If we see the paging daemon kicking up to 200 or 300 scans per second we begin to get worried. Associated with this would be increased paging out (obviously) and likely increased paging in - a condition known as thrashing. We would also see an increase in system calls and a higher system cpu% rate.

If we begin to swap we have we run out of all RAM memory which can be allocated through normal housekeeping - we have exhausted the paging daemon and gone below a second threshold which says - stop taking tiny pieces and take this whole process completely out.  This swap-out will free-up RAM space for everybody else.

So when people concentrate on paging to determine memory issues they often cannot tell if the page-in’s are normal housekeeping or a potential swapping problem.

To aid in this, the vmstat utility gives the “sr” column to designate the scan rate. If we see “sr” rising steadily and then shoot into overdrive we have hit our first threshold. We then look the real swapping (page-in operations) to strike.

The sar UNIX utility is a great tool for reporting on this memory activity. It is great that is on some OS's and merely ok on others. On Sun it is great on HPUX it is OK.

root>sar -w 5 5

HP-UX corp-hp B.11.00 U 9000/800    08/09/00

19:37:57 swpin/s bswin/s swpot/s bswot/s pswch/s

19:38:02    0.00     0.0    0.00     0.0     222

19:38:07    0.00     0.0    0.00     0.0     314

19:38:12    0.00     0.0    0.00     0.0     280

19:38:17    0.00     0.0    0.00     0.0     295

19:38:22    0.00     0.0    0.00     0.0     359

Average     0.00     0.0    0.00     0.0     294

Some of the key flags used in the Sun version of SAR are not available on HP/UX. Nevertheless to use SAR you kick off the sadc program to collect information every 5 minutes (I usually do every minute). then use sar -u to see CPU over the entire time period or sar -w for swapping, sar -r for memory, sar -d for disk usage

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: