Shared Pool, SGA, PGA, Library and Dictionary cache

What is Shared Pool, SGA, PGA, Library cache, Dictionary cache?


Software code areas 

Software code areas are portions of memory used to store code that is being or may be executed. The code for Oracle is stored in a software area, which is typically at a location different from users programs -- a more exclusive or protected location.
The system global area (SGA) 

A System Global Area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, the data in the instance's SGA is "shared" among the users.  Consequently, the SGA is The database buffer cache The database buffer cache is a portion of the SGA that holds copies of the data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

The redo log buffer 

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by  INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.

The shared pool 

The shared pool is an area in the SGA that contains three major areas: library cache, dictionary cache, and control structures.
Library Cache:- 

The library cache includes shared SQL areas, private SQL areas, PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Dictionary Cache:- 

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. ince the data dictionary is accessed so often by Oracle, one special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also know as the row cache.

Program global areas (PGA)

The Program Global Area (PGA) is a memory region that contains data and control information for a single process (server or background).   Consequently, the PGA is referred to as the "Program Global Area" or the "Process Global Area." PGA contains Stack area and Data areas.  

Stack areas 

A PGA always contains a stack space, which is memory allocated to hold a session's variables, arrays, and other information. 

Data areas 

A PGA in an instance running without the multi-threaded server requires additional memory for the user's session, such as private SQL areas and other information. If the instance is running the multi-threaded server, this extra memory is not in the PGA, but is instead allocated in the SGA. 

Sort areas

Sorting requires space in memory. Portions of memory in which Oracle sorts data are called sort areas. A sort area exists in the memory of an Oracle user process that requests a sort. A sort area can grow to accommodate the amount of data to be sorted but is limited by the value of the initialization parameter SORT_AREA_SIZE. The 
value of this parameter is expressed in bytes. The default value varies depending on your operating system.  

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.