數(shù)據(jù)庫最基本的任務(wù)是存儲(chǔ)、管理數(shù)據(jù)，而終端用戶唯一能看到的數(shù)據(jù)庫特性就是其性能：數(shù)據(jù)庫以何速度處理某一指定查詢的結(jié)果，并且將結(jié)果返回到用戶所用的工具和應(yīng)用程序。從大多數(shù)系統(tǒng)的應(yīng)用實(shí)例來看，查詢操作在各種數(shù)據(jù)庫操作中所占據(jù)的比重最大、查閱新聞、 查看文件、 查詢統(tǒng)計(jì)信息等。因此，數(shù)據(jù)庫查詢操作的效率是影響一個(gè)應(yīng)用系統(tǒng)響應(yīng)時(shí)間的關(guān)鍵因素。隨著一個(gè)應(yīng)用系統(tǒng)中數(shù)據(jù)的動(dòng)態(tài)增長，數(shù)據(jù)量變大，數(shù)據(jù)庫查詢效率就會(huì)有所降低，應(yīng)用系統(tǒng)的響應(yīng)速度也隨之減慢，尤其對于海量數(shù)據(jù)的管理和查詢問題就更加突出，Oracle查詢優(yōu)化就顯得尤為重要。

目前通用的數(shù)據(jù)庫產(chǎn)品有很多種，其中Oracle數(shù)據(jù)庫以其支持大數(shù)據(jù)庫、多用戶的高性能事務(wù)處理， 對業(yè)界各項(xiàng)工業(yè)標(biāo)準(zhǔn)的支持，完整的安全和完整性控制，支持分布式數(shù)據(jù)庫利分布處理 具有可移植性、可兼容性和可連接性等突出優(yōu)點(diǎn)倍受用戶喜愛，應(yīng)用較為廣泛，在互聯(lián)網(wǎng)數(shù)據(jù)庫平臺(tái)上處于領(lǐng)先地位、其Spatial技術(shù)能更加有效地管理地理信息，實(shí)現(xiàn)海量空間信息的存儲(chǔ)和管理。本文結(jié)合Oracle數(shù)據(jù)庫應(yīng)用經(jīng)驗(yàn)，從命中率提高、多表查詢優(yōu)化、大表查詢優(yōu)化和SQL優(yōu)化等四個(gè)方面闡述Oracle查詢優(yōu)化的經(jīng)驗(yàn)和方法。

Oracle查詢優(yōu)化第一方面：Oracle數(shù)據(jù)查詢命中率的提高

“命中率（HITRATIO） 是指直接從內(nèi)存中取得數(shù)據(jù)而不從磁盤中取得數(shù)據(jù)的比率，也就是查詢請求的數(shù)據(jù)塊已經(jīng)在內(nèi)存中次數(shù)的百分比”。影響命中率的因素有四種：字典表活動(dòng)、臨時(shí)段活動(dòng)、回滾段活動(dòng)、表掃描， 應(yīng)用DBA可以對這四種因素進(jìn)行分析，找出數(shù)據(jù)庫命中率低的癥結(jié)所在。

1）字典表活動(dòng)

當(dāng)一個(gè)SQL語句第一次到達(dá)Oracle內(nèi)核時(shí)數(shù)據(jù)庫對SQL語句進(jìn)行分析，包含在查詢中的數(shù)據(jù)字典對象被分解，產(chǎn)生SQL執(zhí)行路徑。如果SQL語句指向一個(gè)不在SGA中的對象？？表或視圖，Oracle執(zhí)行SQL語句到數(shù)據(jù)典中查詢有關(guān)對象的信息。數(shù)據(jù)塊從數(shù)據(jù)字典表被讀取到SGA的數(shù)據(jù)緩存中。由于每個(gè)數(shù)據(jù)字典都很小，因此，我們可緩存這些表以提高對這些表的命中率。但是由于數(shù)據(jù)字典表的數(shù)據(jù)塊在SGA中占據(jù)空間，當(dāng)增加全部的命中率時(shí)，它們會(huì)降低表數(shù)據(jù)塊的可用空間， 所以若查詢所需的時(shí)間字典信息已經(jīng)在SGA緩存中，那么就沒有必要遞歸調(diào)用。

2）臨時(shí)段的活動(dòng)

當(dāng)用戶執(zhí)行一個(gè)需要排序的查詢時(shí)，Oracle設(shè)法對內(nèi)存中排序區(qū)內(nèi)的所有行進(jìn)行排序，排序區(qū)的大小由數(shù)據(jù)庫的init.ora文件的數(shù)確定。如果排序區(qū)域不夠大，數(shù)據(jù)庫就會(huì)在排序操作期間開辟臨時(shí)段。臨時(shí)段會(huì)人為地降低OLTP（online transaction processing）應(yīng)用命中率，也會(huì)降低查詢進(jìn)行排序的性能。如果能在內(nèi)存中完成全部排序操作，就可以消除向臨時(shí)段寫數(shù)據(jù)的開銷。所以應(yīng)將SORT_AREA_SIZE設(shè)置得足夠大，以避免對臨時(shí)段的需要。這個(gè)參數(shù)的具體調(diào)整方法是：查詢相關(guān)數(shù)據(jù)，以確定這個(gè)參數(shù)的調(diào)整。

select * from v$sysstat where name=‘sorts（disk）'or name=‘sorts（memory）;

大部分排序是在內(nèi)存中進(jìn)行的，但還有小部分發(fā)生在臨時(shí)段， 需要調(diào)整 值，查看init.ora文件的 SORT_AREA_SIZE值，參數(shù)為：SORT_AREA_SIZE＝65536;將其調(diào)整到SORT_AREA_SIZE＝131072、這個(gè)值調(diào)整后，重啟ORACLE數(shù)據(jù)庫即可生效。

3）回滾段的活動(dòng)

回滾段活動(dòng)分為回滾活動(dòng)和回滾段頭活動(dòng)。對回滾段頭塊的訪問會(huì)降低應(yīng)用的命中率， 對OLTP系統(tǒng)命中率的影響最大。為確認(rèn)是否因?yàn)榛貪L段影響了命中率，可以查看監(jiān)控輸出報(bào)表中的“數(shù)據(jù)塊相容性讀一重寫記錄應(yīng)用” 的統(tǒng)計(jì)值，這些統(tǒng)計(jì)值是用來確定用戶從回滾段中訪問數(shù)據(jù)的發(fā)生次數(shù)。

4）表掃描

通過大掃描讀得的塊在數(shù)據(jù)塊緩存中不會(huì)保持很長時(shí)間， 因此表掃描會(huì)降低命中率。為了避免不必要的全表掃描，首先是根據(jù)需要建立索引，合理的索引設(shè)計(jì)要建立人對各種查詢的分析和預(yù)測上，筆者會(huì)在SQL優(yōu)化中詳細(xì)談及；其次是將經(jīng)常用到的表放在內(nèi)存中，以降低磁盤讀寫次數(shù)。例如 Alter table your_table_name cathe。

Oracle查詢優(yōu)化第二方面：多表查詢的優(yōu)化

在進(jìn)行多表聯(lián)合查詢時(shí)，數(shù)據(jù)庫可能會(huì)采取MERGEJOINS、NESTED LOOP、HASH JOIN。其中，不論什么時(shí)候哈希聯(lián)結(jié)要比另兩種聯(lián)結(jié)開銷要小。

我們可以使用哈希聯(lián)結(jié)代替MERGEJOINS、NESTED LOOP聯(lián)結(jié)、因此，在應(yīng)用中，可添加一些設(shè)置使得數(shù)據(jù)庫在有多大聯(lián)合查詢發(fā)生時(shí)使用哈希聯(lián)結(jié)。其方法是：以 oracle用戶身份登錄數(shù)據(jù)庫服務(wù)器，在initosid.ora文件中添加：

HASH_JOIN_ENABLED=TRUE

HASJ_AREA_SIZE=26000

修改完后，重新啟動(dòng)數(shù)據(jù)庫，使這些參數(shù)值生效。

Oracle查詢優(yōu)化第三方面：大表查詢優(yōu)化

