Oracle數據庫體系框架介紹及SQL語句性能探討 -------------------------------------------------------------------------------- “我們公司主要的業務是軟件開發和系統集成，而這兩個方面都會涉及到數據庫軟件，公司大多數的產品和工作都和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的大?。絣og_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把內存中臟數據塊中的信息寫回磁盤的判定進程?！?注：本圖引自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_idin (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_idExists (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 著?！禣RACLE 性能優化技術內幕 ORACLE Performance Tuning 101》機械工業出版社 Gaja Krishna Vaidyanatha,kirtikumar Deshpande,John Kostelac 著?！禣RACLE高性能SQL調整 Oracle-High-Performance SQL Tuning》機械工業出版社 DONALD K.Burleson著?！禣RACLE8 完全參考手冊 Oracle 8: The Complete Reference》機械工業出版社 Geoge Koch,Kevin Loney 著。