chapter01 oracle体系结构

1. objectives

After completing the lesson, you should be able to do the following:

a.    Outline the oracle architecture and its main components

b.    Lists the structures involved in connecting a user to an oracle instance

 

2. overview of primary components

oracle server = instance + database

instance = SGA + backgound process

database = datafile + controlfile+online redo log file

当然，含有一些其他的组件：password file、parameter file、archive log file、server process、user process

 

3. oracle server

An oracle server:

a. is a database management system that provides an open, comprehensive, integrated approach to information management

b. consists of an oracle instance and an oracle database

 

4. oracle instance

an oracle instance:

a. is a means to access an oracle database

b. always opens one and only one database

c. consists of memory and backgound process structures

 

5. connection and session

connecting to an oracle instance:

a. establishing a TCP/IP connection

b. creating a session

 

6. oracle database

an oracle database:

a. is a collection of data that is treated as a unit

b. consists of three file types (数据文件、控制文件、联机重做日志文件)

 

7. physical structure of oracle database

the physical structure includes three types of files:

a. datafile

b. controlfile

c. online redo log file

 

8. memory structure

oracle's memory structure consists of two memory areas known as:

a. System Global Area (SGA): allocated at instance startup, and is a fundamental component of an oracle instance

b. Program Global Area (PGA): allocated when the server process is started

 

9. SGA (System Global Area)

a. the SGA consists of several memory structures:

  -- shared pool

  -- database buffer cache

  -- redo log buffer

b. There are two additional memory structures that can be configured within the SGA:

  -- large pool

  -- java pool

查看SGA的大小：

SQL> show sga

Total System Global Area  629145600 bytes
Fixed Size                  1220964 bytes
Variable Size             176164508 bytes
Database Buffers          448790528 bytes
Redo Buffers                2969600 bytes

c. SGA is dynamic

d. SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

查看SGA各个部件的信息：

SQL> select component,current_size,last_oper_type,last_oper_time,granule_size from v$sga_dynamic_components;

COMPONENT                      CURRENT_SIZE LAST_O LAST_OPER_TIME      GRANULE_SIZE
------------------------------ ------------ ------ ------------------- ------------
shared pool                       167772160 GROW   2014-05-24 10:20:51      4194304
large pool                          4194304 GROW   2014-05-24 10:19:43      4194304
java pool                           4194304 STATIC                          4194304
streams pool                              0 STATIC                          4194304
DEFAULT buffer cache              448790528 SHRINK 2014-05-24 10:20:51      4194304
KEEP buffer cache                         0 STATIC                          4194304
RECYCLE buffer cache                      0 STATIC                          4194304
DEFAULT 2K buffer cache                   0 STATIC                          4194304
DEFAULT 4K buffer cache                   0 STATIC                          4194304
DEFAULT 8K buffer cache                   0 STATIC                          4194304
DEFAULT 16K buffer cache                  0 STATIC                          4194304
DEFAULT 32K buffer cache                  0 STATIC                          4194304
ASM Buffer Cache                          0 STATIC                          4194304

10. shared pool

a. used to store:

  -- most recently executed sql statements

  -- most recently used data dictionarys

b. it consists of two key performance-related memory structures:

  -- library cache

  -- data dictionary cache

c. sized by the parameter SHARED_POOL_SIZE

SQL> alter system set shared_pool_size=200m;

System altered.

 

11. library cache

a. stores information about the most recently used sql and pl/sql statements

b. enables the sharing of commonly used statements

c. is managed by a least Recently Used (LRU) algorithm

d. consists of two structures:

  -- shared sql area

  -- shared pl/sql area

e. size determined by the shared pool size

 

12. data dictionary cache

a. a collection of the most recently used definitions in the database

b. includes information about database files, tables, indexes, columns, users, privilege, and other database objects

c. during the parse phase, the server process looks at the data dictionary cache for information to resolve object names and validate access

d. caching data dictionary information into memory improves response time on queries and DML

e. size determined by the shared pool size

 

13. database buffer cache

a. stores copies of data blocks that have been retrieved from the data files

b. enables greate performance gains when obtain and update data

c. managed through a LRU algorithm

d. DB_BLOCK_SIZE determines the block size of default buffer cache

e. consists of independent subcaches:

  -- DEFAULT buffer cache (DB_CACHE_SIZE)

  -- KEEP buffer cache (DB_KEEP_CACHE_SIZE)

  -- RECYCLE buffer cache (DB_RECYCLE_CACHE_SIZE)

  ...

f. can be dynamically resized by:

  sql> ALTER SYSTEM SET ...

g. DB_CACHE_ADVICE set to gather statistics for predicting different cache size behavior

h. statistics displayed by V$DB_CACHE_ADVICE

 

14. redo log buffer

a. records all changes mode to the database data blocks

b. primary purpose is rcovery

c. changes recorded within are called redo entries

d. redo entries contain information to reconstruct or redo changes

e. size defined by LOG_BUFFER

 

15. large pool

a. an optional memory area in the SGA

