Donald K. Burleson Oracle Tips

Identifying RAM memory bottlenecks in Oracle

Contention for Random Access Memory (RAM) has always been a problem for Oracle. All database servers have a limited amount of available RAM, and this RAM must be shared between the Oracle database and all external sessions that connect to the server and consume RAM in their Program Global Area (PGA).

However, before we go into detecting if your server memory is exceeded, we must first give you a tool for determining how much memory you have available on your server. Below is a command that you can issue to see how much RAM exists on your server.

Display RAM Size on DE C-Unix

In DEC-Unix, you can use the uerf command in conjunction with grep to display memory size. For example:

uerf -r 300 | grep -i mem

Here, the output of the uerf command is piped to grep to filter out and display the segments relating to memory. The –i option causes grep to find both uppercase and lowercase strings. With respect to the example shown here, grep –i mem looks for both MEM and mem.

Display RAM Size on HP-UX

In HPUX the dmesg command can display memory information.

dmesg

Memory Information:

    physical page size = 4096 bytes, logical page size = 4096 bytes

    Physical: 5242880 Kbytes, lockable: 4051216 Kbytes, available: 4651796 Kbytes

Display RAM Size on AIX

In IBM’s AIX dialect of Unix, you must issue two separate commands.  You start with the lsdev command followed by the lsattr command to display the amount of memory on a server. First execute lsdev to list all devices. Then pipe that output through grep to filter out everything not related to memory. That will get you the name of the memory devices that are installed. For example:

>lsdev -C|grep mem

mem0       Available 00-00           Memory

Here you can see that mem0 is the name of the memory device. Now that you have the name, you can issue the lsattr –El command to see the amount of memory on the server. In the following example, the server has 3 gigabytes of RAM installed:

>lsattr -El mem0

size     3064 Total amount of physical memory in Mbytes goodsize 3064 Amount of usable physical memory in Mbytes

You must issue the lsattr –El command separately for each memory device.

Display RAM size on Solaris

The prtconf command can also be used on all Solaris servers to quickly see the amount of available memory.

>prtconf|grep -i mem

Memory size: 2048 Megabytes

    memory (driver not attached)

    virtual-memory (driver not attached)

Display RAM size in Linux

In Linux the free command can be used to quickly display the amount of RAM memory on the server.

>free

             total       used       free     shared    buffers     cached

Mem:       3728668     504688    3223980      41316     430072      29440

-/+ buffers/cache:      45176    3683492

Swap:       265032        608     264424

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:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm