第一章 Oracle achitechtural components
user process -> server process(PGA) -> INSTANCE { SGA [ shared pool(library cache,data dict cache),database buffer cache,redolog buffer cache], processes [PMON,SMON,DBWR,LGWR,CKPT... ...]} -> DATABASE {data files,control files,redo log files, }(parameter files,password files,archive log files)。
因为贴图不方便只能用这样的一个简单模式描述Oracle主要的components
这其中有几个重点地方需要注意。
1、PGA在dedicated 模式和shared server模式下是不一样的。在dedicated模式下PGA的内容如下 stack space, session information,sort area,curssor information.而在shared server模式下session information是放在SGA中的。
2、几个重要的概念，instance 实例，包括memory structure和 backgroud structure。database 数据库，不过要注意这里Oracle的数据库特指数据文件或者说是物理结构。包括三类文件control files,data files,redo log files。还有2个重要的文件，但不是database的组成部分，它们是parameter files和archive log files。
3、memory structure 包括 SGA,PGA ， SGA由shared pool, database buffer cache, redo log buffer cache和一些其他结构如 lock latch management，和statistical data 组成，通常还有其他2个pool,large pool和java pool.
4、SGA 设置
SGA是由几个parameter决定其大小的，对SGA大小影响最大的几个parameter如下：
DB_CACHE_SIZE:标准block的cache 的size.
LOG_BUFFER：The number of bytes allocated for the redo log buffer cache.
SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL.
LARGE_POOL_SIZE: The size of the large pool; the default is zero.
SGA 是通过SGA_MAX_SIZE来动态设置的。
Unit of Allocation
A granule is a unit of contiguous virtual memory allocation. The size of a granule depends on the estimated total SGA size whose calculation is based on the value of the parameter SGA_MAX_SIZE.
– 4 MB if estimated SGA size is < 128 MB
– 16 MB otherwise
The components (buffer cache, shared pool, and large pool) are allowed to grow and shrink based on granule boundaries. For each component which owns granules, the number of granules allocated to the component, any pending operations against the component (e.g.,
allocation of granules via ALTER SYSTEM, freeing of granules via ALTER SYSTEM, corresponding self-tuning), and target size in granules will be tracked and displayed by the V$BUFFER_POOL view. At instance startup, the Oracle server allocates granule entries, one
for each granule to support SGA_MAX_SIZE bytes of address space. As startup continues, each component acquires as many granules as it requires. The minimum SGA configuration is three granules (one granule for fixed SGA (includes redo buffers; one granule for buffer cache; one granule for shared pool).
A granule is a unit of contiguous virtual memory allocation. The size of a granule depends on the estimated total SGA size whose calculation is based on the value of the parameter SGA_MAX_SIZE.
– 4 MB if estimated SGA size is < 128 MB
– 16 MB otherwise
The components (buffer cache, shared pool, and large pool) are allowed to grow and shrink based on granule boundaries. For each component which owns granules, the number of granules allocated to the component, any pending operations against the component (e.g.,
allocation of granules via ALTER SYSTEM, freeing of granules via ALTER SYSTEM, corresponding self-tuning), and target size in granules will be tracked and displayed by the V$BUFFER_POOL view. At instance startup, the Oracle server allocates granule entries, one
for each granule to support SGA_MAX_SIZE bytes of address space. As startup continues, each component acquires as many granules as it requires. The minimum SGA configuration is three granules (one granule for fixed SGA (includes redo buffers; one granule for buffer cache; one granule for shared pool).
通过这段可以知道oracle是如何分配SGA中各组件的空间。是通过granule来控制的，每个组件的大小是整数个的granule。而granule的大小是受制于SGA_MAX_SIZE。
5、shared pool
shared pool 包括2个组件，library cache 和 data dictionary cache，它的大小是由SHARED_POOL_SIZE参数来控制的。
比如：ALTER system set shared_pool_size = 64M;
Sizing the Shared Pool
Since the shared pool is used for objects that can be shared globally, such as reusable SQL execution plans; PL/SQL packages, procedures, and functions; and cursor information, it must be sized to accommodate the needs of both the fixed and variable areas. Memory
allocation for the shared pool is determined by the SHARED_POOL_SIZE initialization parameter. It can be dynamically resized using ALTER SYSTEM SET. After performance analysis, this can be adjusted but the total SGA size cannot exceed SGA_MAX_SIZE.
Since the shared pool is used for objects that can be shared globally, such as reusable SQL execution plans; PL/SQL packages, procedures, and functions; and cursor information, it must be sized to accommodate the needs of both the fixed and variable areas. Memory
allocation for the shared pool is determined by the SHARED_POOL_SIZE initialization parameter. It can be dynamically resized using ALTER SYSTEM SET. After performance analysis, this can be adjusted but the total SGA size cannot exceed SGA_MAX_SIZE.
Library Cache
The library cache stores information about the most recently used SQL and PL/SQL statements.
The library cache stores information about the most recently used SQL and PL/SQL statements.
The library cache:
• 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
• Has its size determined by the shared pool sizing
• 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
• Has its size determined by the shared pool sizing
Data Dictionary Cache
The data dictionary cache is a collection of the most
recently used definitions in the database.
• It 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 the data dictionary information into
memory improves response time on queries.
• Size is determined by the shared pool sizing.
The data dictionary cache is a collection of the most
recently used definitions in the database.
• It 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 the data dictionary information into
memory improves response time on queries.
• Size is determined by the shared pool sizing.
6、Database Buffer Cache
The database buffer cache stores copies of data
blocks that have been retrieved from the data files.
• It enables great performance gains when you
obtain and update data.
• It is managed through a least recently used (LRU)
algorithm.
• DB_BLOCK_SIZE
determines the
primary block size.
blocks that have been retrieved from the data files.
• It enables great performance gains when you
obtain and update data.
• It is managed through a least recently used (LRU)
algorithm.
• DB_BLOCK_SIZE
determines the
primary block size.
database buffer cache中每个buffer的大小等于ORACLE中block的大小。由DB_BLOCK_SIZE参数决定的。
DB_CACHE_SIZE: Sizes the default buffer cache size only, it always exists and cannot be
set to zero.
DB_KEEP_CACHE_SIZE: Sizes the keep buffer cache, which is used to retain blocks in memory that are likely to be reused.
DB_RECYCLE_CACHE_SIZE: Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little change of being reused.
set to zero.
DB_KEEP_CACHE_SIZE: Sizes the keep buffer cache, which is used to retain blocks in memory that are likely to be reused.
DB_RECYCLE_CACHE_SIZE: Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little change of being reused.
database buffer cache 可以动态的通过
alter system set db_cache_size = 96M
来调整。
Buffer Cache Advisory Parameter 通过统计数据给预定的特性不同的cache size，可以帮助DBA来调整cache的大小。可以通过V$DB_CACHE_ADVICE视图来查看。
通过alter system set db_cache_advice = (OFF,ON,READY)来关闭，开启，READY主要是在on,off之间切换的时候预分配存储空间从而避免ORA-4031错误。
7、Redo Log Buffer Cache
The redo log buffer cache records all changes made to the database data blocks.
• Its primary purpose is recovery.
• Changes recorded within are called redo entries.
• Redo entries contain information to reconstruct or redo changes.
• Size is defined by LOG_BUFFER.
• Its primary purpose is recovery.
• Changes recorded within are called redo entries.
• Redo entries contain information to reconstruct or redo changes.
• Size is defined by LOG_BUFFER.
redo log buffer cache是一个循环使用的buffer用来存储data file block的改变。这些信息存储在redo entrie中，redo entries存放有重新创建那些被INSERT,UPDATE,DELETE,CREATE,ALTER 或 DROP 等操作改变前的数据的必须信息。
8、Large Pool
The large pool is an optional area of memory in the SGA configured only in a shared server environment.
• It relieves the burden placed on the shared pool.
• This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations.
• Unlike the shared pool, the large pool does not use an LRU list.
• Sized by LARGE_POOL_SIZE.
ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;
• It relieves the burden placed on the shared pool.
• This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations.
• Unlike the shared pool, the large pool does not use an LRU list.
• Sized by LARGE_POOL_SIZE.
ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;
这里要注意large pool 只在shared server模式下有，它存在于shared pool 中。用途主要是UGA、I/O Slaves、和备份，恢复操作。并且不使用LRU算法管理。通过LARGE_POOL_SIZE管理空间。
9、Java Pool
The Java pool services the parsing requirements for
Java commands.
• Required if installing and using Java.
• It is stored much the same way as PL/SQL in database tables.
• It is sized by the JAVA_POOL_SIZE parameter.
The Java pool services the parsing requirements for
Java commands.
• Required if installing and using Java.
• It is stored much the same way as PL/SQL in database tables.
• It is sized by the JAVA_POOL_SIZE parameter.
10、Process Structure
An Oracle process is a program that depending on its type can request information, execute a series of steps, or perform a specific task.
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 process: Available when an Oracle instance is started
An Oracle process is a program that depending on its type can request information, execute a series of steps, or perform a specific task.
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 process: Available when an Oracle instance is started
process是一些不同类型的程序，起不同的作用，主要分三大类、user process,server process,background process。
11、user process
简单的说就是用户连接到oracle server时需要首先开始一个user process。user process可以通过sql/plus这样的工具来产生。要注意的是user process并不直接和oracle server交互，而是调用UPI(user program interface)来产生一个进程(session)开始一个server process。
12、server process
直接和oracle server打交道，它所有的职能就是返回结果，可以使dedicated模式或者shared server模式。
13、后台进程
The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes.
• Mandatory background processes
DBWn PMON CKPT
LGWR SMON RECO
• Optional background processes
ARCn LMON Snnn
QMNn LMDn
CJQ0 Pnnn
LCKn Dnnn
• Mandatory background processes
DBWn PMON CKPT
LGWR SMON RECO
• Optional background processes
ARCn LMON Snnn
QMNn LMDn
CJQ0 Pnnn
LCKn Dnnn
这是backgroud process的全家福，作用是维护oracle数据库物理结构和内存结构之间的关系。作用简单说明如下。
• RECO: Recoverer
• QMNn: Advanced Queuing
• ARCn: Archiver
• LCKn: RAC Lock Manager—Instance Locks
• LMON: RAC DLM Monitor—Global Locks
• LMDn: RAC DLM Monitor—Remote Locks
• CJQ0: Snapshot Refresh
• Dnnn: Dispatcher
• Snnn: Shared Server
• Pnnn: Parallel Query Slaves
• QMNn: Advanced Queuing
• ARCn: Archiver
• LCKn: RAC Lock Manager—Instance Locks
• LMON: RAC DLM Monitor—Global Locks
• LMDn: RAC DLM Monitor—Remote Locks
• CJQ0: Snapshot Refresh
• Dnnn: Dispatcher
• Snnn: Shared Server
• Pnnn: Parallel Query Slaves
14、下面重点介绍几个重要的后台进程
DBWn
The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It ensures that a sufficient number of free buffers—buffers that can be overwritten when server processes need to read in blocks from the data files—are available in the database buffer cache. Database performance is improved because server processes make changes only in the buffer cache.
这段要重点读一下
这个server process记录数据的改变并写到rollback和buffer cache中的data block中。DBWn 把脏buffer从database buffer中写到data file中。它保证一定量的可用的buffer在database buffer cache中——可以写的，当server process 需要从数据文件中读数据块时。数据库的性能因为server process仅仅在buffer cache中改变数据而得到提升。
DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP
DBWn在以上几点情况下把buffer 中的数据写到data file。
15、log writer(LGWR)
LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations:
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk.
LGWR can also call on DBWn to write to the data files.
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk.
LGWR can also call on DBWn to write to the data files.
LGWR 仅在redo写到磁盘上后确认commit。LGWR同时调用DBWn把数据写到data file注意是data file不是redo log。
16、System Monitor (SMON)
Responsibilities:
• Instance recovery:
– Rolls forward changes in the redo logs
– Opens the database for user access
– Rolls back uncommitted transactions
• Coalesces free space ever 3 sec
• Deallocates temporary segments
Responsibilities:
• Instance recovery:
– Rolls forward changes in the redo logs
– Opens the database for user access
– Rolls back uncommitted transactions
• Coalesces free space ever 3 sec
• Deallocates temporary segments
SMON 的作用主要是系统维护,包括实例的恢复,每3秒收集空闲空间,释放临时segment.
17、Process Monitor (PMON)
Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarts dead dispatchers
Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarts dead dispatchers
18、Checkpoint (CKPT)
Responsible for:
• Signalling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
Responsible for:
• Signalling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
19、Archiver (ARCn)
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database
20、Logical Structure
The logical structure of the Oracle architecture dictates how the physical space of a database is to be used.
A hierarchy exists in this structure that consists of tablespaces, segments, extents, and blocks.
The logical structure of the Oracle architecture dictates how the physical space of a database is to be used.
A hierarchy exists in this structure that consists of tablespaces, segments, extents, and blocks.
Logical Structure
A logical structure hierarchy exists as follows:
• An Oracle database is a group of tablespaces.
• A tablespace may consist of one or more segments.
• A segment is made up of extents.
• An extent is made up of logical blocks.
• A block is the smallest unit for read and write operations.
A logical structure hierarchy exists as follows:
• An Oracle database is a group of tablespaces.
• A tablespace may consist of one or more segments.
• A segment is made up of extents.
• An extent is made up of logical blocks.
• A block is the smallest unit for read and write operations.
逻辑结构存在这样的层级关系,一个database是一组tablespace,一个tablespace可以包含一个或者多个segment,一个segment是由多个extent组成,一个extent是由逻辑block组成,block是最小的读写操作的单位.
21 Processing a SQL Statement
• Connect to an instance using:
– The user process
– The 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.
• Connect to an instance using:
– The user process
– The 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 a DML Statement
A data manipulation language (DML) statement requires only two phases of processing,
1. Parse is the same as the parse phase used for processing a query.
2. Execute requires additional processing to make data changes. DML Execute Phase To execute a DML statement:
1. If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache.
2. The server process places locks on the rows that are to be modified. The rollback block is used to store the before-image of the data, so that the DML statements can be rolled back if necessary.
3. The data blocks record the new values of the data.
A data manipulation language (DML) statement requires only two phases of processing,
1. Parse is the same as the parse phase used for processing a query.
2. Execute requires additional processing to make data changes. DML Execute Phase To execute a DML statement:
1. If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache.
2. The server process places locks on the rows that are to be modified. The rollback block is used to store the before-image of the data, so that the DML statements can be rolled back if necessary.
3. The data blocks record the new values of the data.
4.The server process records the before image to the undo block and updates the data block. Both of these changes are done in the database buffer cache. Any changed blocks in the buffer cache are marked as dirty buffers. That is, buffers that are not the same as the corresponding blocks on the disk. The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image
of an INSERT contains the row location information.
of an INSERT contains the row location information.
SQL语句的执行过程见上面的简介
一个DML语句在oracle中的执行过程大体有以下几个过程.
1.分析阶段,和查询语句的分析过程是一样的.
2.运行其他的process实现数据更改.运行过程中下面这样的几个阶段.
如果数据和roll back block都还不在buffer cache中,oracle先把这些数据从数据文件中读到buffer cache中.
系统进程在将要被更改的数据行上加锁,roll back bock用于存放数据更改前的影像,因此DML语句在必要的时候可以回滚.
data block记录新的数据.
系统进程把数据更改前的影像记录到undo block中并更新data block.所有的这些更改都是在database buffer cache中进行的.所有在buffer cache被更改的block都被标记为dirty buffer.就是说,这些buffer已经和硬盘上的相应数据已经不同了.delete和insert命令使用的是相同的步骤.delete之前的影像记录被删除的行,insert之前的影像记录行的地址信息.