How Oracle Database Uses RAM ?

The goal of server optimization for any Oracle databases is to manage the RAM and CPU resources of the machine, and make sure that expensive RAM is not under-allocated. When we talk about optimizing Oracle database performance in an MS-Windows environment, the techniques that we use are very similar to those used on larger UNIX platforms.

In my experience as an Oracle consultant, I see millions of dollars' worth of RAM being wasted by Oracle shops. Because the Oracle DBA does not know how to accurately compute the RAM demands of the database, they under-allocate the RAM. On larger servers, RAM is still very expensive and depreciates regardless of use. The savvy Oracle professional knows how to accurately predict the high-water mark of RAM demands for their database, and fully allocates the RAM, reserving only enough to accommodate spikes in user connections.

Let's take a look at each of these activities, using Oracle on an MS-Windows environment as an example.

RAM and Virtual Memory

On all platforms, we need to ensure that the RAM processing demands of the Oracle database do not exceed the real RAM memory of the server. As we may know, all large servers use a Virtual Memory (VM) scheme to allow sharing of RAM resources. Oracle servers (Windows, UNIX, OS390) have a special swap disks to manage excessive RAM demands. Virtual memory is an internal "trick" that relies on the fact that not every executing task is always referencing its RAM memory region. Since all RAM regions are not constantly in use, vendors have developed a paging algorithm that move RAM memory pages to the swap disk when it appears that they will not be needed in the immediate future.

In order to provide for the sharing of RAM, a special area of disk called a swap disk is required, and the primary purpose of the swap disk is to hold page frames from in active programs on disk. The purpose of the swap disk is to offload the least-frequently-used (LRU) RAM page frames so that many applications can concurrently share the same memory. Once RAM pages from inactive programs are written to disk (a page-out), the operating system can make the freed RAM memory available for another active task. Later, when the inactive program resumes execution, the RAM pages are re-loaded from the swap disk into RAM (a page-in). This reloading of RAM pages is called swapping, and swapping is very time-consuming and degrades the performance of the target program.

While having the swap disk ensures concurrent RAM usage above the real amount of RAM, optimal performance requires that the swap disk is never used for active programs. This is because reading RAM pages off of the swap disk is about 14,000 times slower than reading the memory pages from directly from RAM. As we know, disk access is measured in milliseconds, or millionths of the second, while RAM access is access to in nanoseconds, or billionths of a second.

In a VM architecture, the OS will write Ram to the swap disk, even thought the real RAM has not been exceeded. This is done in anticipation of a RAM shortage, and if a real RAM shortage occurs, the LRU RAM frames are already on the swap disk.

For an Oracle server, the goal is to keep all of the RAM memory demands of the database and database connections beneath the amount of physical RAM memory. In an Oracle environment, we can accurately control the amount of RAM memory that is used by the database instance System Global Area (SGA). This is because the Oracle database administrator can issue an alter system command to change the RAM memory areas, and can grow and shrink the RAM memory areas on as-needed basis.

We can see the allocated size of the SGA in the Oracle alert log, and it is also displayed on the console when Oracle is started as shown in listing 1.

SQL> startup ORACLE instance started. Total System Global Area 143421172 bytes Fixed Size 282356 bytes Variable Size 117440512 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes Database mounted. Database opened.

Listing 1: Starting an Oracle database.

We can also see the SGA RAM region by issuing the show sga command. In the example below we see that our total SGA size is 143 megabytes (refer to listing 2).

SQL> connect system/manager as sysdba Connected. SQL> show sga Total System Global Area 143421172 bytes Fixed Size 282356 bytes Variable Size 117440512 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes

Listing 2: Using the show sga command.

Next, let's see how we can quickly find the amount of RAM on our server.

Determining the RAM on Your Server

On most Oracle servers you can issue a few commands to see the amount of RAM. Let's look at a few examples.

RAM on UNIX Servers

On each UNIX dialect, there are specific commands that are required to display the RAM usage.

Dialect of UNIX	RAM memory display command
DEC-UNIX	uerf -r 300 | grep -i mem
Solaris	prtconf|grep -i mem
AIX	lsdev -C|grep mem
Linux	free
HP/UX	swapinfo -tm

RAM on IBM-AIX UNIX

