ORACLE資料庫體系框架介紹及SQL語句效能探討(轉)

ORACLE資料庫體系框架介紹及SQL語句效能探討(轉)[@more@]

　　“我們公司主要的業務是軟體開發和系統整合，而這兩個方面都會涉及到資料庫軟體，公司大多數的產品和工作都和ORACLE資料庫有關；因此，可以說我們宏智科技在ORACLE資料庫應用方面是高手雲集、藏龍臥虎!下面，我本著“魯班門前弄大斧”的動機和“成功就是把犯錯誤的速度提高一倍”的精神，戰戰兢兢地寫下了一些個人認為重要的觀點和一點點個人的體會，希望大家不要吝嗇給我成長的機會，多提意見。

　　摘要：基於資料庫的業務系統的核心是資料庫和資料。理解ORACLE資料的體系框架有助於我們成功開發基於資料庫的業務系統。透過增加一個索引、改變SQL語句的連線方法可以極大的改變系統的效能；80%的效能問題都是由不良的SQL語句引起的。

　　關鍵詞：資料庫、ORACLE、體系框架、SQL效能調整

　　　　　　　　　　　　　　　　　　應用系統最重要的部分：資料庫

　　一個基於資料庫的業務系統的成功開發，除了要求專案組對客戶的需求有深刻的理解、對開發工具有熟練的掌握並有卓越管理之外，還要求項組能明確知道應用程式如何使用資料庫。正如開發一個成功的業務系統對作業系統的理解非常重要一樣，開發一個成功的基於ORACLE資料庫應用的業務系統要求我們能全面的理解ORACLE。對於一個基於資料庫的應用系統來說，系統中最重要的部分是資料庫。記得我們公司CEO王棟在2000年時，在三明市地方稅務局對前來福建考察的國家稅務總局資訊官員講述《地方稅收徵管資訊系統》時說：“三分技術、七分管理、十二分的資料”，這不僅強調了資料的重要性，同時也體現了資料庫在一個基於資料庫的業務系統中的核心地位。一個成功的開發團隊必定是深刻認識這點並讓相關人員都認識這一點的團隊。

　　下面，首先介紹一下ORACLE資料庫體系結構，以便開發人員對ORACLE體系結構有一個基本的、整體的映像；其次是圍繞ORACLE資料庫的效能對ORACLE開發中SQL語句的使用進行一些探討。

　　　　　　　　　　　　　　　　　　　　理解ORACLE資料庫體系框架

　　資料庫軟體就是處理資料檔案的一批程式。關聯式資料庫自上世紀70年代I B M 聖約瑟研究實驗室的高階研究員埃德加·考特(E F Codd)的《大型共享資料庫資料的關係模型》一文發表世以來，就逐步成為了資料庫的主流。1977年，ORACLE公司成立第一個以關聯式資料庫為核心的軟體公司，現在已經推出ORACLE 9i。下面的探討主要以ORACLE8i版本為基礎。

　　雖然大家在很多介紹ORACLE的書籍中都可以看到類似下面的圖，但是我認為下面的這張圖是對ORACLE的體系結構展現的最清晰和簡明扼要的。也許你在看介紹ORACLE的書籍時對這些枯燥的理論介紹沒有太多的關心，而直接進入你關心的、可操作的內容。現在就讓我們一起對這個圖進行簡單的瞭解。

　　大家可以看到，如果從簡單的角度來描述，可以說一個ORACLE例項(Instance)是由一定的記憶體與後臺程式組成，而資料庫(Database)指物理檔案。下面就SGA、五個必須的ORACLE後臺程式進行簡單的介紹：

　　SGA(System Global Area 也稱 Shared Global Area) 主要由以下三部分組成：

　　共享池(Shared Pool) 主要用來儲存最近執行過的SQL語句和最近使用過的資料字典的資料；它主要透過INIT.ORA檔案中的shared_pool_size和shared_pool_reserved_size兩個引數來設定。

