讓Oracle跑得更快——Oracle10g性能分析與優(yōu)化

1、第1章 引起數(shù)據(jù)庫性能問題的因素1.1 軟件設(shè)計對數(shù)據(jù)庫的影響1.1.1 軟件架構(gòu)設(shè)計對數(shù)據(jù)庫性能的影響軟件系統(tǒng)的架構(gòu)對數(shù)據(jù)庫的影響是非常直接的。例如一套系統(tǒng)，如果并發(fā)數(shù)非常大，比如是超過3000個并發(fā)，通常這種情況下，我們會考慮采用一套軟件來搭建一個中間層，這就是通常講到的3層或是多層結(jié)構(gòu)。使用這一套軟件的目的是用來構(gòu)建一個緩沖池，在數(shù)據(jù)庫之前對大量的并發(fā)進(jìn)行處理，以便于每次只有少數(shù)的用戶連接到數(shù)據(jù)庫中，其他的用戶在緩沖池的隊列中等待。當(dāng)然，這只是一個動態(tài)的過程，程序會盡可能快地去響應(yīng)所有用戶的請求，這種提前對大量并發(fā)用戶進(jìn)行處理的方式，會比讓這3000個用戶直接連接到數(shù)據(jù)庫中效果要好得多，

2、同時數(shù)據(jù)庫也可以使用更多的資源來處理用戶的操作請求而不是去開3000個進(jìn)程來處理每個用戶的請求，這個開銷是非常大的。所以對于大量并發(fā)的系統(tǒng)來講，在數(shù)據(jù)庫之前建一個緩沖用戶請求的中間件服務(wù)，顯得至關(guān)重要。同時，很多這種中間件軟件還提供了負(fù)載均衡的功能。當(dāng)然，Oracle數(shù)據(jù)庫自身也提供了一種MTS的技術(shù)，作用和這種中間件服務(wù)是一樣的，但目前看來，采用商業(yè)中間件軟件或是開發(fā)商自己開發(fā)一套中間件服務(wù)的做法更多一些（參見圖1-1）。圖1-1 多層架構(gòu)體系1.1.2 軟件代碼的編寫對數(shù)據(jù)庫性能的影響軟件代碼對數(shù)據(jù)庫的影響，通常指的是應(yīng)用程序代碼中對數(shù)據(jù)庫操作的代碼部分對數(shù)據(jù)庫產(chǎn)生的影響。具體來講就是SQ

3、L語句或是PL/SQL包。SQL語句造成的影響，一種是SQL語句本身在邏輯上就是效率低下的，另一種就是SQL語句沒有綁定變量。性能低下的SQL語句，比如使用Hint，不合適的外連接，謂詞的隱含轉(zhuǎn)換，優(yōu)化器的選擇等，會對SQL的執(zhí)行產(chǎn)生非常大的影響，特別是多表關(guān)聯(lián)的情況下，影響更是顯著。它主要體現(xiàn)為SQL語句的執(zhí)行受到了人為的約束，比如數(shù)據(jù)的訪問方式（索引還是全表掃描），以及表關(guān)聯(lián)方式的選擇上（Hah Join，Nested Loops）。  人為地在SQL代碼中加入Hint來約束SQL的執(zhí)行計劃我曾經(jīng)遇到的一個例子就是這樣，開發(fā)人員為了要求每次對一個表做操作的時候都使用索引，于是在代

4、碼中強(qiáng)行加入了Hint約束SQL的執(zhí)行計劃，它的樣子大概是這樣：Select /*+ index(t1 ind_t1) */ col1,col2 from t1 where col1>. and col1<.我猜測他們在系統(tǒng)上線之前測試的階段，發(fā)現(xiàn)這條SQL選擇索引比全表掃描效率高得多，為了保證以后執(zhí)行計劃能夠始終選擇索引，他們在代碼中的SQL里加入了這個Hint。系統(tǒng)上線后，沒有出現(xiàn)過問題，直到有一天用戶抱怨查詢非常慢，我從數(shù)據(jù)庫里得到了用戶端發(fā)出的SQL，才知道這個SQL在代碼里加入了Hint。問題是，為什么之前操作都沒有問題呢？我登錄這個數(shù)據(jù)庫，查看了一下這個表的信息，驚奇地

5、發(fā)現(xiàn)，這個表每天仍然定時在做分析操作，這是一個奇怪的現(xiàn)象，人為地對表進(jìn)行定時分析，卻不允許數(shù)據(jù)庫來選擇執(zhí)行計劃，這顯然是不合理的事情。但這種現(xiàn)象在開發(fā)人員當(dāng)中又是比較普遍的，大家了解一些數(shù)據(jù)庫的技術(shù)，卻無法將這些知識整合起來運用，系統(tǒng)設(shè)計及開發(fā)階段，沒有DBA參與進(jìn)來，直到系統(tǒng)進(jìn)入運行維護(hù)階段，才有DBA來充當(dāng)救火隊員的角色，這在當(dāng)前中國軟件開發(fā)中是一個很普遍的現(xiàn)象。接著說這個案例。這是一個Oracle 10gr2的數(shù)據(jù)庫，CBO（基于成本的優(yōu)化器）的技術(shù)已經(jīng)比較成熟，所以此時應(yīng)該選擇由Oracle數(shù)據(jù)庫來決定SQL的執(zhí)行計劃。我分別執(zhí)行了這條原始SQL和去掉了Hint的SQL，并獲得了各自的

