oracle 筆記

next_junction發表於2014-01-18
Oracle筆記
1：An Oracle server: Is a database management system that provides an open,comprehensive,integrated approach to information management. Consists of an Oracle instance and an Oracle database.

2：An Oracle instance: Is a means to access an Oracle database.Always opens one and only one database. Consists of memory(memory structures) and background process structures.

3：Connection and Session:
user process, server process,background process

4：An Oracle database: Is a collection of data that is treated as a unit. Consists of three file types(Data files,Control files,Redo Log files. Parameter file,Password file,Archived Log files).

$ps -ef --檢視程式
$sqlplus /nolog --以nolog連線到sqlpus
sql> conn / as sysdba --以sysdba連線到db
sql> startup --啟動instance

Oracle's memory structure consits of two memory areas known as: System Global Area(SGA):Allocated at instance start up,and is a fundamental component of an Oracle instance. Program Global Area(PGA):Allocated when the server process is started.（一個instance只有一個SGA，但是每個server process都對應一個PGA，有幾個server process 就有幾個 PGA）

$ipcs --檢視記憶體狀態（SGA在linux和unix中的實現方式就是共享記憶體，是一個大的記憶體塊）

The SGA consists of serveral memory structures:Shared Pool，Database Buffer Cache，Redo Log Buffer
Other structures(for example,lock and latch menagement,statistical data)
There are two additional memory structures that can be configured within the SGA:Large Pool，Java Pool

$ps -ef | grep oracle --檢視oracle程式
sql>show sga --檢視sga（9i以後sga可以線上調整）

SGA: Is dynamic. Sized by the SGA_MAX_SIZE parameter. Allocated and tracked in granules(granules是sga分配記憶體的基本單位) by SGA.
Contiguous virtual memory allocation.Granule size based on total estimated SGA_MAX_SIZE.(sga小於128mb時候granules是4mb，大於128mb時候是16mb)

sql>select componect,granule_size from v$sga_dynamic_components;--檢視sga的組成部分和對應的granules。

sga有一個總的最大的尺寸，裡面的每個部件又有各自的尺寸。
1.DB_CACHE_SIZE
2.LOG_BUFFER
3.SHARED_POOL_SIZE
4.LARGE_POOL_SIZE
5.JAVA_POOL_SIZE

1:Shared Pool:
Used to store:Most recently executed SQL statements.Most recently used data definitions.
It consists of two key performance-related menory structures:Library Cache.Data Dictionary Cache.
Sized by the parameter:shared_pool_size(alter system set shared_pool_size=64m--這個語句改變其大小)
(1.1)Library Cache:Stores information about the most recently used SQL and PL/SQL statements.Enables the sharing of commonly used statements.Is managed by a least recently used(LRU) algorithm.Consists of two structures:Shared SQL area and Shared PL/SQL area. Size determined by the Shared Pool sizing.
(1.2)Data Dictionary Cache:A collection of the most recently used definitions in the database. 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 data dictionary information into memory improves respinse time on queries and DML.Size determined by the Shared Pool sizing.

2:Database Buffer Cache:
Stores copies of data blocks the have been retrieved from the data files. Enables great performance gains when you obtain and updata data. Managed through an LRU algorithm. DB_BLOCK_SIZE determines primary block size.
Consists of independent subcaches:DB_CACHE_SIZE,DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZE. Can be dynamically resized(alter system set DB_CACHE_SIZE=96M).DB_CACHE_ADVICE set to gather statistics for predicting different cache size bebavior.statistics displayed by V$DB_CACHE_ADVICE.

3:Redo Log Buffer:
Records all changes made to the database data blocks. Primary purpose is recovery. Changes recorded within are called redo entries(每次做改變都會在redo log中記錄，這個記錄稱為redo entries，一條redo entries就可以恢復一次對DB的改變). Redo entries contain information to reconstruct or redo changes. Size defined by LOG_BUFFER.

4：Large Pool：
An optional area of memory in the SGA. Relieves the burden placed on the Shared Pool.Used for:Session memory(UGA) for the Shared Server,I/O server processes,Backup and restore operations or RMAN,Parallel execution message buffers(PARALLEL_AUTOMATIC_TUNING set to TRUE). Does not use an LRU list. Sized by LARGE_POOL_SIZE. Can be dynamically resized.

5:Java Pool:
Services parsing requirements for Java commands.Required if installing and using Java. Sized by JAVA_POOL_SIZE parameter.

PGA:Program Global Area:
Memory reserved for each user process connection to an Oracle database. Allocated when a process is created. Deallocated when the process is terminated. Used by only one process.