　　資料快取記憶體區(Data Buffer Cache) 主要用來儲存最近使用過的資料，可能是要寫到資料檔案的，也可能是從資料檔案讀取的；它主要透過INIT.ORA檔案中db_block_buffers引數來設定；Data Buffer的大小＝db_block_buffers* db_block_size;

　　重做日誌快取區(Redo Log Buffer) 主要儲存服務程式和後臺程式的變化資訊；它主要透過INIT.ORA檔案中的log_buffer引數來設定； Redo Log Buffer的大小＝log_buffer* db_block_size;

　　當然，SGA不僅僅只是上面的三部分，還包括如Java pool(用來儲存java程式碼)、Large pool(供不是和SQL直接相關的程式使用，如：當資料備份或恢復操作時，RMAN backup 用作磁碟I/O快取器；Parallel時用作訊息快取器；MTS回話記憶體)等部分，我們可以透過v$sysstat、v$rowcache、v$librarycache等系統檢視來監控SGA。

　　五個必須的ORACLE後臺程式SMON、PMON、DBWn、CKPT、LGWR

　　系統監控程式(System Monitor SMON) 在資料庫系統啟動時執行恢復工作的強制性程式

　　程式監控程式(Process Monitor PMON) 用於恢復失敗的資料庫使用者的強制性程式，它先獲取失敗使用者的標識，釋放該使用者佔有的所有資料庫資源。

　　資料庫寫入程式(Database Writer, DBWR) 它將修改後的資料塊寫回資料庫檔案。日誌寫入程式(Log Writer , LGWR) 一個專門用於將重做項寫入重做日誌的程式。

　　校驗點程式(Checkpoint Process CKPT ) ORACLE把記憶體中髒資料塊中的資訊寫回磁碟的判斷程式。

[myimg]upload/oracle9.png[/myimg]

(注：本圖引自ORACLE8i的OCP考試培訓官方資料)

　　　　　　　　　　　　　　　　　　　　　　SQL效能調整

　　我們考慮一個基於ORACLE資料庫的應用系統的效能時，優先要考慮什麼呢?主機作業系統？磁碟的I/O還是記憶體的使用？不，都不是！是系統的業務規則。從這個意義上說，我們對各個行業的業務專家的渴求不僅基於市場方面的，更是基於技術方面的。如果我們能夠最佳化客戶的業務規則，我們的系統將在起點上超越競爭對手！

　　在無法改變客戶的業務規則的情況下,我們考慮影響應用系統效能的先後順序應該是：

　　首先，考慮SQL語句的效能；

　　其次，考慮記憶體的分配；

　　第三，CPU的使用分配和磁碟I/O瓶頸；

　　第四，考慮網路因素;

　　第五，考慮作業系統因素;

　　等等……

　　由此可以說，一個基於資料庫的應用系統效能的好壞，首先是應用系統設計人員、應用系統開發人員的責任，而資料庫管理員（DBA）是在其基礎上進行的效能調整。80%的效能問題都是由不良的SQL語句引起的。設計和建立最佳的SQL對於系統的可擴充套件性和響應時間是基本工作。下面，我主要就SQL語句的效能進行一些粗淺的探討，希望能起到拋磚引玉的效果。

　　SQL語句效能調整的目標是：

　　去掉不必要的大表全表掃描 不必要的大表全表掃描會造成不必要的輸入輸出，而且還會拖垮整個資料庫；

　　檢查最佳化索引的使用 這對於提高查詢速度來說非常重要

　　檢查子查詢 考慮SQL子查詢是否可以用簡單連線的方式進行重新書寫；

　　調整PCTFREE和PCTUSED等儲存引數最佳化插入、更新或者刪除等操作；

　　考慮資料庫的最佳化器;

　　考慮資料表的全表掃描和在多個CPU的情況下考慮並行查詢;

　　一、 索引(INDEX)使用的問題

　　1． 索引(INDEX)，用還是不用？這是個的問題。

　　是全表掃描還是索引範圍掃描主要考慮SQL的查詢速度問題。這裡主要關心讀取的記錄的數目。根據DONALD K .BURLESON的說法,使用索引範圍掃描的原則是：

　　對於資料有原始排序的表，讀取少於表記錄數40%的查詢應該使用索引範圍掃描。對讀取多於表記錄數40%的查詢應全表掃描。

　　對於未排序的表，讀取少於表記錄數7%的查詢應該使用索引範圍掃描,反之，對讀取多於表記錄數7%的查詢應全表掃描。

　　注：在不同的書中，對是否使用索引的讀取記錄的百分比值不太一致，基本上是一個經驗值，但是讀取記錄的百分比越低，使用索引越有效。

　　2． 如果列上有建索引，什麼SQL查詢是有用索引(INDEX)的？什麼SQL查詢是沒有用索引(INDEX)的？

　　存在下面情況的SQL，不會用到索引：

　　存在資料型別隱形轉換的,如：

　　select * from staff_member where staff_id=’123’；

　　列上有數學運算的，如：

　　select * from staff_member where salary*2<10000;

　　使用不等於(<>)運算的，如：

　　select * from staff_member where dept_no<>2001;

　　使用substr字串函式的，如：

　　select * from staff_member where substr(last_name,1,4)=’FRED’;

　　‘%’萬用字元在第一個字元的，如:

　　select * from staff_member where first_name like ‘%DON’;

　　字串連線(||)的，如：

　　select * from staff_member where first_name||’’=’DONALD’

　　3． 函式的索引

　　日期型別也是很容易用到的，而且在SQL語句中會使用to_char函式以查詢具體的的範圍日期。如：select * from staff_member where TO_CHAR(birth_day,’YYYY’)=’2003’; 我們可以建立基於函式的索引如：CREATE INDEX Ind_emp_birth ON staff_member (to_char((birth_day,’YYYY’));

　　二、 SQL語句排序最佳化

　　1． 排序發生的情況：

　　SQL中包含group by 子句

　　SQL 中包含order by 子句

　　SQL 中包含 distinct 子句

　　SQL 中包含 minus 或 union操作

　　建立索引時

　　2． 排序在記憶體還是在磁碟中進行？

　　在記憶體執行的排序速度要比在磁碟執行的排序速度快14000倍。如果是專用連線，排序記憶體根據INIT.ORA的sort_area_size進行分配，如果是多執行緒服務連線，排序記憶體根據large_pool_size進行分配。

　　sort_area_size的增大可以減少磁碟排序，但是過大將使ORACLE效能降低，因為所用的連線回話都會分配到一個sort_area_size大小的記憶體，所以，為了提高有限的查詢速度，可能會浪費大量的記憶體。

　　增加sort_multiblock_read_count的值使每次讀取更多的內容，減少執行次數，提高效能。

　　三、SQL子查詢的調整

　　1、理解關聯子查詢和非關聯子查詢。

　　下面是一個非關聯子查詢：

　　select staff_name from staff_member where staff_id

　　in (select staff_id from staff_func);

　　而下面是一個關聯子查詢：

　　select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);

　　以上返回的結果集是相同的，可是它們的執行開銷是不同的：

　　非關聯查詢的開銷——非關聯查詢時子查詢只會執行一次，而且結果是排序好的，並儲存在一個ORACLE的臨時段中，其中的每一個記錄在返回時都會被父查詢所引用。在子查詢返回大量的記錄的情況下，將這些結果集排序，以及將臨時資料段進行排序會增加大量的系統開銷。

　　關聯查詢的開銷——對返回到父查詢的的記錄來說，子查詢會每行執行一次。因此，我們必須保證任何可能的時候子查詢用到索引。

　　2、XISTS子句和IN子句

　　帶IN的關聯子查詢是多餘的，因為IN子句和子查詢中相關的操作的功能是一樣的。如：

　　select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);

　　為非關聯子查詢指定EXISTS子句是不適當的，因為這樣會產生笛卡乘積。如：

　　select staff_name from staff_member where staff_id

　　Exists (select staff_id from staff_func);

　　儘量不要使用NOT IN子句。使用MINUS 子句都比NOT IN 子句快，雖然使用MINUS子句要進行兩次查詢：

　　select staff_name from staff_member where staff_id in (select staff_id from staff_member MINUS select staff_id from staff_func where func_id like ‘81%’);

　　3、 任何可能的時候，用標準連線或內嵌檢視改寫子查詢。

　　四、更新、插入、以及刪除等DML語句的調整

　　1、DML語句是指用來執行更新、插入、以及刪除等操作型別的語句。這些語句在結構上是很簡單的，可調整的餘地較小。效能低下的情況有：

　　插入緩慢並佔有過多的I/O資源——這種情況主要是空閒列表(free list)中的資料塊的空間過小，僅容的下較少的記錄。

　　更新緩慢——這種情況主要是UPDATE操作擴充套件了一個VARCHAR2型別的列，而ORACLE被強制將內容遷移到其他資料塊時。

　　刪除緩慢——這種情況主要是記錄被刪除，ORACLE必須將資料塊重新放置到空閒列表(free list)時。

　　因此，對DML進行調整，主要時利用物件儲存引數和SQL之間的關係進行調整。

　　2、 CTFREE儲存引數

　　PCTFREE儲存引數告訴ORACLE什麼時候應該將資料塊從物件的空閒列表中移出。ORACLE的預設引數是PCTFREE=10；也就是說，一旦一個INSERT操作使得資料塊的90%被使用，這個資料塊就從空閒列表(free list)中移出。

　　PCTUSED儲存引數

　　PCTUSED儲存引數告訴ORACLE什麼時候將以前滿的資料塊加到空閒列表中。當記錄從資料表中刪除時，資料庫的資料塊就有空間接受新的記錄，但只有當填充的空間降到PCTUSED值以下時，該資料塊才被連線到空閒列表中，才可以往其中插入資料。PCTUSED的預設值是PCTUSED=40。

　　儲存引數規則小結

　　（1）PCTUSED較高意味著相對較滿的資料塊會被放置到空閒列表中，從而有效的重複使用資料塊的空間，但會導致I/O消耗。PCTUSED低意味著在一個資料塊快空的時候才被放置到空閒列表中，資料塊一次能接受很多的記錄，因此可以減少I/O消耗，提高效能。

　　（2）PCTFREE的值較大意味著資料塊沒有被利用多少就從空閒列表中斷開連線，不利於資料塊的充分使用。PCTFREE過小的結果是，在更新時可能會出現資料記錄遷移(Migration)的情況。(注：資料記錄遷移(Migration)是指記錄在是UPDATE操作擴充套件了一個VARCHAR2 型別的列或BLOB列後,PCTFREE引數所指定的空間不夠擴充套件，從而記錄被ORACLE強制遷移到新的資料塊，發生這種情況將較嚴重的影響 ORACLE的效能，出現更新緩慢)。

　　（3）在批次的插入、刪除或者更新操作之前，先刪除該表上的索引，在操作完畢之後在重新建立，這樣有助於提高批次操作的整體速度，並且保證B樹索引在操作之後有良好的效能。

　　3、 同最佳化器下的調整；

　　基於成本最佳化器(CBO)：

　　（1）ORACLE 8i 以上版本更多地使用成本最佳化器，因為它更加智慧；

　　（2）透過optimizer_mode=all_rows 或 first_rows來選擇CBO；透過alter session set optimizer_goal=all_rows 或 first_rows來選擇CBO;透過新增hint來選擇CBO;

　　（3）使用基於成本最佳化的一個關鍵是：存在表和索引的統計資料。透過analyze table 獲得表的統計資料；透過analyze index獲得索引的統計資料。

