Basic concepts of Oracle-Oracle Memory Structure

Memory Structure of the database:
Memory structures are allocated to the Oracle instance when the instance is started.
The two major memory structures are known as the system global area (also called the shared
Global Area) and the program global area (also called the private global area or the process
Global Area)

SGA:
The system global area (SGA) is a shared memory area. All users of the database share
Information Maintained in this area. the SGA and the backgroud processes constitute an oracle instance. oracle allocates memory for the SGA when an oracle instance is started and de-allocates it when the istance is shut down. the information stored in the SGA is divided into multiple memory structures that are allocated fixed space when the instance started.
Database buffer cache:
The database buffer cache is the area of memory that caches the database area, holding Blocks
From the data files that have been read recently. The DB buffer cache is shared among all the users connected to the database.
There are three type of buffers.
Dirty buffers: Dirty buffers are the buffer blocks that need to be written to the data files.
The data in these buffers has changed and has not yet been written ti the disk.
Free buffers: Free buffers do not contain any data or are free to be overwritten. When Oracle reads data from disk, free buffers are used to hold this area.
Pinned buffers: pinned buffers are the ones that are currently being accessed or explicityly retained for futer use. (For example, the keep buffer)

Oracle maintains two lists to manage the buffer cache, the write list (dirty buffer list) has the buffers that are modified and need to be written to the disk (the dirty buffers ).
The least recently used (LRU) List contains free buffers, pinned buffers, and the dirty Buffers
That have not yet been moved to the write list.

When an oracle process accesses a buffer, it moves the buffer to the MRU end of the list.
So the most frequently accessed data is available in the buffers. When new data buffers are moved
To the LRU list, they are copied to the MRU end of the list, pushing out the buffers from the LRU end.
An exception to this occurs when a full table scan is done and the blocks from a full table scan are
Written to the LRU end of the list.
When an oracle process requests data, it searches the data in the buffer cache, and if it finds data, the result is a cache hit. if it cannot find the data, the result is a cache miss, and data needs to be copied from disk to buffer.
Before reading a data block into the cache, the process must first find a free buffer.
The server process on behalf of the user process searched either until it finds a free buffer
Or until it has searched the threshold limit of buffers. If the server process finds a dirty Buffer
As it searches the LRU list. It moves that buffer to write list and continues to search. When
Process finds a free buffer, it reads the data block from the disk into the buffer and moves
Buffer to the MRU end of the LRU list. If an ORACLE Server process searches the threshold limit
Buffers without finding a free buffer, the process stops searching and signals the dbwn background
Process to write some of the dirty buffers to disk.

Oracle 8i lets you divide the buffer pool into three areas (using the multiple buffer pool feature): The keep buffer pool retains the data blocks in memery; they are not aged our.
Recycle buffer pool removes the buffers from memory as soon as they are not needed. The default
Buffer contains the blocks that are not assigned to the other pools.

Shared Pool
Library cache:
The library cahce contains the shared SQL areas, private SQL areas, PL/SQL procedures and packages,
And control structures such as locks and library cache handles.
The shared SQL area is used for maintaining recently executed SQL commands and their execution
Plans. Oracle divides each SQL statement that it executes into a shared SQL area and a private SQL
Area. When two users are executing the same SQL, the information in the shared SQL area is used
Both. The shared SQL area contains the parse tree and execution plan, whereas the private
SQL area contains values for the bind variables (persisten area). Oracle creates teh runtime Area
As the first step of an execute request. For insert, update and delete statements, Oracle frees the runtime area after the statement has been executed. For queries, Oracle frees the runtime area only
After the statement has been executed. For queries, Oracle frees the runtime area only after all rows
Have been fetched or the query has been canceled.
Oracle processes PL/SQL program units the same way it processes SQL statements. When a PL/SQL Program unit is executed, the code is moved to the shared PL/SQL area while the individual SQL
Commands within the program until are moved to the shared SQL area. Again, the shared program units are maintained in memory with an LRU algorithm. shoud the same program unit be required by another
Process, disk I/O and compilation can be ommited, and the code that resides in memory will be executed.
The third area of the library cache is maintained for internal use by the instance. varous locks, latches, and other control structures reside here and are freely accessed by any server processes requring this information.

Data ditionary Cache
The data dictionary is a collection of database talbes and views containing metadata about
Database, its structures, its privileges, and its users.
Oracle accesses the data dictinary frequently during the parsing of SQL statements. The data dictinary cache holds the most recently used database dictionary information. The data ditinary Cache
Is also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data ).

 

Program global area:
The program global area (PGA) is the area in the memory that contains the data and process information for one process, and this area is non-shared memory. The contents of the PGA vary
Depending on the server configuration.
For a dedicated server configuration (one dedicated server process for each connection to
Database-dedicated server and multithreaded server provided aitions are discussed later in this
Chapter), the PGA holds stack space and session information. For multithreaded deployments (User
Connections go through a dispatcher, a smaller number of server processes are required as they can
Be shared by multiple user processes), the PGA has the stack space information (the session information is in the SGA ).

Stack space is the memory allocated to hold variables, arrays, and other information that belongs
To the session. PGA is allocated for each server process and de-allocated when the process is completed.
Unlike the SGA that is shared by serveral processes, the PGA provides sort space, session information, stack space, and cursor information for a single server process.

Sort Area
The memory area that Oracle uses to sort data is known as the sort area. which uses memory
From the PGA for a dedicated server connection.
For multithread server (MTS) invocations, the sort area is allocated from the SGA. MTS and
Dedicated server deployments are discussed later in this chapter.
Sort area size can grow depending on the need; the maximum size is set by the sort_area_size
Parameter. The parmameter sort_area_retained_size determines the size to which the sort area is
CED after the sort operation. The memory released from the sort area is kept with the server
Process; it is not released to the operating system.
If the data to be sorted does not fit into the memory area defined by sort_area_size, Oracle
Divides the data into smaller pieces that do fit, and these are sorted individualy. These individual sorts are called runs, and the data sorted is held on the user's temporary tablespace using temporary
Segments. When all the individual sorts are completed, these runs are merged to produce the final
Result. Oracle sorts the result set if the query contains a distint, order by, group by, or any set
Operators (Union, intersect, minus)