In the IBM AIX dialect of UNIX, we have a two-step command to display the amount of available RAM memory. We start with the lsdevlsdev command produces a large listing of all devices, but we can pipe the output from lsdev to the grep command to refine the display to show only the name of the device that has the RAM memory. command to show all devices that are attached to the UNIX server. The

root> lsdev -C|grep mem

mem0       Available 00-00           Memory

Here we see that mem0 is the name of the memory device on this AIX server. Now we can issue the lsattr -El command (passing mem0 as an argument) to see the amount of memory on the server. Below we see that this server has 2 gigabytes of RAM memory attached to the mem0 device.

root> lsattr -El mem0
size     2048 Total amount of physical memory in Mbytes  False
goodsize 2048 Amount of usable physical memory in Mbytes False

RAM in Linux

In Linux, see RAM is easy. The free command can be used to quickly display the amount of RAM memory on the server.

root> lsattr -El mem0

size     2048 Total amount of physical memory in Mbytes  False
goodsize 2048 Amount of usable physical memory in Mbytes False

RAM on MS-Windows

To see how much RAM you have on your MS-Windows server, you can go to start settings control panel system, and click on the "general" tab (refer to figure 1). Here we see that this server has 1,250 megabytes of RAM.

Figure 1: The MS-windows system display screen.

Now that we know how to tell the size of our MS-Windows RAM and the size of the SGA, we have to consider the RAM usage for Oracle connections.

Reserving RAM for Database Connections

The Oracle DBA can use math to determine the optimal RAM allocation for a MS-Windows server. For the purposes of this example, let's assume that we are on a dedicated MS-Windows Oracle server, and Oracle will be the only program running on the server.

The total RAM demands for Oracle on MS-Windows are as follows:

• OS RAM — 20 percent of total RAM for MS-Windows, 10% of RAM for UNIX
• Oracle SGA RAM — determined with the show sga command
• Oracle database connections RAM — Each Oracle connection (when not using the Oracle multi-threaded server) will use two megabytes of RAM plus sort_area_size plus hash_area_size.

Once we know the total available RAM memory, we have to subtract 20 percent from this value for MS-Windows overhead. Even in an idle state, Windows services use RAM resources, and we must subtract 20% to get the real free RAM on an idle server.

Finding the High-water Mark of User Connections

Once we know the amount of available RAM for Oracle, we must know the high-water mark (HWM) for the number of Oracle connections. For systems that are not using Oracle's multithreaded server architecture, each connected session to the Windows server is going require an area of memory for the program global area, or PGA.

There is no easy way to determine the high-water mark of connected Oracle sessions. If you use Oracle STATSPACK you can get this information from the stats$sysstat table, but most Oracle DBAs make a generous guess for this value.

Determining the PGA Size

In our example, we have 1,250 megabytes of RAM memory on our MS-Windows server, and less 20 percent, we wind up with the total available allocation for Oracle of 750 MB.

The size for each PGA RAM region is computed as follows:

• OS Overhead - We reserve 2 meg for Windows and 1 meg for UNIX
• Sort_area_size parameter value - This RAM is used for data row sorting inside the PGA
• Hash_area_size parameter value - This RAM defaults to 1.5 time sort_area_size, and is used for performing hash joins of Oracle tables.

We can use the Oracle show parameters command to quickly see the values for sort_area_size and hash_area_size (refer to listing 3).

SQL> show parameters area_size NAME TYPE VALUE ------------------------------------ ----------- --------- bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 hash_area_size integer 1048576 sort_area_size integer 524288 workarea_size_policy string MANUAL

Listing 3: Display PGA area sizes.

Here we can see the values for sort_area_size and hash_area_size for our Oracle database. To compute the value for the size of each PGA RAM region, we can write a quick data dictionary query against the v$parameter view (refer to listing 4).

set pages 999; column pga_size format 999,999,999 select 1048576+a.value+b.value pga_size from v$parameter a, v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size' ;

Listing 4: A dictionary query to compute PGA size.

The output from this data dictionary query shows that every connected Oracle session will use 3.6 megabytes of RAM memory for the Oracle PGA.

PGA_SIZE
------------
 3,621,440

Now, if we were to multiply the number of connected users by the total PGA demands for each connected user, we will know exactly how much RAM memory in order to reserve for connected sessions.

Getting back to our example, let's assume that we have a high-water mark of 100 connects sessions to our Oracle database server. We multiply 100 by the total area for each PGA memory region, and we can now determine the maximum size of our SGA:

