SQL之結束篇

處理一個查詢語句的三個階段以及相關後臺程式的簡單介紹[@more@]

sql之28 sql的執行

處理一個查詢語句的三個階段

第一階段：Parse:

Search for identical statement in the shared pool

Check syntax, object names, and privileges

Lock objects used during parse

Create and store execution plan

第二階段：Execute: Identify rows selected

第三階段：Fetch: Return rows to user process

對shared pool的理解

The shared pool has two primary components：Library cache and Data dictionary cache

在shared pool中主要有兩種區域：一是library cache 二是data dictionary cache

對library cache的解釋

The library cache stores information about the most recently used SQL statements in a memory structure called a

shared SQL area. The shared SQL area contains:

The text of the SQL statement

The parse tree: A compiled version of the statement

The execution plan: The steps to be taken when executing the statement

The optimizer is the function in the Oracle Server that determines the optimal execution plan.

在library cache中儲存的有SQL語句、解析路徑、執行計劃

If a SQL statement is reexecuted and a shared SQL area already contains the execution plan for the statement, the server process does not need to parse the statement. The library cache improves the performance of applications that reuse SQL statements by reducing parse time and memory requirements. If the SQL statement is not reused, it is eventually aged out of the library cache.

如果重複執行一個SQL語句，在library cache中儲存的仍有這個SQL，ORACLE不需要再解析，library cache由此提高效能。如果library cache中的SQL語句得不到重用，它就會最後老去。

對data dictionary cache的解釋

The data dictionary cache, also known as the dictionary cache or row 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 for the information in the dictionary cache to resolve the object names specified in the SQL statement and to validate the access privileges. If necessary, the server process initiates the loading of this information from the data files.

在data dictionary cache中儲存的有資料庫物件的定義及物件的許可權資訊，在SQL解析階段，伺服器程式在資料字典快取中查詢物件名稱，以確認物件存在，及相應的許可權存在。如果需要，伺服器程式開始從資料檔案中載入這些資料。

對db buffer cache的理解

When a query is processed, the server process looks in the database buffer cache for any blocks it needs. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the buffer cache. Because subsequent requests for the same block may find the block in memory, the requests may not require physical reads. The Oracle Server uses a least recently used algorithm to age out buffers

that have not been accessed recently to make room for new blocks in the buffer cache.

當一個查詢執行時，oracle從db buffer cache中查詢它需要的塊，如果沒有找到所需要的塊，它從資料檔案中讀取，並把讀取的塊在db buffer cache中放一個copy，此時發生物理讀。Oracle使用最少最近使用演算法來老化不使用的塊，以使新塊能放入buffer cache中。

對PGA的理解

In a dedicated server configuration, the PGA of the server 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

The PGA is allocated when a process is created and deallocated when the process is terminated.

在常規的專用伺服器的配置下，PGA包括以下內容：

Sort area排序區、session的資訊如使用者許可權及效能統計資訊、cursor說明、stack space包含其它session變數。

PGA在一個process建立時分配，在這個process終止時釋放

DML Execute Phase

To execute a DML statement:

• If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache.

• The server process places locks on the rows that are to be modified.

• In the redo log buffer, the server process records the changes to be made to the rollback and data.

• The rollback block changes record the values of the data before it is modified. The rollback block is used to store the before image of the data, so that the DML statements can be rolled back if necessary.

• The data blocks changes record the new values of the data.

執行一個DML語句時，如果資料和回退塊不在db buffer cache中，伺服器要從資料檔案讀入到db buffer cache中；

鎖定那些修改的行；

伺服器記錄對回退段及資料段的改變，都記錄到redo log buffer中；

前映像記錄在回退段中，用以回滾操作；

新內容記錄在資料塊中；回退塊和資料塊都在db buffer cache中。

The server process records the before image to the rollback block and updates the data block. Both of these changes are done in the database buffer cache.

Any changed blocks in the buffer cache are marked as dirty buffers: that is, buffers that are not the same as the corresponding blocks on the disk.

The processing of a DELETE or INSERT command uses similar steps. The before image for a

DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.

Because the changes made to the blocks are only recorded in memory structures and are not written immediately to disk, a computer failure that causes the loss of the SGA can also lose these changes.

在這個過程中，產生SGA中的資料塊與資料檔案中的塊不一致。

在delete和insert過程中，前映像選擇比update更簡單的儲存方式。

Redo Log Buffer

• Has its size defined by LOG_BUFFER

• Records changes made through the instance

• Is used sequentially

• Is a circular buffer

COMMIT Processing與系統改變號

System Change Number

Whenever a transaction commits, the Oracle Server assigns a commit system change number (SCN) to the transaction. The SCN is monotonically incremented and is unique within the database. It is used by the Oracle Server as an internal time stamp to synchronize data and to provide read consistency when data is retrieved from the data files. Using the SCN enables the Oracle Server to perform consistency checks without depending on the date and time of the operating system.

每一次commit，都會產生一個SCN，SCN作為一個時間戳用來同步資料，以及當從資料檔案讀取資料時保證讀一致性。SCN使資料庫能夠一致性檢查而不依賴作業系統的日期時間。

Steps in Processing COMMITs

When a COMMIT is issued, the following steps are performed:

• The server process places a commit record, along with the SCN, in the redo log buffer.

• LGWR performs a contiguous write of all the redo log buffer entries up to and including

the commit record to the redo log files. After this point, the Oracle Server can guarantee that the changes will not be lost even if there is an instance failure.

• The user is informed that the COMMIT is complete.

• The server process records information to indicate that the transaction is complete and that

resource locks can be released.

Flushing of the dirty buffers to the data file is performed independently by DBW0 and can occur either before or after the commit.

提交以後，LGWR會將重做快取區的所有條目（可能包括未發commit的記錄）寫入重做日誌檔案中。寫完之後，資料庫能保證即使例項失敗也不會丟失資料。之後，通知使用者提交完成。伺服器釋放所持有的鎖。提交不會造成立即寫資料檔案。

LGWR程式

1 LGWR何時寫日誌檔案？

• When a transaction commits

• When the redo log buffer is one-third full

• When there is more than a megabyte of changes recorded in the redo log buffer

• Before DBW0 writes modified blocks in the database buffer cache to the data files

2 一個例項最少需要哪幾個後臺程式？

LGWR SMON PMON DBW0 CKPT 五個後臺程式

DBW0程式

1 DBW0何時寫資料檔案？

• The number of dirty buffers reaches a threshold value

• A process scans a specified number of blocks when scanning for free buffers and cannot

find any

• A timeout occurs (every three seconds)

• A checkpoint occurs (A checkpoint is a means of synchronizing the database buffer cache with the data file.)

SMON程式

1 SMON有哪些功能？

在例項失敗時的自動恢復，前滾已提交寫入到日誌檔案但還沒寫入資料檔案的內容；回退未提交的事務；管理合併資料檔案的空閒空間；釋入臨時表空間；

PMON程式

1 PMON有哪些功能？

在process失敗時的清理工作：

• Rolling back the user’s current transaction

• Releasing all currently held table or row locks

• Freeing other resources currently reserved by the user

常見問題

1 oracle包含幾種型別的檔案？

• Control files

• Data files

• Online redo logs

其它三種型別的檔案有：

• Parameter file:

• Password file:

• Archived redo logs:

2 SGA有哪三大塊內容？

Share pool

Database buffer cache

Redo log buffer