Exploring the oracle database architecture

Process Structure

Client process Background process Server process

Database

A database is a set of files, located on disk, that store data.

Session

A logical entity in the database instance that represents the state of a current user login to a database. A session lasts until the user disconnects or exits the connecting application.

Client process Connection

A physical communication pathway between a client process and database instance.

Java pool

It stores all session-specific java code and data writhin the Java virtual machine.

Database Buffer Cache

It stores copies of data blocks read from data files. A buffer is an address where the buffer manager temporarily caches a currently or recently used data blocks. It is optimize physical I/O; to keep frequently accessed blocks in the buffer chache; and to write infrequently accessed blocks to disk.

Background processess

Consolidate functions that otherwise would require multiple oracle database programs running for each client process, background processes perform I/O and monitor other Oracle database processes.

Background Processes

PMON SMON Database writer process Log writer process Check Point process MMON AND MMNL RECO

Background Process

PMON, SMON, DBWn, LGWR, CKPT, Other

Automatic Memory Management

Oracle manages the SGA and instance PGA memory automatically. This is the simplest method for managing memory and is strongly recommended by oracle. For databases created with DBCA using the basic installation option. automatic memory management is enabled by default.

Are logical and physical structure of oracle completely separate

YES

System Global Area (SGA)

shared pool Shared sql area, private sql area Data dictionary cache, Reserved pool Server Result cache, others Large Pool Response queue Request queue Database Buffer Cache redo log buffer Fixed SGA Java pool Steam pool

Data blocks

-At the finest level of granularity, data is sorted in data blocks. one data blocks corresponds to a specific number of bytes on disk.

CKPT

-updates the control file and data file headers. -performs full checkpoints only at database shutdown or on request.

Tablespaces

-A database is divided into logical storage units called table spaces. -It is a logical container for a segment. -can be permeanent or temporary

Segments

-A set of extents allocated for a user object eg, a table,or index. -Undo data or temporary data.

Extents

-An extent is a specific number of logicallly contiguous data blocks, obtained in a single allocation.

Streams pool

is used exclusively by oracle streams. It stores buffered queue messages and provides memory fro streams capture and apply processes.

Memory Management

Automatic Memory Management (AMM) Automatic Shared Memory Management (ASMM) Manual Shared Memory Management (MSMM)

Physical storage

Data files control files Online redo log files

Automatic shared memory management

Enables you to exercise more control over the size of the SGA and is the default when automatic memory management is disabled. Oracle will tune the the total SGA to a supplied target size and will also tune the sizes of SGA components. There are few components that take the memory from teh SGA_TARGET parameter, leaving the memory avilable for the auomatically tuned parameters. Following SGA components need to be manually sized. LOG_BUFFER, DB_KEEP_CACHE_SIZE, DB_nk_CACHE_SIZE

SMON

Is in charge of a variety of system level cleanup duties. performing instance recover at instance startup if necessary recovering any terminated translocation that were skipped during instance recovery cleaning up unused temporary segments

User global area (UGA)

It is a memory associated with a user session.

Fixed SGA

It is an internal housekeeping area. It contains general information required by the background processes about the state of the database and the instance. The size of the fixed SGA is set by the oracle database and cannot be altered manually.

PMON

It monitors all the other background processes. When a server or dispatcher process terminates abnormally, it performs process recover. Cleans up the database buffer cache and freeing resources that were allocated for client process. It releases the resources held by failed user process, rolls back the transaction that caused the process to fail, and releases the row and table level locks held by the failed user process. PMON rolls back the transaction that caused the process to fail, the data up to the last commit before the abnormal termination is retained n the database.

LGWR

Manages the redo log buffer a user commits a transaction an online redo log switch occurs 3 seconds have passed The redo log buffer is one third full. DBWN must write modified buffers to disk.

SGA

is a group of shared memory structures that contain data and control information for a single oracle database instance. It is shared by all server and background processes, eg, cached data blocks and shared sql areas.

Client process

These process run the software code for an application program or an oracle tool. Client process interact with serve processes in order to interface with the database. When the client process runs on the client machine, it is knows as two tier architecture.

Large pool

is an optional memory area in the SGA. It is intended for memory allocations that are larger than is appropriate to store in the shared pool. Eg, UGA and ORacle XVA andd buffers for RMAN I/O slaves.

PGA(Program Global Area)

a memory region that is not shared, it is private. It contains data and control information exclusively for the use of an oracle process. A pga is created when an oracle process is started.

Database Instance

a set of memory structures that manage database files. An oracle instance consists of a shared memory area and set of background processes.

Software code area

are portions of memory used to store code that is being run or can be run.

Shared pool

caches various types of program data required by the server. A partial list includes storing parsed sql, pl/sql code, system parameters, and data dictionary information. It is involved in almost every operation that occurs in the database.

Server processes

communicate with client processes and interact with the database to fulfill requests. A client porcess cannot communicate with the database on its own. It can perform the following tasks: 1 Parse and run sql statements 2 Execute PL/SQL code 3 Read data blocks from data files into the database buffer cache 4 Return results from the database to the application

Logical data

consists of data only veiwable from within the database itself.

Physical data

consists of what is viewable at the operating level eg, datafiles can be listed using IS command

Logical Storage

data blocks extents segments tablespaces

Redo Log Buffer

is a circular buffer that stores redo entries describing changes made to the database. These entries contain the information required to reconstruct changes made to the databases by DML or DDL operations.

DBWN

writes modified buffers in the database buffer cache to disk: There are no free buffers Every 3 seconds During a checkpoint When there are too many dirty buffers When the instance shutdown cleanly when a table space changes status.

Manual Shared Memory Mangement

you set the sizes of several individual SGA components and manually tune individual SGA components. This option provides complete control of individual SGA component size.