Total RAM on Windows Server 1250 MB
Less:
Total PGA regions for 10 users: 362 MB
RAM reserved for Windows (20 percent) 500 MB
----------
862 MB

Hence, we would want to adjust the RAM to the data buffers in order to make the SGA size less than 388 MB. Any SGA size greater than 388 MB, and the server will start RAM paging, adversely affecting the performance of the entire server. The final task is to size the Oracle SGA such that the total memory involved does not exceed 388 MB.

Remember, RAM is an expensive server resource, and it is the job of the DBA to fully allocate RAM resources on the database server. Un-allocated RAM wastes expensive hardware resources, and RAM depreciates regardless of usage.

As a review, the size of an Oracle SGA is based upon the following parameter settings:

• shared_pool_size — This sizes the administrative RAM for Oracle and the library cache.
• db_cache_size — This parameter determines the size of the RAM for the data buffers
• large_pool_size — The size of the Java pool
• log_buffer — The size of the RAM buffer for redo logs

In general, the most variable of these parameters is db_cache_size. Because Oracle has an almost insatiable appetite for RAM data buffers, most DBAs add additional RAM to the db_cache_size.

A Script for Computing Total PGA RAM

In SQL*Plus, you can accept a parameter and then reference it inside your query by placing an ampersand in front of the variable name. In the simple example below, we declare a variable called myparm and direct SQL*Plus to accept this value when the script is executed:

set heading off
set echo on

accept myparm number prompt 'Choose a number between 1 and 10: '

select 'You chose the number '||&myparm from dual;

Our goal is to create a script called pga_size.sql. This script will prompt you for the high-water mark of connected users, and then compute the sum of all PGA RAM to be reserved for dedicated Oracle connections. In this example, we have a 2-meg overhead for MS-Windows PGA sessions.

Here is the finished script:

set pages 999;

column pga_size format 999,999,999

accept hwm number prompt 'Enter the high-water mark of connected users: '

select
  &hwm*(2048576+a.value+b.value) pga_size
from
 v$parameter a,
 v$parameter b
where
 a.name = 'sort_area_size'
and
 b.name = 'hash_area_size'
;

When we run the script, we see that we are prompted for the HWM, and Oracle takes care of the math needed to compute the total RAM to reserve for Oracle connections.

SQL> @pga_size


Enter the high-water mark of connected users: 100

old   2:     &hwm*(2048576+a.value+b.value) pga_size
new   2:            100*(2048576+a.value+b.value) pga_size


  PGA_SIZE
------------
362,144,000

Now that we understand sizing RAM regions for Oracle on Windows, let's look at how we can examine the RAM used by Windows during Oracle activities.

Monitoring Server Resources in MS-Windows

In MS-Windows we can use the performance manager screen to observe the resource consumption of the Oracle Windows server (refer to figure 2). The performance manager is hidden deep inside the Windows menus, but can be found by following start > settings > control panel > administrative tools > performance.

Figure 2: The MS-Windows server performance monitor.

The MS-Windows performance monitor plots three metrics:

• Green (CPU) - This is the percentage of CPU resources consumed
• Yellow (RAM) - This is the number of RAM pages per seconds used
• Blue (DISK) - This is the disk I/O queue length percentage

Let's take a closer look at the MS-Windows performance monitor. figure 2 is a time-based snapshot of an Oracle databases resource consumption at startup time. These lines form signatures (known usage patterns) that reveals some interesting patterns inside Oracle:

1. RAM Usage — The yellow line is RAM usage, and we see the first spike in the RAM when the SGA is allocated and a short spike in RAM as the database is mounted.
2. DISK Usage — The blue line is the disk I/O, and we see the disk I/O activity peg at the point where we mount the database. This is because Oracle must touch every data file header to read the system change number (SCN).
3. CPU Usage — The green line is CPU and it is interesting to note that the CPU never goes above 50 percent during Oracle database startup.

Conclusion

In sum, the allocation of RAM memory for an Oracle server can be done solely with mathematics, and no expensive performance monitors are required. The most difficult part of Oracle RAM optimization in any environment is accurately predicting the high-water mark of connected user sessions. If we have an unexpected spike of connected sessions, it is possible that we would exceed the amount of RAM on the server, causing active programs RAM regions to go out to the swap disk. The goal is to fully allocate RAM without ever experiencing RAM paging.

HAPPY LEARNING!