中科信软培训中心 www.info-soft.cn
Oracle Architectural Components
中科信软培训中心 www.info-soft.cn1-2
Objectives
After completing this lesson, you should be able to do
the following:
• Outline the Oracle architecture and its main
components
• List the structures involved in connecting a user to
an Oracle instance
中科信软培训中心 www.info-soft.cn1-3
Password
file
Overview of Primary Components
Instance
SGA
Redo Log
Buffer
Shared Pool
Data Dictionary
Cache
Library
Cache
DBWRSMONPMON CKPTLGWR Others
User
process
Server
process
PGA
Control
filesData
files
Database
Database
Buffer Cache
Redo Log
files
Java Pool Large Pool
Parameter
file
Archived
Log files
中科信软培训中心 www.info-soft.cn1-5
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
中科信软培训中心 www.info-soft.cn1-6
Oracle Instance
An Oracle instance:
• Is a means to access an Oracle database
• Always opens one and only one database
• Consists of memory and background process
structures
Background
process structures
Memory
structures
Instance
SGA
Redo Log
Buffer
Shared Pool
Data Dictionary
Cache
Library
Cache
DBWRSMONPMON CKPTLGWR Others
Database
Buffer Cache
Java Pool Large Pool
中科信软培训中心 www.info-soft.cn1-7
Establishing a Connection
and Creating a Session
Connecting to an Oracle instance:
• Establishing a user connection
• Creating a session
Session created
Database user
User
process
Server
processConnection
established
Oracle server
中科信软培训中心 www.info-soft.cn1-9
Oracle Database
An Oracle database:
• Is a collection of data that is treated as a unit
• Consists of three file types
Password
file
Parameter
file
Archived
Log files
Control
filesData
files
Redo
Log
files
Oracle Database
中科信软培训中心 www.info-soft.cn1-10
Physical Structure
The physical structure includes three types of files:
• Control files
• Data files
• Online redo log files
Control
filesData
files
(includes
Data
Dictionary)
Header
Online
Redo Log
files
中科信软培训中心 www.info-soft.cn1-11
Memory Structure
Oracle’s memory structure consists of two memory
areas known as:
• System Global Area (SGA): Allocated at instance
start up, and is a fundamental component of an
Oracle instance
• Program Global Area (PGA): Allocated when the
server process is started
中科信软培训中心 www.info-soft.cn1-12
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
中科信软培训中心 www.info-soft.cn1-14
System Global Area
• Is dynamic
• Sized by the SGA_MAX_SIZE parameter
• Allocated and tracked in granules by SGA
components
– Contiguous virtual memory allocation
– Granule size based on total estimated SGA_MAX_SIZE
中科信软培训中心 www.info-soft.cn1-15
Shared Pool
• Used 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
Data
Dictionary
Cache
Library
Cache
ALTER SYSTEM SET
SHARED_POOL_SIZE = 64M;
中科信软培训中心 www.info-soft.cn1-16
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
中科信软培训中心 www.info-soft.cn1-17
Data Dictionary Cache
• A collection of the most recently used definitions in
the database
• Includes information about database files, tables,
indexes, columns, users, privileges, and other
database objects
• During 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
中科信软培训中心 www.info-soft.cn1-18
Database Buffer Cache
• Stores copies of data blocks that have been
retrieved from the data files
• Enables great performance gains when you obtain
and update data
• Managed through an LRU algorithm
• DB_BLOCK_SIZE determines primary block size
Database Buffer
Cache
中科信软培训中心 www.info-soft.cn1-19
Database Buffer Cache
• Consists of independent subcaches:
– 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;
中科信软培训中心 www.info-soft.cn1-21
Redo Log Buffer
• Records all changes made to the database data
blocks
• Primary purpose is recovery
• Changes recorded within are called redo entries
• Redo entries contain information to reconstruct or
redo changes
• Size defined by LOG_BUFFER
Redo Log
Buffer
中科信软培训中心 www.info-soft.cn1-22
Large Pool
• An optional area of memory in the SGA
• Relieves the burden placed on the Shared Pool
• Used 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 list
• Sized by LARGE_POOL_SIZE
• Can be dynamically resized
中科信软培训中心 www.info-soft.cn1-24
Java Pool
• Services parsing requirements for Java commands
• Required if installing and using Java
• Sized by JAVA_POOL_SIZE parameter
中科信软培训中心 www.info-soft.cn1-25
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
User
process
PGA
Server
process
中科信软培训中心 www.info-soft.cn1-28
Process Structure
Oracle takes advantage of various types of processes:
• User process: Started at the time a database user
requests connection to the Oracle server
• Server process: Connects to the Oracle instance
and is started when a user establishes a session
• Background processes: Started when an Oracle
instance is started
中科信软培训中心 www.info-soft.cn1-29
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
Server
process
User
process
Connection
established
中科信软培训中心 www.info-soft.cn1-30
Server Process
• A program that directly interacts with the Oracle
server
• Fulfills calls generated and returns results
• Can be dedicated or shared server
Connection
established Session created
Database user
User
process
Server
process
Oracle server
中科信软培训中心 www.info-soft.cn1-31
Background Processes
Maintains and enforces relationships between physical
and memory structures:
• Mandatory background processes:
DBWn PMON CKPT
LGWR SMON
• Optional background processes:
ARCn LMDn QMNn
CJQ0 LMON RECO
Dnnn LMS Snnn
LCKn Pnnn
中科信软培训中心 www.info-soft.cn1-32
Database Writer (DBWn)
DBWn writes when:
• Checkpoint occurs
• Dirty buffers reach
threshold
• There are no free buffers
• Timeout occurs
• RAC ping request is made
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN
BACKUP
Instance
SGA
Control
files
Data
files
Redo
Log
files
Database
DBWn
Database
Buffer
Cache
中科信软培训中心 www.info-soft.cn1-33
Log Writer (LGWR)
LGWR writes:
• At commit
• When one-third full
• When there is 1 MB of
redo
• Every three seconds
• Before DBWn writes
Instance
SGA
Control
filesData files
Redo
Log
files
Database
Redo Log
Buffer
DBWn LGWR
中科信软培训中心 www.info-soft.cn1-34
System Monitor (SMON)
Responsibilities:
• Instance recovery
– Rolls forward
changes in online
redo log files
– Opens database
for user access
– Rolls back
uncommitted
transactions
• Coalesces free space
• Deallocates
temporary segments
Control
filesData files
Redo
Log
files
Database
Instance
SGA
SMON
中科信软培训中心 www.info-soft.cn1-35
Process Monitor (PMON)
Cleans up after failed
processes by:
• Rolling back the
transaction
• Releasing locks
• Releasing other
resources
• Restarting dead
dispatchers
PGA area
Instance
SGA
PMON
中科信软培训中心 www.info-soft.cn1-36
Checkpoint (CKPT)
Responsible for:
• Signaling DBWn at
checkpoints
• Updating datafile
headers with
checkpoint
information
• Updating control
files with checkpoint
information
Control
filesData files
Redo
Log
files
Database
Instance
SGA
DBWn LGWR CKPT
中科信软培训中心 www.info-soft.cn1-37
Archiver (ARCn)
• Optional background process
• Automatically archives online redo log files when
ARCHIVELOG mode is set
• Preserves the record of all changes made to the
database
ARCn Archived
redo log
files
Control
filesData
files
Redo
log
files
中科信软培训中心 www.info-soft.cn1-39
Logical Structure
• Dictates how the physical space of a database is
used
• Hierarchy consisting of tablespaces, segments,
extents, and blocks
Tablespace
Datafile
Segment
BlocksExtent
Segment
中科信软培训中心 www.info-soft.cn1-42
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.
中科信软培训中心 www.info-soft.cn1-44
Summary
In this lesson, you should have learned how to:
• Explain database files: data files, control files,
online redo log files
• 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 optional background process
ARCn
• Identify optional and conditional background
processes
• Explain logical hierarchy
中科信软培训中心 www.info-soft.cn1-45
Practice 1 Overview
This practice covers the following topics:
• Reviewing architectural components
• Identifying structures involved in connecting a user
to an Oracle instance
Oracle Architectural Components
Objectives
Overview of Primary Components
Oracle Server
Oracle Instance
Establishing a Connection�and Creating a Session
Oracle Database
Physical Structure
Memory Structure
System Global Area
System Global Area
Shared Pool
Library Cache
Data Dictionary Cache
Database Buffer Cache
Database Buffer Cache
Redo Log Buffer
Large Pool
Java Pool
Program Global Area
Process Structure
User Process
Server Process
Background Processes
Database Writer (DBWn)
Log Writer (LGWR)
System Monitor (SMON)
Process Monitor (PMON)
Checkpoint (CKPT)
Archiver (ARCn)
Logical Structure
Processing SQL Statements
Summary
Practice 1 Overview
本文档为【oracle培训课件】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑,
图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
该文档来自用户分享,如有侵权行为请发邮件ishare@vip.sina.com联系网站客服,我们会及时删除。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。