1Z0-031.1. 体系结构概述

[@more@]

总体结构：

- 软件结构

- 存储结构

存储结构：

- 逻辑存储结构：主要描述Oracle数据库的内部存储结构，即在技术概念上描述在Oracle数据库中如何组织、管理数据

- 物理存储结构：主要描述Oracle数据库的外部存储结构，即在操作系统中如何组织、管理数据

逻辑存储结构：

- 在技术概念上描述在Oracle数据库中如何组织、管理数据

- 通过查询Oracle数据库的数据字典，可以找到逻辑存储结构的描述

- 简单描述：多个数据块组成区，多个区组成段，多个段组成表空间，多个表空间组成逻辑数据库

物理存储结构：

- 现实的数据存储单元，对应于操作系统的文件

- 包括数据文件、控制文件、重做日志文件

- 跟Oracle数据库有关，但从技术上看不属于Oracle数据库的附件文件有密码文件（PWD.ORA）、参数文件（SPFILE.ORA）和归档日志文件等

软件结构：

- 例程结构（别称），包括进程结构和内存结构

- 在启动数据库时，oracle首先要在内存中获取、划分、保留各种用途的区域（表现一定的结构），运行各种用途的后台进程，即创建一个例程（instance），然后再由该例程装载（mount）、打开（open）数据库，最后由这个例程来访问和控制数据库的各种物理结构。

- 例程是用户和数据库之间的一个中间层，它是由操作系统的内存结构和一系列进程所组成的，可以启动和关闭。当用户连接到数据库并使用数据库时，实际上是连接到该数据库的例程，通过例程来连接、使用数据库。

内存结构：

- 分为SGA（System Global Area，系统全局区）和PGA（Program Glabal Area，程序全局区）

进程结构：

- User Process

- Programe Process

- Backgroud Process

Oracle Server:

Oracle Server = Oracle Instance + Oracle Database

-- Run ways:

1> Client-Application Server-Server

2> Client-Server

3> Host-Based

Oracle Instance:

Oracle Instance = Backgroud Process + SGA

- It is a means to access an Oracle database.

- Always opens one and only one database.

Oracle Database:

Oracle Database = Contrl Files + Redo Files + Data Files. --&gt Physical Structure

- Data files containing the actual data in the database.

- Redo logs containing a record of changes made to the database to enable recovery of the data in case of failures

- Control files containing information necessary to maintain and verify database integrity.

Other Key File Structures:

- The parameter file defines the characteristics of an Oracle instance.

- The password file authenticates users privileged to start up and shut down an Oracle instance.

- Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures

Memory Structure:

Memory Structure = SGA + PGA

SGA:

SGA = Database buffer cache + Redo log buffer + Shared pool + [Large pool + Java pool] + other structures (eg:lock and latch management,statistical data).--&gt[] means optional

Allocated at instance startup, and is a fundamental component of an Oracle Instance.

Sizing SGA:

-- SGA is dynamic and sized using SGA_MAX_SIZE.

-- SGA memory allocated and tracked in granules SGA components

-- 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

-- Target size in granules will be tracked and displayed by the V$BUFFER_POOL view

Dynamic SGA:

- A dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance.

- This then allows the sizes of the database buffer cache, shared pool, and large pool to be changed without shutting down the instance.

- Conceivably,the database buffer cache, shared pool, and large pool could be initially under configured and would grow and shrink depending upon their respective work loads, up to a maximum of SGA_MAX_SIZE.

- The components (buffer cache, shared pool, and large pool) are allowed to grow and shrink based on granule boundaries.

Shared pool:

- shared pool = Library cache + Data dictionary cache

- Size by the parameter SHARED_POOL_SIZE and cannot exceed SGA_MAX_SIZE

- change size : ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

- The shared pool environment contains both fixed and variable structures.

The fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements.

Library cache:

- Library cache = 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

- Shared SQL: The Shared SQL stores and shares the execution plan and parse tree for SQL statements run against the database.

The second time that an identical SQLstatement is run, it is able to take advantage of the parse information available in the shared SQL to expedite its execution.

To ensure that SQL statements use a shared SQL area whenever possible, the text, schema, and bind variables must be exactly the same.

- Shared PL/SQL: The shared PL/SQL area stores and shares the most recently executed PL/SQL statements.

Parsed and compiled program units and procedures (functions,packages, and triggers) are stored in this area.

Data dictionary cache:

- 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.

Database Buffer Cache:

Database Buffer Cache = DB_CACHE + DB_KEEP_CACHE + DB_RECYCLE_CACHE

- 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.

- Consists of independent sub-caches:

-- 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.

- Database buffer cache can be dynamically resized to grow or shrink using ALTER SYSTEM. (eg:ALTER SYSTEM SET DB_CACHE_SIZE = 96M;)

- The buffer cache advisory information is collected and displayed through the V$DB_CACHE_ADVICE view.

- DB_CACHE_ADVICE can be set to gather statistics for predicting different cache size behavior.

- DB_CACHE_ADVICE Parameter Values

- OFF: Advisory is turned off and the memory for the advisory is not allocated

- ON: Advisory is turned on and both cpu and memory overhead is incurred

Attempting to set the parameter to this state when it is in the OFF state may lead to ORA-4031. Inability to allocate from the shared pool when the parameter is switched to ON. If the parameter is in a READY state it can be set to ON without error since the memory is already allocated.

- READY: Advisory is turned off but the memory for the advisory remains allocated.

