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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle PGA Tips

Oracle Tips by Burleson Consulting

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


Oracle PGA Concepts

Program Global Area (PGA)

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a non-shared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf. It contains a private SQL area and a session memory area.

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

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. For more information, click on ?Oracle PGA Sizing?.

Oracle Database 12c 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 pga_aggregate_target parameter.

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.  The Oracle docs list these methods of PGA management and the associated parameters:

SGA

PGA

Description

Initialization Parameters

Instance
Auto

Instance
Auto

The database tunes the size of the instance based on a single instance target size.

you set:

  • total memory target size for the database instance (memory_target)
  • optional maximum memory size for the database instance (memory_max_target)

Auto

Auto

The database automatically tunes the SGA based on an SGA target.

The database automatically tunes the PGA based on a PGA target.

you set:

  • SGA target size (sga_target)
  • optional SGA maximum size (sga_max_size)
  • PGA aggregate target size (pga_aggregate_target)

the database automatically configures the pga_aggregate_limit initialization parameter. you may set this parameter manually.

Auto

Manual

The database automatically tunes the SGA based on an SGA target.

You control the PGA manually, setting the maximum work area size for each type of SQL

you set:

  • sga target size (sga_target)
  • optional SGA maximum size (sga_max_size)
  • PGA work area parameters such as sort_area_size, hash_area_size, and bitmap_merge_area_size

Manual

Auto

You control the SGA manually by setting individual component sizes.

The database automatically tunes the PGA based on a PGA target.

you set:

  • shared pool size (shared_pool_size)
  • buffer cache size (db_cache_size)
  • large pool size (large_pool_size)
  • java pool size (java_pool_size)
  • streams pool size (streams_pool_size)
  • pga aggregate target size (pga_aggregate_target)foot 2

the database automatically configures the pga_aggregate_limit initialization parameter. you may set this parameter manually.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

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 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.