Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Optimizing Oracle RAM for SGA & PGA

Oracle Tips by Burleson

April 11, 2015 - Updated March 18, 2016

Oracle technology is constantly changing, so don't miss my notes on updates to Oracle PGA behavior.  Also see these important notes on over-riding the Oracle PGA defaults.

This is an excerpt on RAM sizing for Oracle from my bestselling book "Oracle Tuning: The Definitive Reference".  In this book I have complete coverage of RAM management plus automated scripts for the initial sizing of your SGA and PGA regions. 

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/Linux 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.

RAM on UNIX/Linux Servers

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

Dialect of UNIX RAM 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 and Virtual Memory for Oracle

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 it's 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 thousandths 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 alter system command to change the RAM memory areas, and can grow and shrink the RAM memory areas on as needed basis.

If you are licensed for the diagnostic and tuning packs, then you can run this query to see total and used RAM on your Oracle server:

prompt ******************************************************
prompt Get server RAM Size
prompt ******************************************************
col c1 heading 'Physical|Memory|MB' format 999,999,999
   max(value)/1024/1024 c1
   stat_name = 'PHYSICAL_MEMORY_BYTES';
prompt ******************************************************
prompt Get server minimum Free RAM Size
prompt ******************************************************
col c1 heading 'Minimum|Physical|Free|Memory|MB' format 999,999,999
   min(value)/1024/1024 c1
   stat_name ='FREE_MEMORY_BYTES';
prompt ******************************************************
prompt Get server maximum Free RAM Size
prompt ******************************************************
col c1 heading 'Maximum|Physical|Free|Memory|MB' format 999,999,999
   max(value)/1024/1024 c1
   stat_name ='FREE_MEMORY_BYTES';


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 here:

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.

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:

SQL> connect system/manager as sysdba


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

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 Oracle server

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

Viewing Oracle 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 lsdev command to show all devices that are attached to the UNIX server. The lsdev 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 only show the name of the device that has the RAM memory

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

Oracle RAM in Linux

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

root> free
             total       used       free     shared    buffers     cached
Mem:       3728668     504688    3223980      41316     430072      29440
-/+ buffers/cache:      45176    3683492
Swap:       265032        608     264424

Usage note:  For displaying RAM on Linux with the free command, note that the *second* data line ("-/+ buffers/cache") is the important one.  Many people get confused by Linux using all "free" RAM for buffers, so the first line looks like the server is out of RAM.

Oracle 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.


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. (or pga_aggregate_target allocation)
Important Windows Note:  If you are 32-bit Windows, you cannot address more than 2**32 bits (about 1.7 gig), and you need to implement Windows AWE to use all of the RAM on your windows server.  AWE will move the data buffers above the 2-gig line.

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 Oracle 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 optimal 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 One gig.

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:

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


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:

set pages 999;

column pga_size format 999,999,999

    1048576+a.value+b.value   pga_size
   v$parameter a,
   v$parameter b
where = 'sort_area_size'
and = 'hash_area_size'

A dictionary query to compute PGA size.

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


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
    Total PGA regions for 100 users: 250 MB
    RAM reserved for Windows (20 %) 250 MB
    RAM for SGA & buffers 750 MB

Hence, we would want to adjust the RAM to the data buffers in order to make the SGA size less than 750 MB. Any SGA size greater than 750 MB, and the server might 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 750 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 used for shared servers (MTS, not recommended) and parallel queries.  Parallel execution allocates buffers out of the large pool only when parallel_automatic_tuning=true.
  • 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 estimating 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 high-water mark of connected users:'

    &hwm*(2048576+a.value+b.value) pga_size
   v$parameter a,
   v$parameter b
where = 'sort_area_size'
and = '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


Optimizing PGA for Oracle

When sizing your PGA it's easy to minimize the "disk sorts" metric (using a STATSPACK or AWR report), but we need to account for hash joins.  The more PGA, the higher the propensity for Oracle to choose hash joins.  You can use a script like this to track nested loop vs. hash joins, tracking rows processed, disk reads and CPU consumption:

   to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,   
   count(*)                                         c2,   
   sum(st.rows_processed_delta)                     c3,   
   sum(st.disk_reads_delta)                         c4,   
   sum(st.cpu_time_delta)                           c5
   dba_hist_snapshot sn,   
   dba_hist_sql_plan  p,   
   dba_hist_sqlstat  st
 see code depot for full script

The output below shows the number of total nested loop joins during the snapshot period along with a count of the rows processed and the associated disk I/O.  This report is useful where the DBA wants to know if increasing pga_aggregate_target will improve performance.

              Nested Loop Join Thresholds
               Loops        Rows        Disk         CPU
Date           Count   Processed       Reads        Time
-------------------- ----------- ----------- -----------
04-10-10 16       22         750         796   4,017,301
04-10-10 17       25         846           6   3,903,560
04-10-10 19       26         751       1,430   4,165,270
04-10-10 20       24         920           3   3,940,002

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.

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% during Oracle database startup.

PGA Sizing for RAC

In the past, a session's Program Global Area (PGA) was sized by a number of initialization parameters including sort_area_size and hash_area_size. Most of today's database administrators now use the pga_aggregate_target parameter to size the PGA memory allocation. The big downside to this parameter is that the value defined is a target and not a hard limit. It is too common to have all session's collective PGA sizes exceed the target value, thus consuming too much memory on the server.


In Oracle RAC, sessions often perform parallel operations, a subject that will be discussed in more detail in the next chapter, to split the processing for a SQL statement over multiple nodes. The parallel server slave processes read data blocks directly into the session's PGA. Due to the increased parallelization seen in Oracle RAC systems, it is too easy for all sessions to exceed the pga_aggregate_target value.


You may need to increase the PGA memory to support Oracle RAC parallel processing across the nodes. You will still want to use the v$pga_target_advice view to assist with correctly sizing the PGA on an instance-by-instance basis. Similar to sizing the Buffer Cache, you will need to handle the PGA needs of any sessions that failover due to instance failure on another node.



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.

A  script to display the memory used by any specific session would look something like this:


    to_char(ssn.sid, '9999') || ' – ' || nvl(ssn.username,
    nvl(, 'background')) || nvl(lower(ssn.machine), ins.host_name) ssession,
    to_char(prc.spid, '999999999')                       pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
    v$statname  stat1,
    v$statname  stat2,
    v$session   ssn,
    v$sesstat   se1,
    v$sesstat   se2,
    v$bgprocess bgp,
    v$process   prc,
    v$instance  ins
 (See CODE DEPOT for full working script) = 'session pga memory'
and = 'session pga memory max'
    se1.sid = ssn.sid
    se2.sid = ssn.sid
    se2.statistic# = stat2.statistic#
    se1.statistic# = stat1.statistic#
    ssn.paddr = bgp.paddr(+)
    ssn.paddr = prc.addr(+);

Click for more details on displaying RAM memory used by an Oracle session.

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.




Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster