數據庫中Oracle查詢(xún)優(yōu)化4大方面的主要途徑
摘要：數據庫的優(yōu)化是個(gè)細致的工作，Oracle查詢(xún)優(yōu)化只是其中的一個(gè)部分，亦如何提高查詢(xún)效率這個(gè)問(wèn)題上，本文作者通過(guò)對Oracle數據庫應用的實(shí)踐經(jīng)驗 ，總結了四個(gè)方面來(lái)優(yōu)化查詢(xún)：從命中率提高、多表查詢(xún)優(yōu)化、大表查詢(xún)優(yōu)化和SQL優(yōu)化。
數據庫最基本的任務(wù)是存儲、管理數據，而終端用戶(hù)唯一能看到的數據庫特性就是其性能：數據庫以何速度處理某一指定查詢(xún)的結果，并且將結果返回到用戶(hù)所用的工具和應用程序。從大多數系統的應用實(shí)例來(lái)看，查詢(xún)操作在各種數據庫操作中所占據的比重最大、查閱新聞、 查看文件、 查詢(xún)統計信息等。因此，數據庫查詢(xún)操作的效率是影響一個(gè)應用系統響應時(shí)間的關(guān)鍵因素。隨著(zhù)一個(gè)應用系統中數據的動(dòng)態(tài)增長(cháng)，數據量變大，數據庫查詢(xún)效率就會(huì )有所降低，應用系統的響應速度也隨之減慢，尤其對于海量數據的管理和查詢(xún)問(wèn)題就更加突出，Oracle查詢(xún)優(yōu)化就顯得尤為重要。
目前通用的數據庫產(chǎn)品有很多種，其中Oracle數據庫以其支持大數據庫、多用戶(hù)的高性能事務(wù)處理, 對業(yè)界各項工業(yè)標準的支持,完整的安全和完整性控制，支持分布式數據庫利分布處理 具有可移植性、可兼容性和可連接性等突出優(yōu)點(diǎn)倍受用戶(hù)喜愛(ài)，應用較為廣泛，在互聯(lián)網(wǎng)數據庫平臺上處于領(lǐng)先地位、其Spatial技術(shù)能更加有效地管理地理信息，實(shí)現海量空間信息的存儲和管理。本文結合Oracle數據庫應用經(jīng)驗，從命中率提高、多表查詢(xún)優(yōu)化、大表查詢(xún)優(yōu)化和SQL優(yōu)化等四個(gè)方面闡述Oracle查詢(xún)優(yōu)化的經(jīng)驗和方法。
Oracle查詢(xún)優(yōu)化第一方面：Oracle數據查詢(xún)命中率的提高
“命中率（HITRATIO） 是指直接從內存中取得數據而不從磁盤(pán)中取得數據的比率，也就是查詢(xún)請求的數據塊已經(jīng)在內存中次數的百分比”。影響命中率的因素有四種：字典表活動(dòng)、臨時(shí)段活動(dòng)、回滾段活動(dòng)、表掃描， 應用DBA可以對這四種因素進(jìn)行分析，找出數據庫命中率低的癥結所在。
1)字典表活動(dòng)
當一個(gè)SQL語(yǔ)句第一次到達Oracle內核時(shí)數據庫對SQL語(yǔ)句進(jìn)行分析，包含在查詢(xún)中的數據字典對象被分解，產(chǎn)生SQL執行路徑。如果SQL語(yǔ)句指向一個(gè)不在SGA中的對象??表或視圖，Oracle執行SQL語(yǔ)句到數據典中查詢(xún)有關(guān)對象的信息。數據塊從數據字典表被讀取到SGA的數據緩存中。由于每個(gè)數據字典都很小，因此，我們可緩存這些表以提高對這些表的命中率。但是由于數據字典表的數據塊在SGA中占據空間，當增加全部的命中率時(shí)，它們會(huì )降低表數據塊的可用空間， 所以若查詢(xún)所需的時(shí)間字典信息已經(jīng)在SGA緩存中，那么就沒(méi)有必要遞歸調用。
2)臨時(shí)段的活動(dòng)
當用戶(hù)執行一個(gè)需要排序的查詢(xún)時(shí)，Oracle設法對內存中排序區內的所有行進(jìn)行排序，排序區的大小由數據庫的init.ora文件的數確定。如果排序區域不夠大，數據庫就會(huì )在排序操作期間開(kāi)辟臨時(shí)段。臨時(shí)段會(huì )人為地降低OLTP(online transaction processing）應用命中率,也會(huì )降低查詢(xún)進(jìn)行排序的性能。如果能在內存中完成全部排序操作，就可以消除向臨時(shí)段寫(xiě)數據的開(kāi)銷(xiāo)。所以應將SORT_AREA_SIZE設置得足夠大，以避免對臨時(shí)段的需要。這個(gè)參數的具體調整方法是:查詢(xún)相關(guān)數據，以確定這個(gè)參數的調整。
select * from v$sysstat where name='sorts(disk)'or name='sorts(memory);
大部分排序是在內存中進(jìn)行的，但還有小部分發(fā)生在臨時(shí)段， 需要調整 值，查看init.ora文件的 SORT_AREA_SIZE值，參數為：SORT_AREA_SIZE＝65536;將其調整到SORT_AREA_SIZE＝131072、這個(gè)值調整后，重啟ORACLE數據庫即可生效。
3)回滾段的活動(dòng)
回滾段活動(dòng)分為回滾活動(dòng)和回滾段頭活動(dòng)。對回滾段頭塊的訪(fǎng)問(wèn)會(huì )降低應用的命中率， 對OLTP系統命中率的影響最大。為確認是否因為回滾段影響了命中率，可以查看監控輸出報表中的“數據塊相容性讀一重寫(xiě)記錄應用” 的統計值，這些統計值是用來(lái)確定用戶(hù)從回滾段中訪(fǎng)問(wèn)數據的發(fā)生次數。
4)表掃描
通過(guò)大掃描讀得的塊在數據塊緩存中不會(huì )保持很長(cháng)時(shí)間， 因此表掃描會(huì )降低命中率。為了避免不必要的全表掃描，首先是根據需要建立索引，合理的索引設計要建立人對各種查詢(xún)的分析和預測上，筆者會(huì )在SQL優(yōu)化中詳細談及；其次是將經(jīng)常用到的表放在內存中，以降低磁盤(pán)讀寫(xiě)次數。例如 Alter table your_table_name cathe。
Oracle查詢(xún)優(yōu)化第二方面：多表查詢(xún)的優(yōu)化
在進(jìn)行多表聯(lián)合查詢(xún)時(shí)，數據庫可能會(huì )采取MERGEJOINS、NESTED LOOP、HASH JOIN。其中，不論什么時(shí)候哈希聯(lián)結要比另兩種聯(lián)結開(kāi)銷(xiāo)要小。
我們可以使用哈希聯(lián)結代替MERGEJOINS、NESTED LOOP聯(lián)結、因此，在應用中，可添加一些設置使得數據庫在有多大聯(lián)合查詢(xún)發(fā)生時(shí)使用哈希聯(lián)結。其方法是:以 oracle用戶(hù)身份登錄數據庫服務(wù)器，在initosid.ora文件中添加:
HASH_JOIN_ENABLED=TRUE
HASJ_AREA_SIZE=26000
修改完后,重新啟動(dòng)數據庫,使這些參數值生效。
Oracle查詢(xún)優(yōu)化第三方面：大表查詢(xún)優(yōu)化
數據庫中有些表是增長(cháng)非�？斓�，記錄量很大，對這種表進(jìn)行訪(fǎng)問(wèn)時(shí)，索引的好處就微乎其微了，通常采用兩種辦法來(lái)進(jìn)行大表訪(fǎng)問(wèn)的優(yōu)化。
1)大表建立在哈希簇中
create cluster TRADE_CLUSTER(vuserid integer)
storage(initial 50M next 50M)
hash is vuserid
size 60 hashkeys 10000000;/*hashkeys指定了在哈希表里的所期望的行數。*/ 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)建分區表
將一個(gè)大表分開(kāi)放置在幾個(gè)邏輯分區中或者是將一個(gè)大表分成了幾張小表 ，即可以單獨對這些小表進(jìn)行查詢(xún)，也可以union all一起查詢(xún)。
例如：將 一個(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 )建執行四個(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;
這樣在查詢(xún)某段時(shí)間內的數據時(shí)只訪(fǎng)問(wèn)小表就可以了，需要時(shí)也可進(jìn)行聯(lián)合查詢(xún)。
Oracle查詢(xún)優(yōu)化第四方面：SQL優(yōu)化
應用程序的執行最終將歸結為數據庫中的SQL語(yǔ)句執行，SQL語(yǔ)句消耗了70%到90%的數據庫資源。因此SQL語(yǔ)句的執行效率最終決定了ORACLE數據庫的性能。許多程序員認為查詢(xún)優(yōu)化是DBMS（數據庫管理系統）的任務(wù)，與程序員所編寫(xiě)的SQL語(yǔ)句關(guān)系不大，這是錯誤的。一個(gè)好的查詢(xún)計劃往往可以使程序性能提高數十倍。另外，SQL語(yǔ)句獨立于程序設計邏輯，相對于對程序源代碼的優(yōu)化，對SQL語(yǔ)句的優(yōu)化在時(shí)間成本和風(fēng)險上的代價(jià)都很低。
SQL優(yōu)化的主要途徑是：
a.有效索引的建立。在經(jīng)常進(jìn)行連接，但是沒(méi)有指定為外鍵的列上建立索引；在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roup by 或 order by 操作）的列上建立索引；在條件表達式中經(jīng)常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引；如果待排序的列有多個(gè)，可以在這些列上建立復合索引（compound index）。
為了降低I/O竟爭, 索引要建在與用戶(hù)表空間不在同一磁盤(pán)上的索引空間里。索引分為：分區索引、完全索引、唯一索引、位圖索引等幾種類(lèi)型，在建立索引前，應該測量這個(gè)索引的選擇性，索引的選擇性是指索引列里不同值的數目與表中記錄數的比。
b.在有大量重復值并且經(jīng)常有范圍查詢(xún)（例如 between,>,<>=,<=）的列,或是用到order by、group by的列,可考慮建立群集索引 ；
c.要經(jīng)常同時(shí)存取多列,目每列都含有重復值可考慮建立組合索引
d.優(yōu)化表達式，在能使用范圍查詢(xún)時(shí)盡可能使用范圍索引， 而少用“l(fā)ike”,因為“LIKE”關(guān)鍵字支持的通配符匹配特別耗費時(shí)間。
f.使用Oracle語(yǔ)句優(yōu)化器（oracle optimizer）和行鎖管理器（row-level manager）來(lái)調整優(yōu)化SQL語(yǔ)句。
文章來(lái)源于領(lǐng)測軟件測試網(wǎng) http://kjueaiud.com/
