Oracle數(shù)據(jù)庫體系框架介紹及SQL語句性能探討 -------------------------------------------------------------------------------- “我們公司主要的業(yè)務(wù)是軟件開發(fā)和系統(tǒng)集成，而這兩個方面都會涉及到數(shù)據(jù)庫軟件，公司大多數(shù)的產(chǎn)品和工作都和ORACLE數(shù)據(jù)庫有關(guān)；因此，可以說我們宏智科技在ORACLE數(shù)據(jù)庫應(yīng)用方面是高手云集、藏龍臥虎!下面，我本著“魯班門前弄大斧”的動機和“成功就是把犯錯誤的速度提高一倍”的精神，戰(zhàn)戰(zhàn)兢兢地寫下了一些個人認(rèn)為重要的觀點和一點點個人的體會，希望大家不要吝嗇給我成長的機會，多提意見。摘要：基于數(shù)據(jù)庫的業(yè)務(wù)系統(tǒng)的核心是數(shù)據(jù)庫和數(shù)據(jù)。理解ORACLE數(shù)據(jù)的體系框架有助于我們成功開發(fā)基于數(shù)據(jù)庫的業(yè)務(wù)系統(tǒng)。通過增加一個索引、改變SQL語句的連接方法可以極大的改變系統(tǒng)的性能；80%的性能問題都是由不良的SQL語句引起的。要害詞：數(shù)據(jù)庫、ORACLE、體系框架、SQL性能調(diào)整應(yīng)用系統(tǒng)最重要的部分：數(shù)據(jù)庫一個基于數(shù)據(jù)庫的業(yè)務(wù)系統(tǒng)的成功開發(fā)，除了要求項目組對客戶的需求有深刻的理解、對開發(fā)工具有熟練的把握并有卓越治理之外，還要求項組能明確知道應(yīng)用程序如何使用數(shù)據(jù)庫。正如開發(fā)一個成功的業(yè)務(wù)系統(tǒng)對操作系統(tǒng)的理解非常重要一樣，開發(fā)一個成功的基于ORACLE數(shù)據(jù)庫應(yīng)用的業(yè)務(wù)系統(tǒng)要求我們能全面的理解ORACLE。對于一個基于數(shù)據(jù)庫的應(yīng)用系統(tǒng)來說，系統(tǒng)中最重要的部分是數(shù)據(jù)庫。記得我們公司CEO王棟在2000年時，在三明市地方稅務(wù)局對前來福建考察的國家稅務(wù)總局信息官員講述《地方稅收征管信息系統(tǒng)》時說：“三分技術(shù)、七分治理、十二分的數(shù)據(jù)”，這不僅強調(diào)了數(shù)據(jù)的重要性，同時也體現(xiàn)了數(shù)據(jù)庫在一個基于數(shù)據(jù)庫的業(yè)務(wù)系統(tǒng)中的核心地位。一個成功的開發(fā)團隊必定是深刻熟悉這點并讓相關(guān)人員都熟悉這一點的團隊。下面，首先介紹一下ORACLE數(shù)據(jù)庫體系結(jié)構(gòu)，以便開發(fā)人員對ORACLE體系結(jié)構(gòu)有一個基本的、整體的映像；其次是圍繞ORACLE數(shù)據(jù)庫的性能對ORACLE開發(fā)中SQL語句的使用進行一些探討。理解ORACLE數(shù)據(jù)庫體系框架數(shù)據(jù)庫軟件就是處理數(shù)據(jù)文件的一批程序。關(guān)系數(shù)據(jù)庫自上世紀(jì)70年代I B M 圣約瑟研究實驗室的高級研究員埃德加·考特(E F Codd)的《大型共享數(shù)據(jù)庫數(shù)據(jù)的關(guān)系模型》一文發(fā)表世以來，就逐步成為了數(shù)據(jù)庫的主流。1977年，ORACLE公司成立第一個以關(guān)系數(shù)據(jù)庫為核心的軟件公司，現(xiàn)在已經(jīng)推出ORACLE 9i。下面的探討主要以O(shè)RACLE8i版本為基礎(chǔ)。雖然大家在很多介紹ORACLE的書籍中都可以看到類似下面的圖，但是我認(rèn)為下面的這張圖是對ORACLE的體系結(jié)構(gòu)展現(xiàn)的最清楚和簡明扼要的。也許你在看介紹ORACLE的書籍時對這些枯燥的理論介紹沒有太多的關(guān)心，而直接進入你關(guān)心的、可操作的內(nèi)容。現(xiàn)在就讓我們一起對這個圖進行簡單的了解。大家可以看到，假如從簡單的角度來描述，可以說一個ORACLE實例(Instance)是由一定的內(nèi)存與后臺進程組成，而數(shù)據(jù)庫(Database)指物理文件。下面就SGA、五個必須的ORACLE后臺進程進行簡單的介紹：SGA(System Global Area 也稱 Shared Global Area) 主要由以下三部分組成：共享池(Shared Pool) 主要用來存儲最近執(zhí)行過的SQL語句和最近使用過的數(shù)據(jù)字典的數(shù)據(jù)；它主要通過INIT.ORA文件中的shared_pool_size和shared_pool_reserved_size兩個參數(shù)來設(shè)置。數(shù)據(jù)高速緩存區(qū)(Data Buffer Cache) 主要用來存儲最近使用過的數(shù)據(jù)，可能是要寫到數(shù)據(jù)文件的，也可能是從數(shù)據(jù)文件讀取的；它主要通過INIT.ORA文件中db_block_buffers參數(shù)來設(shè)置；Data Buffer的大小＝db_block_buffers* db_block_size;重做日志緩存區(qū)(Redo Log Buffer) 主要存儲服務(wù)進程和后臺進程的變化信息；它主要通過INIT.ORA文件中的log_buffer參數(shù)來設(shè)置； Redo Log Buffer的大小＝log_buffer* db_block_size;當(dāng)然，SGA不僅僅只是上面的三部分，還包括如Java pool(用來存儲java代碼)、Large pool(供不是和SQL直接相關(guān)的進程使用，如：當(dāng)數(shù)據(jù)備份或恢復(fù)操作時，RMAN backup 用作磁盤I/O緩存器；Parallel時用作消息緩存器；MTS回話內(nèi)存)等部分，我們可以通過v$sysstat、v$rowcache、v$librarycache等系統(tǒng)視圖來監(jiān)控SGA。五個必須的ORACLE后臺進程SMON、PMON、DBWn、CKPT、LGWR系統(tǒng)監(jiān)控進程(System Monitor SMON) 在數(shù)據(jù)庫系統(tǒng)啟動時執(zhí)行恢復(fù)工作的強制性進程進程監(jiān)控進程(Process Monitor PMON) 用于恢復(fù)失敗的數(shù)據(jù)庫用戶的強制性進程，它先獲取失敗用戶的標(biāo)識，釋放該用戶占有的所有數(shù)據(jù)庫資源。數(shù)據(jù)庫寫入進程(Database Writer, DBWR) 它將修改后的數(shù)據(jù)塊寫回數(shù)據(jù)庫文件。日志寫入進程(Log Writer , LGWR) 一個專門用于將重做項寫入重做日志的進程。校驗點進程(Checkpoint Process CKPT ) ORACLE把內(nèi)存中臟數(shù)據(jù)塊中的信息寫回磁盤的判定進程。　(注：本圖引自O(shè)RACLE8i的OCP考試培訓(xùn)官方資料) SQL性能調(diào)整我們考慮一個基于ORACLE數(shù)據(jù)庫的應(yīng)用系統(tǒng)的性能時，優(yōu)先要考慮什么呢?主機操作系統(tǒng)？磁盤的I/O還是內(nèi)存的使用？不，都不是！是系統(tǒng)的業(yè)務(wù)規(guī)則。從這個意義上說，我們對各個行業(yè)的業(yè)務(wù)專家的渴求不僅基于市場方面的，更是基于技術(shù)方面的。假如我們能夠優(yōu)化客戶的業(yè)務(wù)規(guī)則，我們的系統(tǒng)將在起點上超越競爭對手！在無法改變客戶的業(yè)務(wù)規(guī)則的情況下,我們考慮影響應(yīng)用系統(tǒng)性能的先后順序應(yīng)該是：首先，考慮SQL語句的性能；其次，考慮內(nèi)存的分配；第三，CPU的使用分配和磁盤I/O瓶頸；第四，考慮網(wǎng)絡(luò)因素;第五，考慮操作系統(tǒng)因素;等等……由此可以說，一個基于數(shù)據(jù)庫的應(yīng)用系統(tǒng)性能的好壞，首先是應(yīng)用系統(tǒng)設(shè)計人員、應(yīng)用系統(tǒng)開發(fā)人員的責(zé)任，而數(shù)據(jù)庫治理員（DBA）是在其基礎(chǔ)上進行的性能調(diào)整。80%的性能問題都是由不良的SQL語句引起的。設(shè)計和建立最佳的SQL對于系統(tǒng)的可擴展性和響應(yīng)時間是基本工作。下面，我主要就SQL語句的性能進行一些粗淺的探討，希望能起到拋磚引玉的效果。SQL語句性能調(diào)整的目標(biāo)是：去掉不必要的大表全表掃描 不必要的大表全表掃描會造成不必要的輸入輸出，而且還會拖垮整個數(shù)據(jù)庫；檢查優(yōu)化索引的使用 這對于提高查詢速度來說非常重要檢查子查詢 考慮SQL子查詢是否可以用簡單連接的方式進行重新書寫；調(diào)整PCTFREE和PCTUSED等存儲參數(shù)優(yōu)化插入、更新或者刪除等操作；考慮數(shù)據(jù)庫的優(yōu)化器;考慮數(shù)據(jù)表的全表掃描和在多個CPU的情況下考慮并行查詢;一、 索引(INDEX)使用的問題1． 索引(INDEX)，用還是不用？這是個的問題。是全表掃描還是索引范圍掃描主要考慮SQL的查詢速度問題。這里主要關(guān)心讀取的記錄的數(shù)目。根據(jù)DONALD K .BURLESON的說法,使用索引范圍掃描的原則是：對于數(shù)據(jù)有原始排序的表，讀取少于表記錄數(shù)40%的查詢應(yīng)該使用索引范圍掃描。對讀取多于表記錄數(shù)40%的查詢應(yīng)全表掃描。對于未排序的表，讀取少于表記錄數(shù)7%的查詢應(yīng)該使用索引范圍掃描,反之，對讀取多于表記錄數(shù)7%的查詢應(yīng)全表掃描。注：在不同的書中，對是否使用索引的讀取記錄的百分比值不太一致，基本上是一個經(jīng)驗值，但是讀取記錄的百分比越低，使用索引越有效。2． 假如列上有建索引，什么SQL查詢是有用索引(INDEX)的？什么SQL查詢是沒有用索引(INDEX)的？存在下面情況的SQL，不會用到索引：存在數(shù)據(jù)類型隱形轉(zhuǎn)換的,如：select * from staff_member where staff_id=’123’；列上有數(shù)學(xué)運算的，如：select * from staff_member where salary*2<10000;使用不等于(<>)運算的，如：select * from staff_member where dept_no<>2001;使用substr字符串函數(shù)的，如：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． 函數(shù)的索引日期類型也是很輕易用到的，而且在SQL語句中會使用to_char函數(shù)以查詢具體的的范圍日期。如：select * from staff_member where TO_CHAR(birth_day,’YYYY’)=’2003’; 我們可以建立基于函數(shù)的索引如：CREATE INDEX Ind_emp_birth ON staff_member (to_char((birth_day,’YYYY’));二、 SQL語句排序優(yōu)化1． 排序發(fā)生的情況：SQL中包含group by 子句SQL 中包含order by 子句SQL 中包含 distinct 子句SQL 中包含 minus 或 union操作創(chuàng)建索引時2． 排序在內(nèi)存還是在磁盤中進行？在內(nèi)存執(zhí)行的排序速度要比在磁盤執(zhí)行的排序速度快14000倍。假如是專用連接，排序內(nèi)存根據(jù)INIT.ORA的sort_area_size進行分配，假如是多線程服務(wù)連接，排序內(nèi)存根據(jù)large_pool_size進行分配。sort_area_size的增大可以減少磁盤排序，但是過大將使ORACLE性能降低，因為所用的連接回話都會分配到一個sort_area_size大小的內(nèi)存，所以，為了提高有限的查詢速度，可能會浪費大量的內(nèi)存。增加sort_multiblock_read_count的值使每次讀取更多的內(nèi)容，減少運行次數(shù)，提高性能。三、SQL子查詢的調(diào)整1、理解關(guān)聯(lián)子查詢和非關(guān)聯(lián)子查詢。下面是一個非關(guān)聯(lián)子查詢：select staff_name from staff_member where staff_idin (select staff_id from staff_func);而下面是一個關(guān)聯(lián)子查詢：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);以上返回的結(jié)果集是相同的，可是它們的執(zhí)行開銷是不同的：非關(guān)聯(lián)查詢的開銷——非關(guān)聯(lián)查詢時子查詢只會執(zhí)行一次，而且結(jié)果是排序好的，并保存在一個ORACLE的臨時段中，其中的每一個記錄在返回時都會被父查詢所引用。在子查詢返回大量的記錄的情況下，將這些結(jié)果集排序，以及將臨時數(shù)據(jù)段進行排序會增加大量的系統(tǒng)開銷。關(guān)聯(lián)查詢的開銷——對返回到父查詢的的記錄來說，子查詢會每行執(zhí)行一次。因此，我們必須保證任何可能的時候子查詢用到索引。2、XISTS子句和IN子句帶IN的關(guān)聯(lián)子查詢是多余的，因為IN子句和子查詢中相關(guān)的操作的功能是一樣的。如：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);為非關(guān)聯(lián)子查詢指定EXISTS子句是不適當(dāng)?shù)模驗檫@樣會產(chǎn)生笛卡乘積。如：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、 任何可能的時候，用標(biāo)準(zhǔn)連接或內(nèi)嵌視圖改寫子查詢。四、更新、插入、以及刪除等DML語句的調(diào)整1、DML語句是指用來執(zhí)行更新、插入、以及刪除等操作類型的語句。這些語句在結(jié)構(gòu)上是很簡單的，可調(diào)整的余地較小。性能低下的情況有：插入緩慢并占有過多的I/O資源——這種情況主要是空閑列表(free list)中的數(shù)據(jù)塊的空間過小，僅容的下較少的記錄。更新緩慢——這種情況主要是UPDATE操作擴展了一個VARCHAR2類型的列，而ORACLE被強制將內(nèi)容遷移到其他數(shù)據(jù)塊時。刪除緩慢——這種情況主要是記錄被刪除，ORACLE必須將數(shù)據(jù)塊重新放置到空閑列表(free list)時。因此，對DML進行調(diào)整，主要時利用對象存儲參數(shù)和SQL之間的關(guān)系進行調(diào)整。2、 CTFREE存儲參數(shù)PCTFREE存儲參數(shù)告訴ORACLE什么時候應(yīng)該將數(shù)據(jù)塊從對象的空閑列表中移出。ORACLE的默認(rèn)參數(shù)是PCTFREE=10；也就是說，一旦一個INSERT操作使得數(shù)據(jù)塊的90%被使用，這個數(shù)據(jù)塊就從空閑列表(free list)中移出。PCTUSED存儲參數(shù)PCTUSED存儲參數(shù)告訴ORACLE什么時候?qū)⒁郧皾M的數(shù)據(jù)塊加到空閑列表中。當(dāng)記錄從數(shù)據(jù)表中刪除時，數(shù)據(jù)庫的數(shù)據(jù)塊就有空間接受新的記錄，但只有當(dāng)填充的空間降到PCTUSED值以下時，該數(shù)據(jù)塊才被連接到空閑列表中，才可以往其中插入數(shù)據(jù)。PCTUSED的默認(rèn)值是PCTUSED=40。存儲參數(shù)規(guī)則小結(jié)（1）PCTUSED較高意味著相對較滿的數(shù)據(jù)塊會被放置到空閑列表中，從而有效的重復(fù)使用數(shù)據(jù)塊的空間，但會導(dǎo)致I/O消耗。PCTUSED低意味著在一個數(shù)據(jù)塊快空的時候才被放置到空閑列表中，數(shù)據(jù)塊一次能接受很多的記錄，因此可以減少I/O消耗，提高性能。（2）PCTFREE的值較大意味著數(shù)據(jù)塊沒有被利用多少就從空閑列表中斷開連接，不利于數(shù)據(jù)塊的充分使用。PCTFREE過小的結(jié)果是，在更新時可能會出現(xiàn)數(shù)據(jù)記錄遷移(Migration)的情況。(注：數(shù)據(jù)記錄遷移(Migration)是指記錄在是UPDATE操作擴展了一個VARCHAR2類型的列或BLOB列后,PCTFREE參數(shù)所指定的空間不夠擴展，從而記錄被ORACLE強制遷移到新的數(shù)據(jù)塊，發(fā)生這種情況將較嚴(yán)重的影響ORACLE的性能，出現(xiàn)更新緩慢)。（3）在批量的插入、刪除或者更新操作之前，先刪除該表上的索引，在操作完畢之后在重新建立，這樣有助于提高批量操作的整體速度，并且保證B樹索引在操作之后有良好的性能。3、 同優(yōu)化器下的調(diào)整；基于成本優(yōu)化器(CBO)：（1）ORACLE 8i 以上版本更多地使用成本優(yōu)化器，因為它更加智能；（2）通過optimizer_mode=all_rows 或 first_rows來選擇CBO；通過alter session set optimizer_goal=all_rows 或 first_rows來選擇CBO;通過添加hint來選擇CBO;（3）使用基于成本優(yōu)化的一個要害是：存在表和索引的統(tǒng)計資料。通過analyze table 獲得表的統(tǒng)計資料；通過analyze index獲得索引的統(tǒng)計資料。（4）對于超過5個表的連接的查詢，建議不要使用成本優(yōu)化器，而是在SQL語句中通過添加/* + rule */提示或者通過指定的執(zhí)行計劃來避免可能會在20分鐘以上的SQL解析時間。基于規(guī)則優(yōu)化器(RBO)：（1）ORACLE 8i以及ORACLE的以前版本主要用(RBO),并且比較有效；（2）通過optimizer_mode=rule來選擇RBO；通過alter session set optimizer_goal=rule來選擇RBO; 通過添加/* + rule */來選擇RBO;（3）在RBO中，from 子句的表的順序決定表的連接順序。From 子句的最后一個表是驅(qū)動表，這個表應(yīng)該是最小的表。（4）限定性最強的布爾表達式放在最底層。 4、跟蹤、優(yōu)化SQL語句的方法保證在實例級將TIMED_STATISTICS設(shè)置為TRUE(在 INIT.ORA中永久的設(shè)置它或執(zhí)行 ALTER SYSTEM 命令臨時設(shè)置它)；保證將MAX_DUMP_FILE_SIZE設(shè)置的較高。此參數(shù)控制跟蹤文件的大小。決定USER_DUMP_DEST所指向的位置，并保證有足夠的磁盤空間。這是放置跟蹤文件的位置。在應(yīng)用系統(tǒng)運行時，打開所懷疑的回話的SQL_TRACE.(在 INIT.ORA中通過SQL_TRACE=TRUE永久的設(shè)置對所有的回話進行跟蹤或通過使用系統(tǒng)包DBMS_SYSTEM.set_sql_trace_in_session(sid，serial,true);命令臨時設(shè)置它)執(zhí)行業(yè)務(wù)相關(guān)操作；設(shè)置跟蹤結(jié)束(DBMS_SYSTEM.set_sql_trace_in_session(sid,serial,false)，假如沒有該步驟，可能跟蹤文件中的信息不全，因為可能有一部分還在緩存中)；定位跟蹤文件；對步驟6的跟蹤文件進行TKPROF，生成報告文件；研究此報告文件，可以看到CPU、DISK、 QUERY、 COUNT等參數(shù)和execution plan(執(zhí)行計劃)，優(yōu)化開銷最大的SQL；重復(fù)執(zhí)行步驟4)~9)直到達到所需的性能目標(biāo)；SQL_TRACE、TKPROF、EXPLAIN PLAN和AUTOTRACE為與ORACLE數(shù)據(jù)庫軟件一起發(fā)行的核心工具。把握這些工具對應(yīng)用程序的優(yōu)化取得成功有非常大的幫助，具體的使用我就不多說了，大家可以到網(wǎng)上查到使用操作，也可以參考一些有關(guān)ORACLE性能調(diào)整的書籍。通過上面的探討，我們熟悉到：基于數(shù)據(jù)庫的應(yīng)用系統(tǒng)的核心是數(shù)據(jù)庫和業(yè)務(wù)數(shù)據(jù)；在理解ORACLE數(shù)據(jù)庫體系框架部分，我們重點在于建立一個對ORACLE的體系架構(gòu)的基本的、整體的映像；在SQL性能調(diào)整部分，我們討論了有關(guān)索引(INDEX)、子查詢、DML性能等問題。請相信：通過增加一個索引、改變SQL語句的連接方法可以極大的改變系統(tǒng)的性能。同時需要說明的是，SQL性能調(diào)整遠不止上面的這些，如： ORACLE何時進行并行查詢？表的不同的連接方法(如：NESTED LOOPS JOIN、HASH JOIN等)對SQL有何影響？如何使用提示(hint)進行SQL的優(yōu)化？等等。愿大家在工作中逐步積累經(jīng)驗，從而讓SQL語句有更好的性能。參考文獻：《ORACLE 專家高級編程 expert one-on-one ORACLE》 清華大學(xué)出版社 Thomas Kyte 著。《ORACLE 性能優(yōu)化技術(shù)內(nèi)幕 ORACLE Performance Tuning 101》機械工業(yè)出版社 Gaja Krishna Vaidyanatha,kirtikumar Deshpande,John Kostelac 著。《ORACLE高性能SQL調(diào)整 Oracle-High-Performance SQL Tuning》機械工業(yè)出版社 DONALD K.Burleson著。《ORACLE8 完全參考手冊 Oracle 8: The Complete Reference》機械工業(yè)出版社 Geoge Koch,Kevin Loney 著。