PGA Update: Oracle technology is constantly changing, so don't
miss my new notes on updates to Oracle PGA
behavior. Also see these important notes on over-riding the
Oracle PGA Concepts
Not all RAM in Oracle is shared memory. When you start a
user process, that process has a private RAM area, used for sorting SQL results
and managing special joins called “hash” joins. This private RAM is known as the
Program Global Area (PGA). Each individual PGA memory area is allocated each
time a new user connects to the database.
Google: ”oracle pga sizing”
Oracle Database 10g will manage the PGA for you if you
set the pga_aggregate_target parameter (we will discuss parameters and how they
are set later in this book), but you can manually allocate the size of the PGA
via parameters such as sort_area_size and hash_area_size. We recommend that you
allow Oracle to configure these areas, and just configure the
The PGA can be critical to performance, particularly if
your application is doing a large number of sorts. Sort operations occur if you
use ORDER by and GROUP BY commands in your SQL statements.
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It’s only $19.95 when you buy it directly from the
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.