oracle architecture components

Oracle Architecture -1

Text in blue and black can be changedPositions in Blue text cannot be alteredText in black can be altered in position and sizes if need beText in yellow ochre is meant for legal matters and updates

L&D - IRM

Oracle Architectural Components



Objectives

After completing this lesson, you should be able to do the following:Outline the Oracle architecture and its main

componentsList the structures involved in connecting a user

to an Oracle Instance



Passwordfile

Overview of Primary ComponentsInstance

SGA

Redo LogBuffer

Shared Pool

Data DictionaryCache

LibraryCache

DBWRSMONPMON CKPTLGWR Others

Userprocess

Serverprocess

PGA

Control files

Datafiles

Database

DatabaseBuffer Cache

Redo Log files

Java Pool Large Pool

Parameterfile

Archived Log files



Oracle Server

An Oracle server:Is a database management

system that provides an open, comprehensive, integrated approach to information management

Consists of an Oracle Instance and an Oracle database

Oracle Server



Oracle Instance

An Oracle Instance:Is a means to access an Oracle databaseAlways opens one and only one databaseConsists of memory and background process structures

Background process structures

Memory structures

Instance

SGA

Redo LogBuffer

Shared Pool

Data DictionaryCache

LibraryCache

DBWRSMONPMON CKPTLGWR Others

DatabaseBuffer Cache

Java Pool Large Pool



Establishing a Connection and Creating a Session

Connecting to an Oracle Instance: Establishing a user connection Creating a session

Session created

Database user

Userprocess

ServerprocessConnection

established

Oracle Server



Oracle Database

An Oracle database:Is a collection of data that is treated as a unitConsists of three file types

Passwordfile

Parameterfile

Archived Log files

Control files

Datafiles Redo Log files

Oracle Database



Physical Structure

The physical structure includes three types of files:Control filesDatafilesRedo log files

Controlfiles

Datafiles(includes

Data Dictionary)

Header

OnlineRedo Log

files



Memory Structure

Oracle’s memory structure consists of two memory areas known as: System Global Area (SGA): Allocated at instance

startup, and is a fundamental component of an Oracle Instance

Program Global Area (PGA): Allocated when the server process is started



System Global Area

The SGA consists of several memory structures: Shared Pool Database Buffer Cache Redo Log Buffer Other structures (for example, lock and latch

management, statistical data)There are two additional memory structures that

can be configured within the SGA: Large Pool Java Pool



System Global Area

SGA is dynamicSized by the SGA_MAX_SIZE parameterAllocated and tracked in granules by SGA

components Contiguous virtual memory allocation Granule size based on total estimated SGA_MAX_SIZE



Shared PoolUsed to store:

Most recently executed SQL statements Most recently used data definitions

It consists of two key performance-related memory structures: Library Cache Data Dictionary Cache

Sized by the parameter SHARED_POOL_SIZE

Shared Pool

DataDictionary

Cache

LibraryCache

ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;



Library Cache

Stores information about the most recently used SQL and PL/SQL statements

Enables the sharing of commonly used statements

Is managed by a least recently used (LRU) algorithm

Consists of two structures: Shared SQL area Shared PL/SQL area

Size determined by the Shared Pool sizing



Data Dictionary CacheA collection of the most recently used definitions in the

databaseIncludes information about database files, tables, indexes,

columns, users, privileges, and other database objectsDuring the parse phase, the server process looks at the

data dictionary for information to resolve object names and validate access

Caching data dictionary information into memory improves response time on queries and DML

Size determined by the Shared Pool sizing



Database Buffer Cache

Stores copies of data blocks that have been retrieved from the datafiles

Enables great performance gains when you obtain and update data

Managed through an LRU algorithmDB_BLOCK_SIZE determines primary block size

Database BufferCache



Database Buffer CacheConsists of independent sub-caches:

DB_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE

Can be dynamically resized

DB_CACHE_ADVICE set to gather statistics for predicting different cache size behavior

Statistics displayed by V$DB_CACHE_ADVICE

ALTER SYSTEM SET DB_CACHE_SIZE = 96M;



Redo Log BufferRecords all changes made to the database data

blocks Primary purpose is recoveryChanges recorded within are called redo entriesRedo entries contain information to reconstruct

or redo changesSize defined by LOG_BUFFER

Redo LogBuffer



Large PoolAn optional area of memory in the SGA Relieves the burden placed on the Shared PoolUsed for:

Session memory (UGA) for the Shared Server I/O server processes Backup and restore operations or RMAN Parallel execution message buffers

– PARALLEL_AUTOMATIC_TUNING set to TRUE

Does not use an LRU listSized by LARGE_POOL_SIZE



Java Pool

Services parsing requirements for Java commandsRequired if installing and using JavaSized by JAVA_POOL_SIZE parameter



Program Global Area

Memory reserved for each user process connecting to an Oracle database

Allocated when a process is created

Deallocated when the process is terminated

Used by only one process

Userprocess

PGAServer

process



Process Structure

Oracle takes advantage of various types of processes:User process: Started at the time a database user

requests connection to the Oracle serverServer process: Connects to the Oracle Instance

