基于ORACLE的查詢(xún)優(yōu)化

科技信息博士●專(zhuān)家論壇基于ORACLE的查詢(xún)優(yōu)化寧波大紅鷹職業(yè)技術(shù)學(xué)院唐興通危烽吉林大學(xué)軟件學(xué)院危明摘要]Oracle數據庫系統性能優(yōu)化是一個(gè)復雜的系統工程。本文論述了通過(guò)Oracle數據庫查詢(xún)的工作機制,并分析了-些影響應用性能的可能因素,主要從SQL語(yǔ)句的方面優(yōu)化進(jìn)行闡述,給出了--些可行的調整數據庫應用性能的策略與方法。[關(guān)鍵詞]ORCLE數據庫優(yōu)化 索引RDBMSORACLE是一個(gè)優(yōu)秀的大型關(guān)系型數據庫,支持關(guān)系對象下來(lái)對輸出的SQL語(yǔ)句進(jìn)行過(guò)濾,選出具有不同執行計劃的模型的分布式數據庫產(chǎn)品。目前在銀行.電信、證券市場(chǎng)的數據SQL語(yǔ)句(不同的執行計劃意味著(zhù)不同的執行效率),最后,對庫方面占有絕對的優(yōu)勢。具權威部門(mén)的統計,在全世界的范圍得到的SQL語(yǔ)句進(jìn)行批量測試,找出性能最好的SQL語(yǔ)句內,ORACLE市場(chǎng)占有率為46. 1%是SQLSERVER的7倍,而ORACLE的優(yōu)化器共有3種: a. RULE (基于規則) bIBM的DB2市場(chǎng)占有率也只有23.6%。一個(gè)選用ORACLE為COST (基于成本) c. CHOOSE (選擇性).數據庫的系統的規模一般比較大,用戶(hù)-般比較多而且空間上基于開(kāi)銷(xiāo)在基于開(kāi)銷(xiāo)的優(yōu)化策略中(CBO),對于給定查詢(xún)比較分離,事務(wù)處理的效率對于這樣的系統來(lái)說(shuō)是至關(guān)重要的,生成多個(gè)執行計劃,然后對每個(gè)計劃估算開(kāi)銷(xiāo)查詢(xún)優(yōu)化程序選但是在實(shí)際應用中,隨著(zhù)系統數據庫中數據的增加,訪(fǎng)問(wèn)量的加用估算開(kāi)銷(xiāo)最低的計劃,(代價(jià)= I/O代價(jià)+ CPU代價(jià)+內大，數據庫系統性能將會(huì )下降。這樣,就需要我們數據庫管理人存代價(jià))。員對數據庫進(jìn)行性能調整,使數據庫系統能夠在合理、可靠、低另外基于規則的優(yōu)化程序(RBO)不同于基于開(kāi)銷(xiāo)的優(yōu)化程風(fēng)險和高效率的狀態(tài)下運行。序(CSO)。它按照-系列既成的規則來(lái)對SQL語(yǔ)句進(jìn)行分析，數據庫應用的性能調優(yōu)是一個(gè)系統工程。在實(shí)踐中必須分從而得出較好的執行策略。RBO在分析過(guò)程中不考慮開(kāi)銷(xiāo)因析影響性能的各方面因素.針對不同的具體情況選擇合理的優(yōu)素。實(shí)際應用中-般不考慮化措施,對于ORACLE來(lái)說(shuō)性能的優(yōu)化主要有:內存分配的優(yōu)數據庫的反應速度經(jīng)常體現在優(yōu)化器的優(yōu)化算法上。此外，化,1/O的調整,SQL語(yǔ)句的優(yōu)化與調整以及數據庫各種動(dòng)態(tài)參優(yōu)化器的優(yōu)化策略還與所查詢(xún)表的內容和其他一些與服務(wù)器有數的調整。本文主要探討有關(guān)SQL語(yǔ)句的優(yōu)化與調整的內容。關(guān)的因素有關(guān),如Cache大小.Cache策略.I/O大小等。通常來(lái).數據庫性能優(yōu)化目標就是使數據庫應用運行得更快?！案f(shuō)硬盤(pán)訪(fǎng)問(wèn)是成本最高的操作?？臁蓖ǔＶ傅氖歉叩耐掏铝炕蛘哒f(shuō)是更短的應用響應時(shí)間。數2.數據庫性能優(yōu)化目標就是使數據庫應用運行得更快據庫性能優(yōu)化的基本原則是通過(guò)盡可能少的磁盤(pán)訪(fǎng)問(wèn)獲得所需“更快”通常指的是更高的吞吐量或者說(shuō)是更短的應用響應要的數據。時(shí)間。SQL語(yǔ)句優(yōu)化的要點(diǎn)，就是盡量減少頁(yè)面存取，或使用內實(shí)際數據庫使用過(guò)程中大部分的性能問(wèn)題是由于程序員或存的頁(yè)面代替訪(fǎng)問(wèn)磁盤(pán)。在查詢(xún)優(yōu)化過(guò)程中要對語(yǔ)法樹(shù)中的每用戶(hù)使用了不恰當的查詢(xún)語(yǔ)句造成的,甚至達到80%以上的問(wèn).一個(gè)操作,計算各種執行算法的執行代價(jià)，選擇代價(jià)小的執行算題,因此SQL語(yǔ)句的質(zhì)量對整個(gè)系統效率有相當影響。SQL語(yǔ)法。其中就要涉及到從關(guān)系代數方面的一些優(yōu)化規律:句優(yōu)化的要點(diǎn)，就是盡量減少頁(yè)面存取,或使用內存的頁(yè)面代替(1)選擇操作是根據條件對關(guān)系做水平分割,即選取符合條訪(fǎng)問(wèn)磁盤(pán)。件的元組。應盡可能早地執行選擇操作,以得到較小的中間關(guān)我們要想很好的處理好查詢(xún)優(yōu)化,可以借鑒一些目前商品系,這樣可以減少運算量和讀外存的次數化的優(yōu)化器中,它們是如何進(jìn)行處理的，這樣我們在用戶(hù)書(shū)寫(xiě)查(2)把笛卡爾積和選擇操作合并成F聯(lián)接運算,也就是做詢(xún)時(shí)就有個(gè)好的思路和方法。笛卡爾積操作時(shí)立即檢查聯(lián)接后的元組是否滿(mǎn)足選擇條件,并.1. RDBMS優(yōu)化器決定取舍,以減少系統開(kāi)銷(xiāo)。隨著(zhù)人工智能技術(shù)的發(fā)展和在數據庫優(yōu)化領(lǐng)域應用的深(3)選擇和投影都是--元操作符,而關(guān)系的元組是獨立的單入,在20世紀90年代末優(yōu)化技術(shù)取得了突破性的進(jìn)展,出現了位,可以對每個(gè)元組連續做-串操作,即同時(shí)計算-連串的選擇人工智能自動(dòng)SQL優(yōu)化。人工智能自動(dòng)SQL優(yōu)化的本質(zhì)就是和投影操作,以免多次掃描文件,從而節省時(shí)間。借助人工智能技術(shù),自動(dòng)對SQL語(yǔ)句進(jìn)行重寫(xiě),找到性能最好(4)如果在-個(gè)表達式中多次出現-個(gè)子表達式,應該將此的等效SQL語(yǔ)句。像LECCO SQL Expert就采用了這種人工子表達式預先計算出結果并保存起來(lái),以免重復計算。智能技術(shù),其SQL Expert支持Oracle .Sybase、MS SQL Server(5)根據具體情況對文件進(jìn)行排序或建立索引文件,即對關(guān)和IBMDB2數據庫平臺。其突出特點(diǎn)是自動(dòng)優(yōu)化SQL語(yǔ)句。能.系文件進(jìn)行預處理,可以提高關(guān)系聯(lián)接的速度。通過(guò)比較源SQL和待選SQL的不同之處,為開(kāi)發(fā)人員提供“邊(6)當查詢(xún)涉及多個(gè)關(guān)系時(shí)最好使用嵌套結構,因為嵌套查做邊學(xué)式訓練”,迅速提高開(kāi)發(fā)人員的SQL編程技能等。詢(xún)的執行效率比聯(lián)接查詢(xún)的笛卡爾積的效率高?！癝QL 語(yǔ)法優(yōu)化器”的工作原理大致如下:輸入一條初始中國煤化工意的問(wèn)題SQL語(yǔ)句，“人工智能反饋式搜索引擎"對輸入的SQL語(yǔ)句結最有效的方法之一合檢測到的數據庫結構和索引進(jìn)行重寫(xiě),產(chǎn)生N條等效的SQLMYHCNMH G校,是優(yōu)化的基礎,建立索引語(yǔ)句輸出,產(chǎn)生的N條等效SQL語(yǔ)句再送入“人工智能反饋式的根本目的是提高查詢(xún)效率。不論怎樣優(yōu)化都會(huì )充分利用索引搜索引擎”進(jìn)行重寫(xiě),直至無(wú)法產(chǎn)生新的輸出或搜索限額滿(mǎn),接進(jìn)行記錄定位,減少磁盤(pán)的讀寫(xiě)次數,從而達到提高查詢(xún)速度的基金項目:本文獲得寧波大紅鷹職業(yè)技術(shù)學(xué)院軟件學(xué)院的青年基金的贊助。(06-2070821高維數據的聚類(lèi)分析)-7-科技信息博士●專(zhuān)家論壇目的。-個(gè)建有合理索引的數據庫應用系統可能比一個(gè)沒(méi)有建數據,而不是讓用戶(hù)繼續等待。立索引的數據庫應用系統效率高幾+倍,但并不是索引越多越3.6使用顯式的游標(Cursors)好，在那些經(jīng)常需要修改的數據列上建立索引,將導致索引B*使用隱式的游標將會(huì )執行兩次操作:第1次檢索記錄;第2樹(shù)的不斷重組,造成系統性能的下降和存儲空間的浪費。次檢查T(mén)OOMANYROWS這個(gè)EXCEPTION。而顯式游標不.(1)應對經(jīng)常出現在檢索條件中的數據域建立索引,如果執行第2次操作,速度提高。某些數據域經(jīng)常-起出現在檢索條件中,那么建立復合索引。3.7 LIKE關(guān)鍵字的處理(2)在頻繁進(jìn)行排序或分組(即進(jìn)行g(shù)roup by或order by例如:學(xué)生表中要查詢(xún)2005級學(xué)生的情況: SELECT:操作)的列上建立索引。應對經(jīng)常用于表連接操作中的數據域FROM學(xué)生WHERE XH LIKE“05___” 即使在xh字段建立索引,如果是多域連接則建立復合索引。上建立了索引,在這種情況下也還是采用順序掃描的方式。如果(3)在條件表達式中經(jīng)常用到的不同值較多的列上建立索把語(yǔ)句改為SELECT * FROM學(xué)生WHERE XH >“05000”引,在不同值少的列上不要建立索引。比如在雇員表的“性別”列在執行查詢(xún)時(shí)就會(huì )利用索引來(lái)查詢(xún),顯然會(huì )大大提高速度。上只有“男”與“女"兩個(gè)不同值,因此就沒(méi)有必要建立索引。如果3.8使用連接,避免對數據表的順序存取建立索引不但不會(huì )提高查詢(xún)效率,反而會(huì )嚴重降低更新速度。在嵌套查詢(xún)中,對表的順序存取可能會(huì )采用順序存取來(lái)進(jìn)(4)對于復合索引,索引中的數據域的順序要和實(shí)際應用的行,我們可以使用并集來(lái)避免順序存取。雖然在所有的檢查列上檢索和連接順序保持一致。都有索引,下面的查詢(xún)將對學(xué)生表執行順序操作:SELECT*(5)不要在索引中包含經(jīng)常修改、增加和刪除的列。FROM學(xué)生WHERE (TSH = 00810 AND XH> 05000) OR(6)ORACLE在外鍵上并不建立索引,因此在利用外鍵連XH=04008雖然在TSH和XH上建有索引,但是這語(yǔ)句中仍接的表格中，外鍵上也應該建立索引。然使用順序存取路徑掃描整個(gè)表。如果改為:SELECT(7)很少在where子句中出現的列,不宜建立索引。FROM學(xué)生WHERE TSH = 00810 AND XH> 05000 UNION(8)不能用null作索引,任何包含null值的列都將不會(huì )被SELECT*FROM學(xué)生WHEREXH=04008,可以避免順序包含在索引中。也就是說(shuō)如果某列存在空值,即使對該列建索引存取,是高效的。也不會(huì )提高查詢(xún)效率。3.9SELECT子句中避免使用.(9)對查詢(xún)型的表,建立多個(gè)索引會(huì )大大提高查詢(xún)速度,對如果我們要選取表中所有的列，常用的方法就是使用* ,其更新型的表,如果索引過(guò)多,會(huì )增大開(kāi)銷(xiāo)。實(shí)這樣的操作是低效果的，日常使用過(guò)程中如果表中的列沒(méi)有ORACLE采用兩種訪(fǎng)問(wèn)表中記錄的方式:1.全表掃描,全幾個(gè),最好把這幾個(gè)字段直接寫(xiě)出來(lái)。表掃描就是順序地訪(fǎng)問(wèn)表中每條記錄。ORACLE采用一次讀結束語(yǔ)入多個(gè)數據塊(database block)的方式優(yōu)化全表掃描。在海量數以上只是一些常用的優(yōu)化ORACLE數據查詢(xún)的方法,其據庫中要避免。2.通過(guò)ROWID訪(fǎng)問(wèn)表,你可以采用基于他還有好多像對數據表的切割，使用存儲過(guò)程,避免不兼容數據ROWID的訪(fǎng)問(wèn)方式情況.提高訪(fǎng)問(wèn)表的效率, ROWID包含了的使用等等?？傮w說(shuō)來(lái)ORACLE數據庫的優(yōu)化是一個(gè)動(dòng)態(tài),系表中記錄的物理位置信息。ORACLE采用索引(INDEX)實(shí)現統的過(guò)程。僅僅調整某一方面,不考慮整體情況很難收到預期的了數據和存放數據的物理位置(ROWID)之間的聯(lián)系。通常索引效果。對于查詢(xún)是這樣，對于整個(gè)系統也是同樣的道理。因此,對提供了快速訪(fǎng)問(wèn)ROWID的方法,因此那些基于索引列的查詢(xún)ORACLE相關(guān)的查詢(xún)調整和優(yōu)化,需要我們不斷總結經(jīng)驗還得就可以得到性能上的提高。結合特定的系統來(lái)進(jìn)行操作。3.2在WHERE子句中使用子查詢(xún)時(shí)盡量使用EXISTS而參考文獻不是IN。[1]William G Page等著(zhù);王磊,蔣蕊,王眾等譯. Oracle 8/在使用EXIST中，DBMS系統會(huì )首先檢查主查詢(xún),然后運8i開(kāi)發(fā)使用手冊[M].北京:機械工業(yè)出版社,2000.行子查詢(xún)直到找到第-個(gè)匹配項,這就節省了時(shí)間。DBMS系統[2]Marlene Theriault, Rachel Carmichael. Oracle 數據庫在執行IN子查詢(xún)時(shí),首先執行子查詢(xún),并將獲得的結果列表存管理員基礎教程[M].北京:機械土業(yè)出版社,2000.放在一個(gè)加了索引的臨時(shí)表中。待子查詢(xún)執行完畢,存放在臨時(shí)[3]George KOCh、 Keviri Loney等著(zhù);梅剛等譯. Oracle8表中以后再執行主查詢(xún)。這也就是使用EXISTS比使用IN通常完全參考手冊(四)[M].北京:機械工業(yè)出版社,1998..查詢(xún)速度快的原因。[4]Singh, Leigh, Zafianet al等著(zhù);史森、夏麗麗等譯. Or-3.3盡量多使用COMMITacle數據庫開(kāi)發(fā)指南[M].北京:清華大學(xué)出版社,1998.在自動(dòng)事務(wù)提交模式下,每執行一個(gè)SQL語(yǔ)句就進(jìn)行一次[5]翁正科,王新英. racle8.X For Windows NT實(shí)用教程事務(wù)提交,程序的開(kāi)銷(xiāo)很大?？梢酝ㄟ^(guò)取消自動(dòng)提交模式,將[M].北京:清華大學(xué)出版社,1998.SQL語(yǔ)句匯集成-組執行后集中提交,程序還可以通過(guò)顯式地[6]Jonathan Gennick , Carol McCullough - Dieter, Gerrit .用COMMIT和ROLLBACK進(jìn)行提交和回滾事務(wù)。只要有可一Jan Linker著(zhù)、趙艷勤,劉冠英,秦玉杰等譯. ORACLE 8i能,在程序中盡量多使用COMMIT,這樣程序的性能得到提DBA寶典[M].北京:電子工業(yè)出版社,2001.高，需求也會(huì )因為COMMIT所釋放的資源而減少.COMMIT[7]Colbum Rafe. SQL使用全書(shū)[M].北京:電子工業(yè)出版所釋放的資源: a.回滾段上用于恢復數據的信息. b. 被程序社,200芐于Oracle數據庫性能優(yōu)化中國煤化工語(yǔ)句獲得的鎖c. redo log buffer中的空間d. ORACLE為管理的研究YH上述3種資源中的內部花費。c N M H G軍優(yōu)化設計案[J].計算機系3.4用TRUNCATE替代DELETE .統應用,2001.(5):16- 18.3.5檢索大量數據時(shí)費時(shí)很長(cháng),設置行預取數則能改善系.[9][美JAbraham Silberschatz, Henry F. Korth, S. Sudar-統的工作表現,設置一個(gè)最大值,當SQL語(yǔ)句返回行超過(guò)該值，shan.數據庫系統概念.楊冬青,唐世渭等譯.北京:中國機械工數據庫暫時(shí)停止執行,除非用戶(hù)發(fā)出新的指令,開(kāi)始組織并顯示業(yè)出版社,2000.--8