oracle study notes(一)——Oracle architectural components

Oracle architectural components

一、.Overview of Primary Components

The Oracle architecture includes a number of primary components,as listed:

a.Oracle server

       includes several files，processes,and memory structures.The Oracle server consists of an Oracle instance and an Oracle database.

 

b.Oracle instance

       includes background processes,memory structures.such as SGA includes Shared pool(library cache,data dictionary cache),database buffer cache,redo log buffer cache. Background includes PMON,SMON,DRWR,LGWR,CKPT,OTHERS and so on.

       is a means to access an Oracle database

       always opens one and only one database

       consists of memory and process structures

       Memory Structures——SGA:Shared pool(library cache,data dictionary chache),database buffer cache,redo log buffer cache

       Background structures:PMON,SMON,DBWR,LGWR,CKPT,Others

       establishing a connection and creating a session

 

c.Oracle Database

       includes operating system files,also known as database files.

       is a collection of data that is treated as a unit

       consists of three file types:

       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

 

d.Other keys files

       configure the instance,anthenticate privileged users, and recover the database in the event of a disk failure.

       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.

e.User and server process

       execute a SQL statement.

f.Other process

       Many other processes,other options within Oracle,such as Advanced Queuing,Real Application Clusters,Shared Server,Advanced Replication and so on.

 

二、physical structure

The physical structure of an Oracle database is determined by the operating system files that provide the actual physical storage for database information

       control files

       data files

       redo log files

三、Memory Structure

System Global Area(SGA)

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

1.System Global Area(SGA):Allocated at instance startup,and is a fundamental component of an Oracle instance

shared pool

redo log buffer

database buffer cache

other structures(e.g.lock and latch management,statistical data)

There are two optional memory structures that can be configured within the SGA

       Large pool

       Java pool

2.Program Global Area(PGA):Allocated when the server process is started

example:

SQL> show sga;

Total System Global Area  118255568 bytes

Fixed Size                   282576 bytes

Variable Size              83886080 bytes

Database Buffers           33554432 bytes

Redo Buffers                 532480 bytes

SQL>              532480 bytes

 

四、Share Pools

The shared pool is used to store the most recently executed SQL statements and the most recently used data definitions.

1.It consists of two key performance-related memory structures:

       Library cache

       Data dictionary cache

2.Size by the parameter: SHARED_POOL_SIZE.

       alter system set shared_pool_size=64M;

the shared pool environment contains both fixed and variable structures.

             

the library cache stores information about the most recently used SQL and PL/SQL statements.

the library cache:

       Enables the sharing of commonly used (LRU).

       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 sizeing

 

 

五、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 an 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

----DB_KEEP_CACHE_SIZE

----DB_RECYCLE_CACHE_SIZE

 

Database buffer cache can be dynamically resized to grow or shrink using ALTER SYSTEM.

ALTER SYSTEM SET DB_CACHE_SIZE = 96M;

 

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

 

 

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

INITIALIZATION PARAMETER : LOG_BUFFER

 

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

 

八、Java Pool (optional)

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.

In Oracle9i,the default size of the java Pool is 24M.

 

 

Program Global Area (PGA)

九、Program global area

The SGA is memory reserved for each user process that connects to an Oracle database.

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

       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

Some of these structures are stored in the SGA when using shared server configuration .If using a shared server configuration .It is possible for multiple user processes to share server processes. If a large pool is created , the structures are stored in the large pool ; otherwise ,they are stored in the shared pool.

 

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

Oralce takes advantage of various types of processes:

1.       User process:Started at the time a database user requests connection to the Oracle server

2.       Server process:Connects to the Oracle Instannce and is started when a user establishes a session.

3.       Background process:Available when an Oracle instance is started.

User Process

A user process is a program that requests interaction with the Oralce server.

It must first establish a connection.(UPI)

It does not interact directly with the Oracle server.

Server Process

A Server process is a program that directly interacts with the Oracle server

It fulfills calls generated and returns results.

Can be dedicated or shared server.

Background Processes

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

 

Database writer(DBWn)

DBWn writes when

       Checkpoint

       Dirty buffers threshold reached

       No free buffers

       Timeout

       RAC ping request

       Tablespace read only

       Table Drop or Truncate

       Talbespace begin backup

Log Writer(LGWR)

LGWR writes:

At commit

When one-third full

When there is 1MB of redo

Every 3 seconds

Before DBWn writes

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

Process monitor (PMON)

Cleans up after failed processes by :

       Rollling back the transaction

       Releasing locks

       Releasing other resources

       Restarts dead dispatchers

Checkpoint (CKPT)

Responsible for :

       Signaling DBWn at checkpoints

       Updating datafile headers with checkpoint information

       Updating control files with checkpoint information

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

 

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.

Logical structure

A logical structure hierarchy exists as follows:

An oracle database is a group of tablespace.

A tablespace may consist of one 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.

 

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.