Allocatingthe memory before the advisory is actually turned on will avoid the risk of ORA-4031. If the parameter is switched to this state from OFF, it is possible than an ORA-4031 will be raised

- Multiple Block Sizes:

- An Oracle database can be created with a standard block size and up to four non-standardblock sizes.

- Non-standard block sizes can have any power-of-two value between 2 KB and 32 KB.

Redo Log Buffer Cache:

- Its primary purpose is recovery.

- Changes recorded within are called redo entries.

- Redo entries contain information to reconstruct or redo changes.

Redo entries contain the information necessary to recreate the data prior to the change made by INSERT, UPDATE, DELETE, CREATE,ALTER, or DROP operations.

- Size is defined by LOG_BUFFER.

Large Pool:

- 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.

- change size : ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;

PGA:

Allocated when the server process is started

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process.

The PGA is allocated when a process is created and deallocated when the process is terminated.

In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process.

In a dedicated server configuration, the PGA includes these components:

- Sort area: Used for any sorts that may be required to process the SQL statement

- Session information: Includes user privileges and performance statistics for the session,They are stored in the SGA (in share pool or in large pool if it is created) when using a shared server configuration.

- Cursor state: Indicates the stage in the processing of the SQL statements that are currently used by the session

- Stack space: Contains other session variables

Process Structure

- User process

- Server process

- Background process: Available when an Oracle instance is started

User Process:

- Started at the time a database user requests connection to the Oracle server.

- It must first establish a connection.

- It does not interact directly with the Oracle server.

Server Process

- Connects to the Oracle Instance and is started when a user establishes a session.

- It fulfills calls generated and returns results.

- Can be dedicated or shared server.

In a dedicated server environment, the server process handles the request of a single user process.Once a user process disconnects, the server process is terminated.

In a shared server environment, the server process handles the request of several user processes.

Background Processes:

- Mandatory background processes

DBWn PMON CKPT

LGWR SMON RECO

- Optional background processes

ARCn LMON Snnn

QMNn LMDn

CJQ0 Pnnn

LCKn Dnnn

Database Writer (DBWn):

Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files.

Database performance is improved because server processes make changes only in the 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 (decided by the parameter DB_BLOCK_WRITE_BATCH).

- A process scans a specified number of blocks when scanning for free buffers and cannot find any.

- Timeout occurs(3 SEC).

- 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

Log Writer (LGWR):

LGWR can also call on DBWn to write to the data files.

Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk.

occus:

- 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

System Monitor (SMON):

After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened.

Instance recovery consists of the following steps:

1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log.

This data has not been written to disk because of the loss of the SGA during instance failure.

During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks.

Because all committed transactions have been written to the redo logs, this process completely recovers these transactions.

2. Opening the database so that users can log on.

Any data that is not locked by unrecovered transactions is immediately available.

3. Rolling back uncommitted transactions.

They are rolled back by SMON or by the individual server processes as they access locked data.

SMON also performs some space maintenance functions:

- It combines, or coalesces, adjacent areas of free space in the data files.

- It deallocates temporary segments to return them as free space in data files.

Temporary segments are used to store data during SQL statement processing.

Process Monitor (PMON):

- Rolling back the user’s current transaction

- Releasing all currently held table or row locks

- Freeing other resources currently reserved by the user

- Restarts dead dispatchers

Checkpoint (CKPT):

An event called a checkpoint occurs when the Oracle background process DBWn writes all the modified database buffers in the SGA, including both committed and uncommitted data,to the data files.

Responsible for:

- Signalling DBWn at checkpoints

- Updating datafile headers with checkpoint information

- Updating control files with checkpoint information reasons:

- Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly.

Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify

as the least recently used block and thus might never be written to disk if checkpoints did not occur.

- Because all database changes up to the checkpoint have been recorded in the data files, redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required.

Therefore, checkpoints are useful because they can expedite instance recovery.

At a checkpoint, the following information is written:

- Checkpoint number into the data file headers.

- Checkpoint number, log sequence number, archived log names, and system change numbers into the control file.

CKPT does not write data blocks to disk or redo blocks to the online redo logs.

when occur:

--- at every log switch

--- when an instance has been shutdown with the normal,transaction,or immediate option.

--- when forced by setting the initialization parameter FAST_START_MTTR_TARGET

--- when manually requested by the database administrator

--- when the ALTER TABLESPACE [OFFLINE NORMAL | READ ONLY | BEGIN BACKUP] cause checkpointing on specific data files

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

Establishing a Connection and Creating a Session:

Database User : User Process -- Connection established --Oracle Server : Server Process (Create a Session)

Connection: a communication pathway between a user process and an Oracle server.

Sessions: a specific connection of a user to an Oracle server.

The session starts when the user is validated by the Oracle server, and it ends when the user logs out or when there is an abnormal termination.

Types of Connections:

1> Dedicated Server Connection : There is a one-to-one correspondence between a user and server process.

2> Shared Server Connection : It is possible for multiple user processes to share server processes.

Ways of Connections:

1> The user logs on to the operating system running the Oracle instance and starts an application or tool that accesses the database on that system.

The communication pathway is established using the interprocess communication mechanisms available on the host operating system.

2> The user starts the application or tool on a local computer and connects over a network to the computer running the Oracle instance.

In this configuration, called client-server, network software is used to communicate between the user and the Oracle server.

3> In a three-tiered connection, the user’s computer communicates over the network to an application or a network server, which is connected through a network to the machine running the Oracle instance.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8633028/viewspace-909533/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8633028/viewspace-909533/