2008.12.23Process ＆Logical Structure&SQL 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 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.

Unix－－n process

Win -- 1 process n threads

 

■User　Process (Oracle客户端程序,如sqlplus,Toad)

A program that requests interaction with the Oracle server

Must first establish a connection

Does not interact directly with the Oracle server

■Server Process

A program that directly interacts with the Oracle server

Fulfills calls generated and returns results

Can be dedicated or shared server

$sqlplus /nolog

SQL>conn /as sysdba

Connected.

  IPC&Tcp/IP

IPC:Inter Process Communication,包括共享内存(SGA),队列,信号量等几种形式.

$/sbin/ifconfig

 eth0--

 lo---(Local Loopback) inet addr:127.0.0.1

■Background Process (ora_XXXX_SID)

Maintains and enforces relationships between physical and memory structures;

・Mandatory background processes:

   DBWn(0-20) PMON CKPT LGWR SMON

・Optiona background processes:

   ARCn LMDn QMNn CJQ0 LMON RECO ...

　●DBWｎ（0-20）Database Writer

  Database Buffer Cache →DBWn →DataFiles

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

  ●LGWR（Log Writer）

  Redo Log Buffer →LGWR　→RedoLogFiles

LGWR　writes when:

  At commit

  When one-third full

  When there is 1MB of redo

  Every three seconds

  Before DBWn writes

  ●SMON（System Monitor）清洁工

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

 ●PMON（Process Monitor） 监视是否正常工作..

Cleans up after failed processes by :

 Rolling back the transaction

 Releasing locks

 Releasing other resouces

 Restarting dead dispatchers

 ●CKPT（Checkpoint） 助工打杂

Responsibilities:

　Signaling DBWn at checkpints

  Updating datafile headers with checkpoint information

  Updating control files with checkpoint information

 ●ARCｎ

Redo log files →ARCn→ Arhived redo log files

Optional background process

Automatically archives online redo log files when ARCHIVELOG mode is set

Preserves the record of all changes made to the database

 

Logical Structure

Database ←ｎTablespace ←ｎSegment ←ｎExtent ←ｎBlocks（典型8k）  

・Dictates how the physical space of a database is used

・Hierarchy consisting of tablespaces ,segments,extents,and blocks

 

Processing SQL Statement

linking check→Parsing→Binding

Connect to an instance using :

  --User proces

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