b. relieves the burden placed on the shared pool

c. used for:

  -- session memory for the shared server

  -- I/O server processes

  -- backup and restore operations for RMAN

d. does not use a LRU list

e. sized by LARGE_POOL_SIZE

f. can be dynamically resized

 

16. java pool

a. services parsing requirements for java commands

b. required if installing and using java

c. sized by JAVA_POOL_SIZE

 

17. PGA (Program Global Area)

a. memory reserved for each user process connecting to an oracle database

b. allocated when a process is created

c. deallocated when the process is terminated

d. used by only one process

 

 18. process structure

oracle takes advantage of various types of processes:

a. user process

  -- started at the time a database user requests connection to the oracle server

b. server process

  -- a user connects to the oracle instance and is started when the user establishes a session

c. backgound process

  -- started when an oracle instance is started

 

19. user process

a. a program that requests interaction with the oracle server

b. must first establish a connection

c. does not interact directly with the oracle server(由oracle的server process负责处理客户端的请求，并把结果返回给客户端（server process）)

 

20. server process

a. a program that directly interacts with the oracle server

b. fulfills calls generated and returns results

c. can  be dedicated or shared

 

21. background process

maintains and enforces relationships between physical and memory structures:

a. mandatory background processes:

DBWn、LGWR、CKPT、PMON、SMON

b. optional background process

ARCn、CJQ0 ...

 

22. DBWn (database writer process)

数据库写进程，负责将database buffer cache中的脏数据写回数据文件。

DBWn writes when:

a. checkpoint occurs

b. dirty buffers reach threshold

c. there are no free buffers

d. timeout occurs

e. RAC ping request is made

f. tablespace offline、tablespace read only、tablespace begin backup(表空间热备)

 

23. LGWR (log writer process)

日志写进程，负责将redo log buffer中的redo entry写到online redo log file

LGWR writes:

a. at commit

b. when one-third full

c. when there is 1MB of redo

d. every three seconds

f. before DBWn writes

 

24. SMON (system monitor process)

系统监控进程

responsibilities:

a. instance recovery

  -- rolls forward changes in online redo log files

  -- open database for user access

  -- rolls back uncommitted transactions

b. coalesces free space

c. deallocates temporay segments

 

25. PMON (process monitor)

进程监控进程

cleans up after failed processes by:

a. rolling back the transaction

b. releasing locks

c. releasing other resources

d. restarting dead dispatchers

同时，PMON进程还有一个作用就是负责将实例注册给监听。下面是官方文档的解释：

Specify REGISTER to instruct the PMON background process to register the instance with the listeners immediately. If you do not specify this clause, then registration of the instance does not occur until the next time PMON executes the discovery routine. As a result, clients may not be able to access the services for as long as 60 seconds after the listener is started.

就是说，如果先启动数据库，后启动监听，PMON进程并不会立即将实例注册给监听，而是要等到60s之后，才会注册，此时为了提高数据库的高可用性，可以手工通知PMON

进程去注册实例。（我们这里说的是动态注册）

sql> ALTER SYSTEM REGISTER;

 

26. CKPT (checkpoint process)

检查点进程，又名保存点进程，主要目的就是缩短实例恢复的时间。

responsible for:

a. signaling DWBn at checkponts

b. update datafile headers with checkpont information

c. update control files with checkpoint information

 

27. ARCn (archiver process)

a. optional background process (可选的后台进程，但又是实际生产库中必须的进程，负责归档online redo log file)

b. automatically archives online redo log files when archivelog mode is set

c. preserves the record of all changes made to the database

 

28. logical structure of database

database本质就是存放在磁盘上的一堆文件（数据文件，控制文件，联机重做文件,...）

database的逻辑结构：

一个database由若干个表空间构成，

而一个表空间由若干个segment构成，

而一个segment又是由若干个extent构成，

一个extent是由一组连续的oracle data block构成，

一个oracle data block是由一组os block构成。

需要说明的是：oracle data block 是oracle进行I/O的基本单位，而extent则是空间分配的基本单位。

 

查看oracle 各个表空间(permanent、undo)的大小：

sql> select t.tablespace_name,sum(t.bytes/1024/1024) m from dba_data_files t
group by t.tablespace_name;

TABLESPACE_NAME                         M
------------------------------ ----------
SYSAUX                                200
UNDOTBS                               500
USERS                                 300
SYSTEM                                300
LXTBS                                  50

 

查看oracle各个临时表空间(temporary)的大小：

sql> select t.tablespace_name,sum(t.bytes/1024/1024) m
from dba_temp_files t
group by t.tablespace_name;

TABLESPACE_NAME                         M
------------------------------ ----------
TEMPTBS                               100

 

查看数据库已使用空间大小:

SQL> select sum(t.bytes/1024/1024) "used" from dba_extents t;

      used
----------
       435

 

查看数据库剩余空间大小：

SQL> select round(sum(t.bytes/1024/1024)) "free" from dba_free_space t;

      free
----------
       915