6、執(zhí)行計劃，執(zhí)行計劃顯示出，去掉Hint的SQL選擇了全表掃描（Full Table Scan），執(zhí)行中掃過的數(shù)據(jù)塊遠(yuǎn)遠(yuǎn)小于通過索引訪問數(shù)據(jù)的SQL，于是原因找到了?？墒菫槭裁粗皼]有出現(xiàn)過這個問題？我對比了一下最近的數(shù)據(jù)和之前的數(shù)據(jù)，發(fā)現(xiàn)近期的數(shù)據(jù)在創(chuàng)建索引的列上的列值重復(fù)率要遠(yuǎn)遠(yuǎn)高于從前，因此Oracle在選擇索引之后，比以前讀取了更多的索引塊和數(shù)據(jù)塊，造成了大量的I/O操作。因此，對于高版本的數(shù)據(jù)庫（10g以上），我們還是應(yīng)該讓數(shù)據(jù)庫自己根據(jù)表、索引的統(tǒng)計分析信息來決定SQL的執(zhí)行計劃，因為表中的數(shù)據(jù)是會變化的，這種人為的強(qiáng)行干預(yù)，必然會在某個時候出現(xiàn)問題。1.1.2.2 不必要的外連接操

7、作外連接是一個代價非常昂貴的執(zhí)行過程。如果業(yè)務(wù)需要，這種操作是必要的，但是有時候會出現(xiàn)人為地在SQL中使用不必要的外連接的情形，這實際上是因為有的開發(fā)人員擔(dān)心遺漏一些數(shù)據(jù)而刻意使用它，這就非常有可能留下隱患，讓數(shù)據(jù)庫選擇昂貴的執(zhí)行計劃而不是最優(yōu)的那一個。下面的這個例子說明了一個不必要的外連接使用。我們創(chuàng)建兩個簡單的表，并插入一些數(shù)據(jù)，同時給其中的一個表T2的C列上插入一些空值:SQL> create table t1 as select rownum a,rownum+100 b from dba_users where rownum<10; 表已創(chuàng)建。 SQL

8、> create table t2 as select decode(mod(rownum,2),0,rownum) c,rownum+1000 d from dba_users where rownum<10; 表已創(chuàng)建。 SQL> select * from t1;  A B- - 1 101 2 102 3 103 4 104 5 105 6 106 7 107 8 108 9 109 已選擇9行。 SQL> select * from t2;  C D- - 1001 2 1002 1003 4 100

9、4 1005 6 1006 1007 8 1008 1009 已選擇9行。 通過下面這條語句，通過使用A字段和T2表C字段關(guān)聯(lián)，我們獲取了T1表上所有的行，以及T2表上符合條件的行：SQL> select a,b,c,d from t1,t2 where t1.a=t2.c(+) ;  A B C D- - - - 2 102 2 1002 4 104 4 1004 6 106 6 1006 8 108 8 1008 1 101 3 103 5 105 7 107 9 109 請看下面這條SQL，它是什么意思呢？SQL> select a,b

10、,c,d from t1,t2 where t1.a=t2.c(+) and t2.d>1000; A B C D- - - - 2 102 2 1002 4 104 4 1004 6 106 6 1006 8 108 8 1008 這條SQL的意思是告訴數(shù)據(jù)庫，我要得到T1表上所有的行，并且用A列和T2表C做關(guān)聯(lián)，同時要求T2表C列的值大于1000。讓我們再看看另一條結(jié)果集完全一樣的SQL：SQL> select a,b,c,d from t1,t2 where t1.a=t2.c and t2.d>1000;  A B C D- - - - 2 102

11、 2 1002 4 104 4 1004 6 106 6 1006 8 108 8 1008 從結(jié)果集上來看，這是兩條等價的SQL語句，就是說，在這種情況下，外連接其實是沒有用的，是人為地在SQL里設(shè)定的限制！如果仔細(xì)看一下第一條語句，我們不難發(fā)現(xiàn)，條件中T2.C>1000已經(jīng)明確地指出，在結(jié)果集中，T2表在任何一行，C列都應(yīng)該有值的，也就是在這種情況下，根本就不需要使用外連接，業(yè)務(wù)邏輯上講，外連接在這里是多余的。這種情況在開發(fā)人員的代碼中有時候會遇到，對他們來講，只要保證結(jié)果集正確就好，但對數(shù)據(jù)庫來講，在執(zhí)行時可能會引起極大的性能差別。1.1.2.3 CBO下優(yōu)化器模式的選擇

12、通常對于一種功能單一的數(shù)據(jù)庫來講，在實例級設(shè)置一個優(yōu)化器模式就可以了，比如對于OLAP系統(tǒng)，絕大多數(shù)時候數(shù)據(jù)庫上運行著的是報表作業(yè)，執(zhí)行基本上是聚合類的SQL操作，比如GROUP BY，這時候，把優(yōu)化器模式設(shè)置成all_rows是恰當(dāng)?shù)摹６鴮τ谝恍┓猪摬僮鞅容^多的網(wǎng)站類數(shù)據(jù)庫，設(shè)置成first_rows會更好一些。我卻遇到了另外的一件事情。我們的數(shù)據(jù)庫上運行著的基本上是一個OLAP系統(tǒng)，所以優(yōu)化器模式設(shè)置為ALL_ROWS，這有利于報表SQL的快速完成。但數(shù)據(jù)庫上還運行著一些用戶查詢的業(yè)務(wù)，查詢的方式可以說成是分頁的。有時候就會出現(xiàn)用戶抱怨查詢慢的問題，盡管我知道問題所在，卻比較難解決，因為這

13、些SQL已經(jīng)被開發(fā)人員寫到代碼里面了。針對這種情況，如果能在開發(fā)階段就考慮到這個問題，針對需要分頁操作的SQL，開發(fā)人員在SQL里通過Hint的方式來將優(yōu)化模式轉(zhuǎn)換成FIRST_ROWS，這樣就可以大大地提高數(shù)據(jù)的處理速度。比如這樣一個每次取出10條記錄的分頁查詢：Select * from (SELECT /*+ first_rows(10) */ a.*,rownum rnum from (SELECT /*+ first_rows(10) */ id,name from t1 order by id) aWhere rownum<=10)Where rnum>=1; 

14、;可以在每個子查詢中重復(fù)使用FIRST_ROWS(n)來提高查詢效率。盡管說在SQL中人為地加入Hint操作不是一個好主意，但是有些時候，比如需要兼顧其他的用戶操作時，可以考慮做這樣的設(shè)定。但前期需要做一些測試工作，以確保這樣的設(shè)定能夠帶來性能上的提高，同時不會對數(shù)據(jù)庫造成其他方面的影響。這是系統(tǒng)設(shè)計階段應(yīng)該仔細(xì)考慮好的一個問題。1.1.2.4 沒有綁定變量的SQL對于這個話題，其實很多人存在著一個誤區(qū)。記得有一次在廣州出差，我和一個同樣做數(shù)據(jù)庫的同事，有這樣一段對話：同事：“我們的系統(tǒng)有沒有綁定變量?”我：“不知道.”同事：“我發(fā)現(xiàn)沒有綁定?！彼谋砬楹苣?，仿佛發(fā)現(xiàn)了數(shù)據(jù)庫的一個致命隱患一

15、般。我：“無所謂吧？”他立即反駁我說：“誰說無所謂，SQL沒有綁定變量，數(shù)據(jù)庫每次執(zhí)行就會發(fā)生硬分析（Hard parse，喜歡讀Statspack Report的朋友對這個詞應(yīng)該很敏感吧，我的同事就是一個Statspack fans，那時候他正在研究Statspack,覺得如果硬分析太多了，天就要塌下來，仿佛把這些硬分析變?yōu)檐浄治鲋?，?shù)據(jù)庫性能會提高成百上千倍一樣），這樣性能肯定會大受影響，有時候用戶反映查詢慢，會不會是這個原因?qū)е碌?？”我說不是這個原因?qū)е碌?，我可以保證，因為我們是這樣的一個系統(tǒng)：數(shù)據(jù)庫的用戶連接數(shù)很少，大概不會超過50個，每個用戶每天發(fā)出的查詢操作不會超過50個，這對于

16、一個運行在內(nèi)存8個GB，10幾個CPU的系統(tǒng)上的數(shù)據(jù)庫來說，硬分析對數(shù)據(jù)庫性能的影響微乎其微，完全可以忽略掉，因為我們是一個OLAP系統(tǒng)。他想了一下，認(rèn)同了我的觀點。我想說的綁定變量的誤區(qū)就和上面這個案例一樣，有時候它對性能的影響被夸大化了。我在ITPUB上總看到很多這樣的帖子，大家在談及SQL時必定要求綁定變量，仿佛不這樣系統(tǒng)就要出問題了一樣。實際上，至少對于OLAP系統(tǒng)（在線分析系統(tǒng)，通常指的是這樣的一個系統(tǒng)，數(shù)據(jù)庫存放著海量的數(shù)據(jù)，連接的用戶少，SQL語句基本上都是用戶產(chǎn)生報表的大查詢）來說，未綁定變量對數(shù)據(jù)庫的影響是很有限的，甚至是完全沒有必要的，因為只有少量的用戶和少量的SQL操作，

17、數(shù)據(jù)庫不需要花多少資源在SQL分析上面。這個話題我們會在后續(xù)的章節(jié)中討論到。綁定變量的真正用途是在一個OLTP系統(tǒng)中，這個系統(tǒng)通常有這樣的特點，用戶并發(fā)數(shù)很大，用戶的請求十分密集，并且這些請求的SQL大多數(shù)是可以重復(fù)使用的，我們試想，當(dāng)這些成千上萬的SQL被數(shù)據(jù)庫一遍又一遍地進(jìn)行語法分析、語義分析，生成執(zhí)行計劃時，這對數(shù)據(jù)庫的壓力該有多大？如果一條SQL執(zhí)行一遍之后就被緩存到數(shù)據(jù)庫的內(nèi)存當(dāng)中（實際上是在共享池里），以后的成百上千的用戶請求都使用這個SQL解析后的結(jié)果，那效率將有多么大的提高！所以，我的觀點是，當(dāng)你要考察綁定變量對你的數(shù)據(jù)庫的影響有多大時，先確定你的系統(tǒng)是OLTP系統(tǒng)或是OLAP

18、系統(tǒng)；當(dāng)然，現(xiàn)在很多數(shù)據(jù)庫同時擔(dān)負(fù)這兩種角色，那么你需要分析數(shù)據(jù)庫的性能情況，比如，做一個Statspack Report來幫助你確定變量是否綁定，以及是否已經(jīng)對系統(tǒng)的性能構(gòu)成嚴(yán)重的影響。1.1.2.5 PL/SQL包如果你的程序里面有PL/SQL包，請考慮使用存儲過程來代替它，存儲過程是經(jīng)過成功編譯后存放在數(shù)據(jù)庫中的代碼，執(zhí)行起來的效率要比程序代碼中PL/SQL包的效率高很多，因為它不再需要做語法和語義的分析（語法的分析指的是數(shù)據(jù)庫對代碼進(jìn)行檢查，看它是否存在語法上的錯誤；而語義分析是查看語句執(zhí)行的對象是否存在，比如需要操作的表、列等，以及是否有執(zhí)行這些操作的權(quán)限）。 1.2 數(shù)據(jù)

19、庫的設(shè)計數(shù)據(jù)庫的設(shè)計在系統(tǒng)設(shè)計當(dāng)中是一個非常重要的環(huán)節(jié)，但目前看來，很多開發(fā)商忽略了它應(yīng)有的重要性，大多數(shù)的數(shù)據(jù)庫設(shè)計基本上等同于創(chuàng)建業(yè)務(wù)所需要的所有對象，僅此而已。這是作者從事了10年DBA工作的切身體會，也許這也不能全怪開發(fā)商，比如他們有工期的壓力，有人員成本的壓力，那應(yīng)該是另外一個話題。對于數(shù)據(jù)庫的設(shè)計，我認(rèn)為除了一些必需的對象創(chuàng)建之外，應(yīng)該還要更多地考慮在整個系統(tǒng)運行的生命周期中，按照系統(tǒng)的實際情況及可能的變化做一些前瞻性的設(shè)計，以基本滿足系統(tǒng)生命周期里的各方面需求，不至于發(fā)生大的修改或是升級。說起系統(tǒng)的升級，這是一個有趣的話題，可能很多開發(fā)人員，特別是做項目開發(fā)的人員，應(yīng)該會深有體會

20、。比如我身邊的一些案例，明明是最初設(shè)計上存在著缺陷或者疏漏，導(dǎo)致后來系統(tǒng)出了問題，卻成了開發(fā)商項目的二期、三期的理由，也成為軟件1.0版、2.0版的理由?；旧峡磥?，前期數(shù)據(jù)庫設(shè)計的一個根基就是要弄清楚數(shù)據(jù)庫的類型。通常來說，我們把業(yè)務(wù)分為兩類，在線事務(wù)處理系統(tǒng)（OLTP）和在線分析系統(tǒng)（OLAP）或者DSS（決策支持系統(tǒng)），這兩類系統(tǒng)在數(shù)據(jù)庫的設(shè)計上是如此不同，甚至有些地方的設(shè)計是貌似相悖的。比如OLTP系統(tǒng)強(qiáng)調(diào)數(shù)據(jù)庫的內(nèi)存效率，強(qiáng)調(diào)內(nèi)存各種指標(biāo)的命中率，強(qiáng)調(diào)綁定變量，強(qiáng)調(diào)并發(fā)操作；而OLAP系統(tǒng)則強(qiáng)調(diào)數(shù)據(jù)分析，強(qiáng)調(diào)SQL執(zhí)行時長，強(qiáng)調(diào)磁盤I/O，強(qiáng)調(diào)分區(qū)等。因為這些區(qū)別，在數(shù)據(jù)庫設(shè)計的階段

21、，弄清楚數(shù)據(jù)庫類型是至關(guān)重要的，只有在這個前提之下，才能夠討論數(shù)據(jù)庫的具體設(shè)計，否則設(shè)計必然是盲目的，“皮之不存毛將焉附”。1.2.1 OLTP數(shù)據(jù)庫OLAP和OLTP是兩類完全不同的系統(tǒng)，對數(shù)據(jù)庫的要求也截然不同。通常來講，OLTP（在線事務(wù)處理系統(tǒng)）的用戶并發(fā)數(shù)都很多，但他們只對數(shù)據(jù)庫做很小的操作，數(shù)據(jù)庫側(cè)重于對用戶操作的快速響應(yīng)，這是對數(shù)據(jù)庫最重要的性能要求。我清楚地記得在2008年的時候，某個門票在線銷售系統(tǒng)允許人們通過網(wǎng)絡(luò)購買門票，這是一個典型的OLTP系統(tǒng)。我當(dāng)時還想嘗試去買一張，結(jié)果是還沒等到我去買，就聽說系統(tǒng)癱瘓了。我想，應(yīng)該是在線購票的用戶數(shù)太多吧，導(dǎo)致數(shù)據(jù)庫（我不太確定，也

22、可能是中間件系統(tǒng)）沒有辦法處理大量的連接，從而導(dǎo)致了系統(tǒng)崩潰。這真是一個慘痛的教訓(xùn)，它用事實告訴我們，對于一個系統(tǒng)，特別是非常重要的系統(tǒng)，一些前瞻性的預(yù)測和系統(tǒng)的壓力測試有多么的重要。對于一個OLTP系統(tǒng)來說，數(shù)據(jù)庫內(nèi)存設(shè)計顯得很重要，如果數(shù)據(jù)都可以在內(nèi)存中處理，那么數(shù)據(jù)庫的性能無疑會提高很多。我知道有些對處理速度要求很高的系統(tǒng)，已經(jīng)采用了一些內(nèi)存數(shù)據(jù)庫，比如Oracle的Times Ten。內(nèi)存的設(shè)計通常是通過調(diào)整Oracle和內(nèi)存相關(guān)的初始化參數(shù)來實現(xiàn)的，比較重要的幾個是內(nèi)存相關(guān)的參數(shù)，包括SGA的大?。―ata Buffer，Shared Pool）、PGA大?。ㄅ判騾^(qū)，Hash區(qū)等）等

23、，這些參數(shù)在一個OLTP系統(tǒng)里顯得至關(guān)重要，OLTP系統(tǒng)是一個數(shù)據(jù)塊變化非常頻繁、SQL語句提交非常頻繁的系統(tǒng)。對于數(shù)據(jù)塊來說，應(yīng)盡可能讓數(shù)據(jù)塊保存在內(nèi)存當(dāng)中，對于SQL來說，盡可能使用變量綁定技術(shù)來達(dá)到SQL的重用，減少物理I/O和重復(fù)的SQL解析，能極大地改善數(shù)據(jù)庫的性能。關(guān)于一些初始化參數(shù)的設(shè)定的問題，我認(rèn)為，這里絕沒有一個確定的標(biāo)準(zhǔn)，這和每個數(shù)據(jù)庫上運行的業(yè)務(wù)直接相關(guān)，能夠確定這些參數(shù)值的唯一方法就是測試，先給這些參數(shù)設(shè)定一個經(jīng)驗值，然后通過搭建測試環(huán)境對數(shù)據(jù)庫進(jìn)行測試，通過一些性能報告（比如AWR或者Staspack 報告）作為依據(jù)，不斷地調(diào)整這些參數(shù)值，以達(dá)到最佳的性能。除了內(nèi)存、

24、沒有綁定變量的SQL會對OLTP數(shù)據(jù)庫造成極大的性能影響之外，還有一些因素也會導(dǎo)致數(shù)據(jù)庫的性能下降，比如熱塊（hot block）的問題，當(dāng)一個塊被多個用戶同時讀取的時候，Oracle為了維護(hù)數(shù)據(jù)的一致性，需要使用一種稱為Latch的東西來串行化用戶的操作。當(dāng)一個用戶獲得了這個Latch后，其他的用戶就只能被迫等待。獲取這個數(shù)據(jù)塊的用戶越多，等待就越明顯，就造成了這種熱塊問題。這種熱塊可能是數(shù)據(jù)塊，也可能是回滾段塊。對于數(shù)據(jù)塊來講，通常是數(shù)據(jù)塊上的數(shù)據(jù)分布不均勻?qū)е?，如果是索引的?shù)據(jù)塊，可以考慮創(chuàng)建反向索引來達(dá)到重新分布數(shù)據(jù)的目的，對于回滾段數(shù)據(jù)塊，可以適當(dāng)多增加幾個回滾段來避免這種爭用（熱塊

25、部分在后面有專門的章節(jié)討論）。1.2.2 OLAP數(shù)據(jù)庫我一直認(rèn)為OLAP數(shù)據(jù)庫在內(nèi)存上可優(yōu)化的余地很小，甚至覺得增加CPU處理速度和磁盤I/O速度是最直接的提高數(shù)據(jù)庫性能的方式，但這將意味著系統(tǒng)成本的增加。實際上，用戶對OLAP系統(tǒng)性能的期望遠(yuǎn)遠(yuǎn)沒有對OLTP性能的期望那么高。內(nèi)存的優(yōu)化，對OLAP來講影響很小，比如我曾經(jīng)遇到的一個數(shù)據(jù)庫，每天晚上運行的報表程序，基本上都是對幾億條或者幾十億條數(shù)據(jù)進(jìn)行聚合處理，這種海量的數(shù)據(jù)，全部在內(nèi)存中操作是很難的，同時也完全沒有必要，因為這些數(shù)據(jù)塊很少重用，緩存起來沒有實際意義，倒是物理I/O相當(dāng)大，這種系統(tǒng)的瓶頸往往是在磁盤I/O上面。對于OLAP系統(tǒng)

26、，SQL的優(yōu)化顯得非常重要，試想，如果一張表中只有幾千條數(shù)據(jù)，無論執(zhí)行全表掃描或是使用索引，對我們來說差異都很小，幾乎感覺不出來，但是當(dāng)數(shù)據(jù)量提升到幾億或者幾十億甚至更多的時候，全表掃描、索引可能導(dǎo)致極大的性能差異，因此SQL的優(yōu)化顯得重要起來?？聪旅娴囊粋€例子，它對比了索引和全表掃描的效率：* select * from t where object_id<100 call count cpu elapsed disk query current rows- - - -Parse 1 0.01 0.00 0 0 0 0Execute 1 0.00 0.00 0 0

27、0 0Fetch 8 0.00 0.00 0 17 0 98- - - -total 10 0.01 0.00 0 17 0 98 Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 55  Rows Row Source Operation- - 98 TABLE ACCESS BY INDEX ROWID T (cr=17 pr=0 pw=0 time=95 us) 98 INDEX RANGE SCAN T_INX (cr=9 pr=0 pw=0 time=23

28、83 us)(object id 51627) * select /*+ full(t) */ * from t where object_id<100  call count cpu elapsed disk query current rows- - -Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 8 0.01 0.00 0 695 0 98- - -total 10 0.01 0.01 0 695 0 98 Misses in library cache duri

29、ng parse: 1Optimizer mode: ALL_ROWSParsing user id: 55  Rows Row Source Operation- - 98 TABLE ACCESS FULL T (cr=695 pr=0 pw=0 time=116 us) * 我們看到，在這個只有幾萬條記錄的表中，相同的SQL語句，全表掃描掃過的數(shù)據(jù)塊（一致性讀）是695個，而索引只掃過了17個，差別還是非常大的。分區(qū)技術(shù)在OLAP數(shù)據(jù)庫中很重要，這種重要主要體現(xiàn)在數(shù)據(jù)管理上，比如數(shù)據(jù)加載，可以通過分區(qū)交換的方式實現(xiàn)，備份可以通過備份分區(qū)表空間實現(xiàn)，刪除數(shù)據(jù)可

30、以通過分區(qū)進(jìn)行刪除；至于分區(qū)在性能上的影響，不能一概而論，認(rèn)為分區(qū)的性能將始終好于非分區(qū)，這個結(jié)論是不成立的，至少是片面的，我們通過以下幾種情況來分析它。1. 當(dāng)查詢的范圍正好落在某個分區(qū)的時候這時候分區(qū)的效率自然是高于沒有分區(qū)的，因為SQL在有分區(qū)的表上只掃過一個分區(qū)的數(shù)據(jù)，而對于沒有分區(qū)，需要掃描整個表，這也是大多數(shù)人認(rèn)為分區(qū)會提高性能的一個原因吧，比如下面的例子：* select count(*) from t where x<1000  call count cpu elapsed disk query current rows- - -Parse

31、1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 0 23 0 1- - -total 4 0.00 0.00 0 23 0 1 Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 55  Rows Row Source Operation- - 1 SORT AGGREGATE (cr=23 pr=0 pw=0 time=2495 us) 999 PARTITION RANGE SINGL

32、E PARTITION: 1 1 (cr=23 pr=0 pw=0 time=9085 us) 999 TABLE ACCESS FULL T PARTITION: 1 1 (cr=23 pr=0 pw=0 time=4077 us) * select count(*) from t1 where x<1000  call count cpu elapsed disk query current rows- - -Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.01 0.0

33、0 0 84 0 1- - -total 4 0.01 0.01 0 84 0 1 Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 55  Rows Row Source Operation- - 1 SORT AGGREGATE (cr=84 pr=0 pw=0 time=9015 us) 999 TABLE ACCESS FULL T1 (cr=84 pr=0 pw=0 time=4077 us) 第一個SQL只掃過了一個分區(qū)的數(shù)據(jù)，掃過的數(shù)據(jù)塊為2

34、3個；第二個SQL做了全表掃描，掃過的數(shù)據(jù)塊為84個，這種情況下肯定是分區(qū)表的效率要高一些。2. 當(dāng)查詢的范圍跨越幾個分區(qū)時這時候分區(qū)可能并不絕對是最優(yōu)的，比如下面的例子，我們把查詢的范圍擴(kuò)大到分區(qū)表的13個分區(qū)，讓CBO使用FAST INDEX FULL SCAN的方式掃描索引，另外我們創(chuàng)建另一張非分區(qū)表，表結(jié)果和數(shù)據(jù)同分區(qū)表完全一樣，我們使用同一條SQL，并且也讓CBO強(qiáng)制使用FAST INDEX FULL SCAN的方式訪問非分區(qū)表上的全局索引。我們要驗證的一個觀點是，分區(qū)索引并不一定比全局索引在任何時候都快，有時候它反而會慢。下面是輸入的結(jié)果：Select /*+ index_ffs(

35、t t_ind) */ count(*) from t where x<13000  call count cpu elapsed disk query current rows- - -Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.03 0.02 0 164 0 1- - -total 4 0.03 0.03 0 164 0 1 Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user

36、id: 55  Rows Row Source Operation- - 1 SORT AGGREGATE (cr=164 pr=0 pw=0 time=29234 us) 12999 PARTITION RANGE ALL PARTITION: 1 13 (cr=164 pr=0 pw=0 time=117074 us) 12999 INDEX FAST FULL SCAN T_IND PARTITION: 1 13 (cr=164 pr=0 pw=0 time=52408 us)(object id 51774) select /*+ index_ffs(t1 t1_i

37、nd) */ count(*) from t1 where x<13000  call count cpu elapsed disk query current rows- - -Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.03 0.02 0 117 0 1- - -total 4 0.03 0.02 0 117 0 1 Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id:

38、55  Rows Row Source Operation- - 1 SORT AGGREGATE (cr=117 pr=0 pw=0 time=24755 us) 12999 INDEX FAST FULL SCAN T1_IND (cr=117 pr=0 pw=0 time=52082 us)(object id 51788) * 在這個例子里面，分區(qū)索引之所以掃過了更多的數(shù)據(jù)塊，是因為分區(qū)索引在做FFS(INDEX FAST FULL SCAN)的時候只能夠在本地索引上進(jìn)行，如果涉及其他的分區(qū)，還需要按照訪問索引的方式去訪問其他索引（比如先找到其他分區(qū)索引的

39、根數(shù)據(jù)塊，再找到最左邊的葉塊，然后執(zhí)行FFS操作），這樣，查詢跨過的分區(qū)越多，這種額外的代價就越大；而在這種情況下，全局索引只需要定位到一個葉塊，然后執(zhí)行一次FFS就能夠掃過所有的索引葉塊，這樣性能就會好于分區(qū)索引。上面的例子是想說明，OLAP環(huán)境中，分區(qū)主要的功能是管理上的方便性，它并不能絕對保證查詢性能的提高，有時候分區(qū)會帶來性能上的提高，有時候甚至?xí)档?，就像我們在例子中看到的一樣?#160;1.3 數(shù)據(jù)庫的硬件設(shè)計數(shù)據(jù)庫的硬件設(shè)計在性能上主要體現(xiàn)在：lCPUlI/Ol       負(fù)載情況 這些指標(biāo)需要對業(yè)務(wù)進(jìn)行綜

40、合評估和系統(tǒng)測試之后，做出一個合理的硬件配置清單。數(shù)據(jù)庫的硬件設(shè)計包含了數(shù)據(jù)庫服務(wù)器的架構(gòu)和數(shù)據(jù)存儲。這些因素在數(shù)據(jù)庫設(shè)計階段將作為重點的考慮因素。如果當(dāng)系統(tǒng)上線之后，出現(xiàn)冗余或者空間不足的問題，將是一件非常麻煩的事情。數(shù)據(jù)的存儲和安全應(yīng)該主要考慮以下幾個問題。1.3.1 存儲容量如果一個系統(tǒng)的生命周期可以確定，或者說數(shù)據(jù)庫中的數(shù)據(jù)保存時間可以確定，那么我們就可以通過一個簡單的計算，大致估算出數(shù)據(jù)庫所存放的數(shù)據(jù)量的大小，以作為存儲設(shè)備采購的一個依據(jù)?？梢酝ㄟ^估算占有存儲空間的所有數(shù)據(jù)庫對象（其實主要是估算業(yè)務(wù)用戶下的所有對象）的容量，來計算數(shù)據(jù)的容量。占用空間的對象都可以在DBA_SEGMEN

41、TS視圖里面找到，數(shù)據(jù)庫的空間的分配是以段的形式分配的，凡是段對象，都是要占用空間的，它包括表、索引、物化視圖、其他的一些大對象（比如全文索引對象）。如果在開發(fā)階段能夠預(yù)測每個表的記錄數(shù)，然后我們?nèi)〉眠@個表的字段總長度，于是表的容量=記錄數(shù)*字段長度。一個表中索引的大小和索引的類型，以及索引鍵值的重復(fù)率有很大的關(guān)系，開發(fā)人員可以通過模擬一些實際數(shù)據(jù)來估算出索引和表數(shù)據(jù)的一個比例，然后做出索引所占空間的估算。一個計算容量的例子如下。我們創(chuàng)建一個表，然后在表上創(chuàng)建索引，之后對表和索引進(jìn)行分析，然后查詢視圖user_tables就可以得到表的大致容量。SQL> create table t a

