Oracle Tips by Burleson

Determining the Optimal Oracle PGA Size

Our sample MS-Windows server has 1,250 megabytes of RAM. Subtracting 20 percent for overhead, we have 1000 megabytes available for Oracle.

Each PGA RAM region size is determined as follows:

• OS Overhead – We reserve 2 MB for Windows and 1 MB 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 times sort_area_size and is used for performing hash joins of Oracle tables.

The values for sort_area_size and hash_area_size are quickly shown with the Oracle show parameters command:

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

A quick dictionary query (pga_size_each.sql) against the v$parameter view will yield the correct value for each PGA RAM region size.

See code depot for full scripts

column pga_size format 999,999,999

select
  2048576+a.value+b.value pga_size
from
  v$parameter a,
  v$parameter b
where
. . .
;

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

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

If we now multiply the number of connected users by the PGA demands for each user, we will know exactly how much RAM should be reserved for connected sessions. Alternatively, we could issue an SQL statement to obtain the same result. The script for such a statement is shown below.
 

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.