數(shù)據(jù)庫中有些表是增長非常快的，記錄量很大，對這種表進(jìn)行訪問時(shí)，索引的好處就微乎其微了，通常采用兩種辦法來進(jìn)行大表訪問的優(yōu)化。

1）大表建立在哈希簇中

create cluster TRADE_CLUSTER（vuserid integer）

storage（initial 50M next 50M）

hash is vuserid

size 60 hashkeys 10000000;/*hashkeys指定了在哈希表里的所期望的行數(shù)。*/ create table

trade_detail_new as select * from trade_detail cluster

TRADE_CLUSTER（userid）;

drop table trade_detail;

rename trade_detail_new to trade_detail;

2）建分區(qū)表

將一個(gè)大表分開放置在幾個(gè)邏輯分區(qū)中或者是將一個(gè)大表分成了幾張小表 ，即可以單獨(dú)對這些小表進(jìn)行查詢，也可以union all一起查詢。

例如：將 一個(gè)記錄交易詳情的表拆分：

create trade_detail_1 as select * from trade_detail

where trade_time between to_date（'mm-dd‘，'01-01‘）and to_date（'mm-dd‘，'03-31‘）;

alter table trade_detail_1 add constraint check_trade_detail_1

check （trade_time between to_date（'mm-dd‘，'01-01‘）and to_date（'mm-dd‘，'03-31‘））;

同樣，建立起另幾張按交易發(fā)生的季度而劃分的表。然后創(chuàng)建執(zhí)行四個(gè)表聯(lián)合的視圖；

create view trade_detail as select * from trade_detail_1

union all select * from trade_detail_2

union all select * from trade_detail_3

union all select * from trade_detail_4;

這樣在查詢某段時(shí)間內(nèi)的數(shù)據(jù)時(shí)只訪問小表就可以了，需要時(shí)也可進(jìn)行聯(lián)合查詢。

Oracle查詢優(yōu)化第四方面：SQL優(yōu)化

應(yīng)用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫中的SQL語句執(zhí)行，SQL語句消耗了70%到90%的數(shù)據(jù)庫資源。因此SQL語句的執(zhí)行效率最終決定了ORACLE數(shù)據(jù)庫的性能。許多程序員認(rèn)為查詢優(yōu)化是DBMS（數(shù)據(jù)庫管理系統(tǒng)）的任務(wù)，與程序員所編寫的SQL語句關(guān)系不大，這是錯(cuò)誤的。一個(gè)好的查詢計(jì)劃往往可以使程序性能提高數(shù)十倍。另外，SQL語句獨(dú)立于程序設(shè)計(jì)邏輯，相對于對程序源代碼的優(yōu)化，對SQL語句的優(yōu)化在時(shí)間成本和風(fēng)險(xiǎn)上的代價(jià)都很低。

SQL優(yōu)化的主要途徑是：

a.有效索引的建立。在經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的列上建立索引；在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roup by 或 order by 操作）的列上建立索引；在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引；如果待排序的列有多個(gè)，可以在這些列上建立復(fù)合索引（compound index）。

為了降低I/O竟?fàn)帲?索引要建在與用戶表空間不在同一磁盤上的索引空間里。索引分為：分區(qū)索引、完全索引、唯一索引、位圖索引等幾種類型，在建立索引前，應(yīng)該測量這個(gè)索引的選擇性，索引的選擇性是指索引列里不同值的數(shù)目與表中記錄數(shù)的比。

b.在有大量重復(fù)值并且經(jīng)常有范圍查詢（例如 between，》，《》=，《=）的列，或是用到order by、group by的列，可考慮建立群集索引 ；

c.要經(jīng)常同時(shí)存取多列，目每列都含有重復(fù)值可考慮建立組合索引

d.優(yōu)化表達(dá)式，在能使用范圍查詢時(shí)盡可能使用范圍索引， 而少用“l(fā)ike”，因?yàn)椤癓IKE”關(guān)鍵字支持的通配符匹配特別耗費(fèi)時(shí)間。

f.使用Oracle語句優(yōu)化器（oracle optimizer）和行鎖管理器（row-level manager）來調(diào)整優(yōu)化SQL語句。