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.
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.
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.
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.
A PGA always contains a stack space, which is memory allocated to hold a session's variables, arrays, and other information.
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.
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
Have a Oracle Question
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.