42、s select * from dba_objects; Table created. SQL> create index t_ind on t(object_id); Index created. SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true); PL/SQL procedure successfully completed. SQL> select avg_row_len from user_tables whe

43、re table_name='T' AVG_ROW_LEN- 93 這個數(shù)值就是表的平均行長，如果我們能夠估算出預(yù)期表的記錄數(shù)N，那么最終表占用的空間就是 93 bytes*N：SQL> select segment_name,segment_type,bytes from user_segments where segment_name in ('T','T_IND'); SEGMENT_NAME SEGMENT_TYPE BYTES- - -T TABLE 6291456T_IND INDEX 104857

44、6 SQL> select trunc(1048576/6291456)*100) ind_pct from dual;  IND_PCT- 16 我們?nèi)〉昧藴y試表中表和索引的大小，計算出索引和表大小的百分比，這樣，我們就獲得了最終這個表的空間使用量為：表的總使用量 = 93 bytes*N(1+16) 這就是預(yù)期這個表的容量，如果能夠預(yù)測出未來數(shù)據(jù)庫的數(shù)據(jù)量，我們就可以比較客觀地估算出數(shù)據(jù)庫預(yù)期的容量大小。另外一個容易被開發(fā)商忽略的問題是對系統(tǒng)備份數(shù)據(jù)占用空間的考慮。我遇到的一些系統(tǒng)就有這個問題，系統(tǒng)上線之后才發(fā)現(xiàn)，設(shè)計人員根本就沒有考慮過系統(tǒng)備

45、份的問題，也沒有預(yù)留出足夠的空間來做數(shù)據(jù)備份，給DBA帶來的壓力相當(dāng)大。為了保證數(shù)據(jù)的安全，我設(shè)計了一套備份方案，由于磁盤的空間有限，在備份新的數(shù)據(jù)的同時，還需要及時刪除一些舊的備份數(shù)據(jù)，在這種磁盤空間捉襟見肘的空隙下工作，DBA有時候覺都不能睡安穩(wěn)。1.3.2 存儲的物理設(shè)計現(xiàn)在越來越多的大數(shù)據(jù)量數(shù)據(jù)庫選擇了SAN存儲結(jié)構(gòu)（參見圖1-2），這是一個擴(kuò)展性非常好的存儲設(shè)計，它可以非常方便地將存儲設(shè)備增加到存儲網(wǎng)絡(luò)當(dāng)中，但成本和故障點相應(yīng)地就會變多，對維護(hù)人員的技術(shù)要求就很高，它不但要求維護(hù)人員懂得磁盤陣列的技術(shù)，還要掌握SAN交換機(jī)的相關(guān)技術(shù)。 圖1-2 SAN存儲架構(gòu)1.3.3 數(shù)據(jù)的安全數(shù)

46、據(jù)的安全是系統(tǒng)設(shè)計階段應(yīng)該充分考慮好的一個問題，要按照用戶對數(shù)據(jù)安全級別要求的高低，以及運行業(yè)務(wù)停止的時長來設(shè)計數(shù)據(jù)庫的安全解決方案。大致來講，用戶對數(shù)據(jù)安全的要求有如下幾個層次。1.3.3.1 Data Guard結(jié)構(gòu)如果用戶對數(shù)據(jù)的安全性要求非常高，并且對系統(tǒng)的宕機(jī)時間要求很高，可以考慮Data Guard設(shè)計結(jié)構(gòu)（參見圖1-3），當(dāng)主數(shù)據(jù)庫出現(xiàn)故障時，維護(hù)人員可以用最短的時間啟用備用數(shù)據(jù)庫，保證業(yè)務(wù)的正常進(jìn)行。1.3.3.2 RAC結(jié)構(gòu)RAC結(jié)構(gòu)（參見圖1-4）和Data Guard結(jié)構(gòu)分屬于不同級別的安全設(shè)計，Data Guard能夠保證數(shù)據(jù)不丟失或者盡可能少丟失（注：Data Guard有三種保護(hù)模式，具體細(xì)節(jié)請參考Oracle官方文檔），它是數(shù)據(jù)庫級別的一個冗余結(jié)構(gòu)。而RAC則是實例級的一個冗余結(jié)構(gòu)，它能夠保證數(shù)據(jù)庫在一個實例出現(xiàn)故障之后，用戶操作可以無縫地由另外一個實例接管，現(xiàn)在很多對業(yè)務(wù)連續(xù)性要求很高的系統(tǒng)都采用了RAC+Data Guard的數(shù)據(jù)庫結(jié)構(gòu)設(shè)計。 圖1-3 Data Guard結(jié)構(gòu) 圖1-4 RAC結(jié)構(gòu)1.3.3.3 Rman+歸檔的方式Rman+歸檔的備份方式相對RAC+Data Guard來看，它的優(yōu)勢在于成本上要廉價，并