|
What is Shared Pool, SGA, PGA, Library cache, Dictionary cache? ANS:- 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
Have a Oracle Question
Oracle Books
Oracle Application
Oracle Home
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
|