oracle體系結構

bitifi發表於2015-09-22
Oracle

體系結構：

  整個oracle資料庫管理系統可分為：記憶體丶程式丶檔案。

 

         記憶體+後臺程式 = 例項。例項就相當於進入資料庫的入口，啟動資料庫先要啟動例項，即先給oracle分配記憶體然後啟動後臺程式並透過程式將記憶體和檔案連線起來，後臺程式執行資料庫的輸入輸出和監控其他oracle程式。

         記憶體分配：

·         SGA()：

存放sql語句的共享池 （shared_pool_size），

存放大輸入輸出及備份檔案的大型池 (large_pool_size)，

存放最近讀入或者寫入資料庫的資料資訊的資料庫快取記憶體區     (db_cache_size)，

存放java程式碼的JAVA池(java_pool_size)，

還有存放了在進行DML操作之前和之後的資料資訊的重做日誌快取區   (log_buffer)。

流池（stream_pool_size）(Oracle Streams enables information sharing, including data replication and message queuing, within or between databases.)
官方文件如下：

Component

Description

Database buffer cache

Before data stored in the database can be queried or modified, it must be read from a disk and stored

in the buffer cache. All user processes connected to the database share access to the buffer cache.

 For optimal performance, the buffer cache should be large enough to avoid frequent disk I/O

operations.

Shared pool            

 

The shared pool caches information that is shared among users:

·         SQL statements that can be reused

·         Information from the data dictionary such as user account data, table and index descriptions,

·          and privileges

·         Stored procedures, which are executable code that is stored in the database

Redo log buffer

This buffer improves performance by caching redo information until it can be written to the physical

online redo log files stored on disk. Redo information and online redo log files are discussed in

 .

Large pool

This optional area is used to buffer large I/O requests for various server processes.

Java pool

The Java pool is an area of memory that is used for all session-specific Java code and data within the

 Java Virtual Machine (JVM).

Streams pool

The Streams pool is an area of memory that is used by the Oracle Streams feature.

 For more information about Oracle Streams, see .

Result cache

The result cache buffers query results. If a query is run for which the results are stored in the result

cache, then the database returns the query results from the result cache instead of rerunning the query.

 This SGA component speeds the execution of frequently run queries.

 

 

·         PGA()：The PGA is used to process SQL statements and to hold logon and other session information. A large part of the PGA is dedicated to SQL work areas, which are working memory areas for sorts and other SQL operations.

         5主要的個後臺程式：系統監控程式（SMON），程式監控（PMON），資料庫寫程式（DBWR），日誌寫程式（LGWR），檢查點程式（CKPT）。

Background Process

Description

Database writer (DBWn)

The database writer writes modified blocks from the database buffer cache to the

files on a disk. Oracle Database allows a maximum of 36 database writer processes.

Log writer (LGWR)

The log writer process writes redo log entries to disk. Redo log entries are generated

in the redo log buffer of the System Global Area (SGA) and the log writer process writes

the redo log entries sequentially into an online redo log file.

Checkpoint (CKPT)

At specific times, all modified database buffers in the SGA are written to the data files

by a database writer process (DBWn). This event is called a checkpoint.

The checkpoint process signals DBWn, updates the data files and

control files of the database, and records the time of this update.

System monitor (SMON)

The system monitor performs instance recovery when a failed instance is restarted.

Process monitor (PMON)

The process monitor performs a recovery when a user process fails.

 It cleans up the cache and frees resources that the failed process was using.

Archiver (ARCn)

Archiver processes copy the online redo log files to archival storage

when the log files are full or a log switch occurs.

The database must be in archive log mode to run archive processes.

 For more information, see .

Manageability monitor (MMON)

This process performs various management-related background tasks, for example:

·         Issuing alerts whenever a given metric violates its threshold value

·         Taking snapshots by spawning additional processes

·         Capturing statistical values for SQL objects that have been recently modified

Job Queue Processes (CJQ0 and Jnnn)

Job queue processes run user jobs, often in batch mode.

 A job is a user-defined task scheduled to run one or more times.

ALTER SYSTEM SET pga_aggregate_target = 140 M;

ALTER SYSTEM SET sga_target = 472 M;

 

資料庫主要有三種檔案：

 

         儲存整個資料庫的結構資訊的控制檔案，儲存對資料庫操作資訊記錄資料庫變化的重做日誌檔案，整個資料庫最核心的儲存最終資料的資料檔案。

 

工作原理：

         首先啟動例項，即資料庫進入nomount狀態，這時就要根據系統的引數檔案給oracle分配記憶體並啟動後臺程式，然後裝載資料庫，進入mount狀態，這時開啟了就控制檔案，很多修改引數的操作只能在這個模式下進行，最後開啟資料庫進入open狀態，就可以對資料庫進行操作了。

         資料庫開啟之後就要進行使用者登陸，資料庫會根據口令檔案進行檢查，當登陸成功之後，oracle會建立一個使用者程式，所有的請求都會透過這些程式傳送給oracle伺服器。
         當使用者向伺服器發出請求時，比如執行一條DML語句，伺服器程式會將這條語句進行與ASCII值的等效轉化，再由HASH函式轉化為HASH值，這個HASH值可以唯一標識一條sql語句，oracle並不會直接解析語句，而是先再shared pool裡查詢是否存在相同的HASH值，如果存在，那麼就可以省去後面的解析等開銷，直接使用已經解析好的語句，並且這些資料塊都已經提前取出，就放在db buffer中，所以很快就可以出結果。如果沒有，那就只能將語句解析，再到資料檔案中找到相應的資料，這個過程會使用較大的開銷。這也是硬解析慢的原因。（如有錯誤，請留言。）


                                                                                                                                                                                                

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30496894/viewspace-1806057/，如需轉載，請註明出處，否則將追究法律責任。

相關文章