Process Structure:
Oracle 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 instance and is tarted when a user establishes a session.
(3)Background processes:Started when an Oracle instance is started.
(1):User Process(客戶端工具：sqlplus,toad,sql developer等),A program that requests interaction with the Oracle server. Must first establish a connection. Does not interact directly with the Oracle server.(過程：[Database user]-User process-{connection established}-Server process)
(2):Server Process,A program that directly interacts with the Oracle server. Fulfills calls generated and returns results. Can be dedicated or shared server.(過程：[Database user]-User process-{connection established}-Server process-{Session created}-[Oracle server]).
--》關於server process，也就是伺服器程式，可以這樣講：oracle對用於使用者發出sql請求如果通過後臺程式去處理，那麼當使用者量很大，sql請求很多時候，oracle得需要同樣多的後臺程式來處理，這種情況會導致oracle不可能有那麼多的後臺程式來完成請求處理，只有通過一個伺服器程式也就是server process來對應一個使用者請求，這樣就可以面對大量使用者請求處理而輕鬆對付--（dba的思想天空）).
(2.1):IPC AND TCP/IP(IPC:Inter Process Communication,包括共享記憶體，佇列，訊號量等幾種形式),兩臺不同的機器程式進行通訊，走網路卡（eht0）的話一定是用TCP/IP協議進行通訊。如果同一臺機器的程式可以走IPC協議進行通訊，也可以通過lo(local loopback)走模擬的TCP/IP協議進行通訊。
(3):Background Process($ps -ef | grep oracle--檢視oracle的後臺程式),Maintains and enforces relationships between physical and memory structures(可以有兩種形式的程式，必須有的，可選的):Mandatory background processes:DBWn,PMON,CKPT,LGWR,SMON.Optional background processes:ARCn,LMDn,QMNn,CJQ0,LMON,RECO,Dnnn,LMS,Snnn,LCKn,Pnnn.
(3.1):Database Writer(DBWn),DNWn writes when:Checkpoint occers,Drity buffers reach threshold,There are no free buffers,Timeout occers,RAC ping request is made,Tablespace OFFLINE,Tablespace READ ONLY,Table DROP OR TRUNCATE,Tablespace BEGIN BACKUP.
(3.2):Log Writer(LGWR)，LGWR writes：At commit,When one-third full,When there is 1 MB of redo,Every three seconds,Before DBWn writes.
(3.3):System Monitor(SMON),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.
(3.4):Process Monitor(PMON),Cleans up after failed processes by:Rolling back the transaction,Releasing locks,Releasing other resources,Restarting dead dispatchers.
(3.5):Checkpoint(CKPT,早期是lgwr負責此工作),Responsible for:Signaling DBWn at checkpoints,Updating datafile headers with checkpoint information,Updating control files with checkpoint information.
(3.6):Archiver(ARCn,生產庫必要模式),Optional background process，Automatically archives online redo log files when ARCHIVELOG mode is set,Preserves the record of all changes made to the database.
(4):Dictates how the physical space of a database is used.Hierarchy consisting of tablespaces,segments,extents,and blocks.
                    datafilesegment->extent->blocks
                                datafile
(5):Processing SQL Statements，Connect to an instance using:User process,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.
    oracle處理sql語句的過程：首先parssing（解析）,客戶端連線到資料庫，建立一個連線會話，發出一個sql語句到後臺，這之前還要做一個安全檢查（許可權等），解析成原子語句，然後繫結變數，執行語句，最後返回給客戶端。
總結：資料庫物理結構：資料檔案，控制檔案，聯機重做日誌檔案等
資料庫邏輯結構：表空間，段，擴充套件，邏輯快
SGA記憶體結構：資料緩衝區，共享池，日誌緩衝區
後臺程式：DBWn,LGWR,CKPT,PMON,SMON,ARCn
A connection is a communication pathway betwwn a user process and an Oracle server.一個使用者只會和資料庫instance的server process進行連線，並不會一個使用者去啟動一個instance，instance是資料庫管理員來啟動的。
A session starts when a user is validated by the Oracle server.
Connection:Communication between a user process and an instance.
Session:Specific connection of a user to an instance through a user process.
    {User--Sql>select ...--(user process--server process)--session}---&gt>()之間是一個connection，{}之間是一個session
Teh PGA is a memory region that contains data and control information for single(唯一) server process or multiple(多個) background processes.
Oracle的檔案型別：Control files,Data files Online redo log files,Parameter file,Backup files,Archived redo log files,Pawwsord file,Alert log and trace files.
----------------------------------------------------------------------------------------------------------------------------（待續1）
















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

相關文章