　　（4）對於超過5個表的連線的查詢，建議不要使用成本最佳化器，而是在SQL語句中透過新增/* + rule */提示或者透過指定的執行計劃來避免可能會在20分鐘以上的SQL解析時間。

　　基於規則最佳化器(RBO)：

　　（1）ORACLE 8i以及ORACLE的以前版本主要用(RBO),並且比較有效；

　　（2）透過optimizer_mode=rule來選擇RBO；透過alter session set optimizer_goal=rule來選擇RBO; 透過新增/* + rule */來選擇RBO;

　　（3）在RBO中，from 子句的表的順序決定表的連線順序。From 子句的最後一個表是驅動表，這個表應該是最小的表。

　　（4）限定性最強的布林表示式放在最底層。

　　4、跟蹤、最佳化SQL語句的方法

　　保證在例項級將TIMED_STATISTICS設定為TRUE(在 INIT.ORA中永久的設定它或執行 ALTER SYSTEM 命令臨時設定它)；

　　保證將MAX_DUMP_FILE_SIZE設定的較高。此引數控制跟蹤檔案的大小。

　　決定USER_DUMP_DEST所指向的位置，並保證有足夠的磁碟空間。這是放置跟蹤檔案的位置。

　　在應用系統執行時，開啟所懷疑的回話的SQL_TRACE.(在 INIT.ORA中透過SQL_TRACE=TRUE永久的設定對所有的回話進行跟蹤或透過使用系統包 DBMS_SYSTEM.set_sql_trace_in_session(sid，serial,true);命令臨時設定它)

　　執行業務相關操作；

　　設定跟蹤結束(DBMS_SYSTEM.set_sql_trace_in_session(sid,serial,false)，如果沒有該步驟，可能跟蹤檔案中的資訊不全，因為可能有一部分還在快取中)；

　　定位跟蹤檔案；

　　對步驟6的跟蹤檔案進行TKPROF，生成報告檔案；

　　研究此報告檔案，可以看到CPU、DISK、 QUERY、 COUNT等引數和execution plan(執行計劃)，最佳化開銷最大的SQL；

　　重複執行步驟4)~9)直到達到所需的效能目標；

　　SQL_TRACE、TKPROF、EXPLAIN PLAN和AUTOTRACE為與ORACLE資料庫軟體一起發行的核心工具。掌握這些工具對應用程式的最佳化取得成功有非常大的幫助，具體的使用我就不多說了，大家可以到網上查到使用操作，也可以參考一些有關ORACLE效能調整的書籍。

　　透過上面的探討，我們認識到：基於資料庫的應用系統的核心是資料庫和業務資料；在理解ORACLE資料庫體系框架部分，我們重點在於建立一個對 ORACLE的體系架構的基本的、整體的映像；在SQL效能調整部分，我們討論了有關索引(INDEX)、子查詢、DML效能等問題。請相信：透過增加一個索引、改變SQL語句的連線方法可以極大的改變系統的效能。同時需要說明的是，SQL效能調整遠不止上面的這些，如： ORACLE何時進行並行查詢？表的不同的連線方法(如：NESTED LOOPS JOIN、HASH JOIN等)對SQL有何影響？如何使用提示(hint)進行SQL的最佳化？等等。願大家在工作中逐步積累經驗，從而讓SQL語句有更好的效能。

　　參考文獻：

　　《ORACLE 專家高階程式設計 expert one-on-one ORACLE》 清華大學出版社 Thomas Kyte 著。

　　《ORACLE 效能最佳化技術內幕 ORACLE Performance Tuning 101》機械工業出版社 Gaja Krishna Vaidyanatha,kirtikumar Deshpande,John Kostelac 著。

　　《ORACLE高效能SQL調整 Oracle-High-Performance SQL Tuning》機械工業出版社 DONALD K.Burleson著。

　　《ORACLE8 完全參考手冊 Oracle 8: The Complete Reference》機械工業出版社 Geoge Koch,Kevin Loney 著。