and is started when a user establishes a sessionBackground processes: Started when an Oracle

Instance is started



User Process

A program that requests interaction with the Oracle server

Must first establish a connection Does not interact directly with the Oracle server

Database user

Serverprocess

Userprocess

Connectionestablished



Server Process

A program that directly interacts with the Oracle server Fulfills calls generated and returns resultsCan be Dedicated or Shared Server

Connection established Session created

Database user

Userprocess

Serverprocess

Oracle server



Background ProcessesMaintains and enforces relationships between physical

and memory structuresMandatory background processes:

DBWn PMON CKPT LGWR SMON

Optional background processes: ARCn LMDn RECO CJQ0 LMON Snnn Dnnn Pnnn LCKn QMNn



Database Writer (DBWn)DBWn writes when:

Checkpoint occursDirty buffers reach

thresholdThere are no free buffersTimeout occursRAC ping request is madeTablespace OFFLINETablespace READ ONLYTable DROP or TRUNCATE

Tablespace BEGIN BACKUP

Instance

SGA

Control files


Database

DBWn

DatabaseBufferCache



Log Writer (LGWR)

LGWR writes:At commit When one-third fullWhen there is 1 MB of

redoEvery three secondsBefore DBWn writes

Instance

SGA

Control files


Database

Redo Log

Buffer

DBWn LGWR



System Monitor (SMON) Responsibilities:

Instance recovery Rolls forward

changes in redo logs Opens database for

user access Rolls back

uncommitted transactions

Coalesces free spaceDeallocates temporary

segments

Control files


Database

InstanceSGA

SMON



Process Monitor (PMON)

Cleans up after failed processes by:

Rolling back the transaction

Releasing locksReleasing other resourcesRestarting dead

dispatchersPGA area

Instance

SGA

PMON



Checkpoint (CKPT)

Responsible for:Signaling DBWn at

checkpointsUpdating datafile headers

with checkpoint information

Updating control files with checkpoint information

Control files


Database

Instance

SGA

DBWn LGWR CKPT



Archiver (ARCn)

Optional background processAutomatically archives online redo logs when ARCHIVELOG mode is set

Preserves the record of all changes made to the database

ARCn Archived Redo Log

files

Control files




Logical Structure

Dictates how the physical space of a database is usedHierarchy consisting of tablespaces, segments, extents,

and blocks

Tablespace

DatafileSegment

BlocksExtent

Segment



Processing SQL Statements

Connect to an instance using: User process Server process

The Oracle server components that are used depend on the type of SQL statement: Queries return rows DML statements log changes Commit ensures transaction recovery

Some Oracle server components do not participate in SQL statement processing



Processing SQL Statements

Processing a query: Parse:

– Search for identical statement– Check syntax, object names, and

privileges– Lock objects used during parse– Create and store execution plan

Bind: Obtains values for variables Execute: Process statement Fetch: Return rows to user process



SummaryIn this lesson, you should have learned how to:

Explain database files: datafiles, control files, online redo logs

Explain SGA memory structures: Database Buffer Cache, Shared Pool, and Redo Log Buffer

Explain primary background processes: DBWn, LGWR, CKPT, PMON, SMON

Explain the use of the background process ARCnIdentify optional and conditional background

processesExplain logical hierarchy



QUIZ1. Match the process with the taska. DBWR b. LGWR c. SMON d. PMON e. CKPT1. Assists with writing to the data file headers2. Responsible for instance recovery3. Cleans up after failed processes4. Records database changes for recovery purposes5. Writes dirty buffers to the data files

Answers:– 5– 4– 2– 3– 1



QUIZ

2 . Which one of the following memory areas is not part of the SGA?

a. Database Buffer Cache

b PGA

c Redo Log Buffer

d. Shared Pool

Answer: PGA



QUIZ

3. Which one of the following memory areas is used to cache the data dictionary information?

a Database Buffer Cacheb PGAc Redo Log Buffer d Shared Pool

Answer: D



QUIZ

4 . Which two of the following statements are true about the Shared Pool?

a. The Shared Pool consists of the Library Cache, Data Dictionary Cache, Shared SQL area, Java Pool, and Large Pool.

b. The Shared Pool is used to store the most recently executed SQL statements.

c. The Shared Pool is used for an object that can be shared globally.d . The Library Cache consist of the Shared SQL and Shared

PL/SQL areas.

Answer: B& D



QUIZ

5 .Which one of the following statements is true?a. An Oracle server is a collection of data consisting

of three file types.b. A user establishes a connection with the

database by starting an Oracle Instance.c. A connection is a communication pathway

between the Oracle server and theOracle Instance.

d. A session starts when a user is validated by the Oracle server.

Answer: D



QUIZ

6. The primary purpose of the Redo Log Buffer is to record all changes to the database data blocks.

a Trueb False

Answer : A



QUIZ

6. The PGA is a memory region that contains data and control information for multiple server processes or multiple background processes.

a Trueb False

Answer: B



QUIZ

7. Which of the following becomes available when an Oracle Instance is started?

a User processb Server processc Background processes

Answer : C

oracle architecture components

Documents