Oracle數(shù)據(jù)庫查詢優(yōu)化方法研究

羅雨

遼寧錦州渤海大學(xué)信息科學(xué)與技術(shù)學(xué)院

Oracle數(shù)據(jù)庫查詢優(yōu)化方法研究

羅雨

遼寧錦州渤海大學(xué)信息科學(xué)與技術(shù)學(xué)院

現(xiàn)今，Oracle數(shù)據(jù)庫在很多的領(lǐng)域中都得到了廣泛的應(yīng)用，并且在目前數(shù)據(jù)庫市場(chǎng)以及企事業(yè)信息化系統(tǒng)的起到了重大的作用。在許多的信息化系統(tǒng)中首先選擇的產(chǎn)品是Oracle數(shù)據(jù)庫。隨著因特網(wǎng)的飛速發(fā)展，很多的信息的形式以電子化和數(shù)字化出現(xiàn)，而且這些信息的存儲(chǔ)通常采用的是Oracle數(shù)據(jù)庫。在現(xiàn)實(shí)的許多應(yīng)用系統(tǒng)中，數(shù)據(jù)查詢的速度效率如何都直接對(duì)用戶評(píng)價(jià)系統(tǒng)有著重要的影響。

Oracle 數(shù)據(jù)庫 優(yōu)化

1 Oracle數(shù)據(jù)查詢語句執(zhí)行過程

1.1 SQL查詢語句的執(zhí)行過程

在Oracle中SQL語句的執(zhí)行是自動(dòng)的，大部分的用戶不會(huì)關(guān)注每一個(gè)階段的具體執(zhí)行細(xì)節(jié)。然而，充分地了解到執(zhí)行的每一個(gè)階段可以有利于提高找到低性能的SQL語句的效率，并且可以增強(qiáng)書寫高效SQL語句的效率，從而讓問題得到解決。每一個(gè)SQL語句所包括的有分析語法、執(zhí)行語句以及數(shù)據(jù)讀取等。

1.2 Oracle查詢優(yōu)化器

作為非過程化的語言，在SQL語句的執(zhí)行中用戶輸入取數(shù)據(jù)的指令，關(guān)于取數(shù)據(jù)的方式包括了索引取數(shù)據(jù)以及全表掃描取數(shù)據(jù)，而這些都是數(shù)據(jù)庫內(nèi)部的優(yōu)化器決定的。Oracle優(yōu)化器的目的主要是為了訪問SQL數(shù)據(jù)的有效路徑，這樣能夠大大地降低語句執(zhí)行過程中的花費(fèi)的開銷。在Oracle發(fā)展中，優(yōu)化器主要包括了兩類，分別是基于成本以及基于規(guī)則的優(yōu)化器。這兩者的最大區(qū)別就是代價(jià)的衡量方法不一樣。

1.2.1 基于成本優(yōu)化器

基于成本的優(yōu)化方法指的是選擇一個(gè)集成數(shù)據(jù)庫內(nèi)核里面的成本估算器從而估算出在整個(gè)執(zhí)行計(jì)劃中所有需要投入的資金成本，其中成本的大小主要是取決于SQL語句執(zhí)行所用的資源以及SQL語句執(zhí)行所采取的時(shí)間。通過查詢優(yōu)化器從而將成本花銷最小的執(zhí)行計(jì)劃選出來。采用基于成本優(yōu)化器，基礎(chǔ)數(shù)據(jù)是選擇的分析數(shù)據(jù)是帶著表以及索引，這些分析數(shù)據(jù)的采集所用到的命令是ANALYZE。關(guān)于Oracle 9i，當(dāng)統(tǒng)計(jì)數(shù)據(jù)不存在的時(shí)候，就用到缺省值，從而會(huì)造成在選擇優(yōu)化器方面的錯(cuò)誤執(zhí)行計(jì)劃，這樣會(huì)給SQL語句查詢的性能帶來負(fù)面的影響。在OraclelOg之后，數(shù)據(jù)的分析就逐漸演變?yōu)樽詣?dòng)數(shù)據(jù)，這樣會(huì)大大地減輕DBA的負(fù)荷，然而在一些特定的環(huán)境下，可能要采取相關(guān)的手工分析。隨著Oracle數(shù)據(jù)庫的不斷發(fā)展，Oracle的版本也逐漸的更新，基于成本優(yōu)化器的發(fā)展也得到了很大的提高。

2 數(shù)據(jù)查詢語句的運(yùn)行環(huán)境

2.1 Oracle數(shù)據(jù)庫體系結(jié)構(gòu)

Oracle數(shù)據(jù)庫的體系結(jié)構(gòu)的與數(shù)據(jù)查詢性能以及查詢速度有著很大的聯(lián)系。Oracle9i數(shù)據(jù)庫的相關(guān)服務(wù)器給予了良好的信息管理的氛圍，而且該信息管理能夠?qū)⒑芏嗟臄?shù)據(jù)存儲(chǔ)起來，同時(shí)還能夠滿足用戶快速訪問這些數(shù)據(jù)的需求。Oracle9i數(shù)據(jù)庫的組成包括考慮Oracle數(shù)據(jù)庫物理文件與Oracle實(shí)例。Oracle的體系結(jié)構(gòu)與Oracle數(shù)據(jù)庫性能有著直接的聯(lián)系，能夠?qū)?shù)據(jù)的查詢性進(jìn)行改善與調(diào)整。基于該平臺(tái)上能夠在一定程度上增強(qiáng)數(shù)據(jù)庫語句的查詢速率。

Oracle物理文件中所包括的內(nèi)容有初始化參數(shù)文件、日志文件、以及控制文件和數(shù)據(jù)文件等。數(shù)據(jù)文件的作用是存放具全部的數(shù)據(jù)信息。數(shù)據(jù)文件和表空間是一一對(duì)應(yīng)的關(guān)系，而且每個(gè)表空間和數(shù)據(jù)文件是一對(duì)多的對(duì)應(yīng)關(guān)系。重做日志文件中主要記錄的是所有數(shù)據(jù)的變化情況，而且這些數(shù)據(jù)是按照一定的順序保存的，一般情況下重做日志文件的保存和數(shù)據(jù)文件的保存所放置的地方是不一樣的。當(dāng)系統(tǒng)出現(xiàn)了意外的故障時(shí)候，日志文件恢復(fù)數(shù)據(jù)庫的事務(wù)是按照正確的順序進(jìn)行的。為了增強(qiáng)可靠性，Oracle9i中的重做日志文件要達(dá)到兩個(gè)以上才可以正常運(yùn)行，當(dāng)服務(wù)器運(yùn)行的模式是處于歸檔日志情況下，那么通過歸檔在歸檔日志文件中寫入日志，從而很大程度上確保了數(shù)據(jù)庫是在安全的模式下運(yùn)作。Oracle9i在整個(gè)數(shù)據(jù)庫的構(gòu)建中同時(shí)也能夠構(gòu)建控制文件，這里的控制文件內(nèi)部中具體的內(nèi)容主要有數(shù)據(jù)的名稱附帶著重做日志文件全部的路徑名和全部的數(shù)據(jù)庫文件。這對(duì)于數(shù)據(jù)庫中控制文件有意義非凡的影響。

3 Oracle物理文件I/O應(yīng)用

數(shù)據(jù)庫性能的瓶頸是磁盤I/O，如果能夠?qū)⑵款i問題解決的話，就能夠在一定程度上提高性能。配置數(shù)據(jù)庫的重要性能準(zhǔn)則就是要盡可能地將磁盤I/O減少，并且使得多個(gè)磁盤驅(qū)動(dòng)器之間的負(fù)荷進(jìn)行平衡，而且盡可能采用本地管理表空間。此外在Oracle數(shù)據(jù)的性能提高過程中，如果Oracle的體系結(jié)構(gòu)可以得到很大地簡(jiǎn)化，可以使得數(shù)據(jù)庫的空間得到改善。按照安裝選項(xiàng)選擇，Oracle軟件在安裝的時(shí)候能夠自動(dòng)地構(gòu)建出標(biāo)準(zhǔn)的Oracle的體系結(jié)構(gòu)。配置數(shù)據(jù)庫的時(shí)候，數(shù)據(jù)庫管理員正確地布局?jǐn)?shù)據(jù)庫的物理文件以及相應(yīng)的邏輯對(duì)象。

3.1 Oracle數(shù)據(jù)庫物理文件

Oracle實(shí)例中的進(jìn)程充當(dāng)著重要的作用，Oracle實(shí)例可以確保交換Oracle實(shí)例和物理文件，從而保證整個(gè)系統(tǒng)可以成功運(yùn)行。后臺(tái)進(jìn)程的作用是維護(hù)與實(shí)現(xiàn)數(shù)據(jù)庫的物理結(jié)構(gòu)以及存儲(chǔ)結(jié)構(gòu)兩者的關(guān)系。Oracle實(shí)例的數(shù)量和數(shù)據(jù)庫的配置有著直接的關(guān)系。數(shù)據(jù)庫管理Oracle實(shí)例中，所做的系統(tǒng)管理工作比較少。數(shù)據(jù)庫的警告日志常記錄的是對(duì)后臺(tái)進(jìn)程有著重要影響的問題。

3.2 網(wǎng)絡(luò)環(huán)境

Oracle9i的網(wǎng)絡(luò)產(chǎn)品是OracleNet，通過TNS，Net的作用是連接服務(wù)器和客戶機(jī)。OracleNet協(xié)議適配器所進(jìn)行的操作是把普通的網(wǎng)絡(luò)操作進(jìn)行特定協(xié)議的轉(zhuǎn)換，采用第三方或者系統(tǒng)所提供的網(wǎng)絡(luò)軟硬件能夠?qū)?shù)據(jù)傳輸在兩臺(tái)計(jì)算機(jī)之間。OracleNet連接機(jī)制可以分為以下步驟進(jìn)行：

OracleNet的特點(diǎn)是透明性的網(wǎng)絡(luò)、透明的位置、獨(dú)立的協(xié)議等。許多的數(shù)據(jù)庫應(yīng)用程序使用場(chǎng)合通常是網(wǎng)絡(luò)環(huán)境。而且網(wǎng)絡(luò)性能的是否好和數(shù)據(jù)查詢的速度有著重要的聯(lián)系。網(wǎng)絡(luò)最大目標(biāo)的調(diào)整主要是利用數(shù)據(jù)庫自身局部的功能進(jìn)而帶來網(wǎng)絡(luò)的數(shù)據(jù)流量減少，進(jìn)而減少依賴網(wǎng)絡(luò)，防止數(shù)據(jù)性能問題的產(chǎn)生。

4 數(shù)據(jù)查詢優(yōu)化方法應(yīng)用

4.1 系統(tǒng)優(yōu)化措施

如果索引構(gòu)造是足夠合理，那么這在一定程度上使得數(shù)據(jù)庫查詢速度得到提高。也使得滿足請(qǐng)求的物理讀取總數(shù)得到減少。一般情況下，讀取索引塊能夠?qū)λ饕碇械臄?shù)據(jù)的行與塊進(jìn)行直接地讀取，相比于整個(gè)表的讀取更加地高效。

4.1.1 Oracle的主要索引類型

Oracle中用的比較多的索引就是B樹索引。該索引存在的形式是樹型存儲(chǔ)。根節(jié)點(diǎn)是索引的基礎(chǔ)，根節(jié)點(diǎn)中的信息有其他的指針，這些指針指向的是索引中下一層。而且該索引的下一層的組成是分枝節(jié)點(diǎn)，葉子節(jié)點(diǎn)是分枝節(jié)點(diǎn)中的最后一個(gè)，葉子節(jié)點(diǎn)的索引項(xiàng)中存儲(chǔ)的信息有列值的行所相應(yīng)的ROWID。連接葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)的左右葉子節(jié)點(diǎn)，從而在索引的前后進(jìn)行范圍的搜索。當(dāng)更改索引列中的數(shù)據(jù)時(shí)候，維護(hù)B樹索引實(shí)現(xiàn)起來比較容易，而且效率很快，該B樹索在聯(lián)機(jī)事務(wù)的應(yīng)用處理方面有很大的影響。B樹索引根據(jù)不同的事務(wù)進(jìn)行對(duì)應(yīng)的處理，從而增強(qiáng)SQL語句中查詢的性能。

4.2 系統(tǒng)的I/O效率提高

磁盤I/O是影響數(shù)據(jù)庫性能的重大因素，而且也對(duì)數(shù)據(jù)查詢速率造成一定的影響。所以．在規(guī)劃物理文件的時(shí)候，一般情況下會(huì)選擇對(duì)數(shù)據(jù)查詢性能有所提高的方法，比如在獨(dú)立的磁盤中存儲(chǔ)訪問量大的數(shù)據(jù)，并且對(duì)于在同一個(gè)表空間中對(duì)數(shù)據(jù)文件進(jìn)行分類處理，分別放置在不一樣的磁盤上，從而給索引建立出獨(dú)自的表空間，進(jìn)而防止由于日志切換過于頻繁，建立用于排序使用的臨時(shí)表空間。能夠按照具體的情況從而分析找出數(shù)據(jù)查詢的瓶頸，選擇合理的方法進(jìn)而將數(shù)據(jù)的查詢性能提高。運(yùn)行數(shù)據(jù)庫系統(tǒng)中，會(huì)出現(xiàn)許多的變化因素，例如增大數(shù)據(jù)文件，與索引，而且也可能改變數(shù)據(jù)庫的結(jié)構(gòu)。所以，針對(duì)表空間以及布局?jǐn)?shù)據(jù)文件需要制定出更加有效的計(jì)劃。

4.3 全表掃描調(diào)整

數(shù)據(jù)查詢通常用到了全表掃描，當(dāng)處理不恰當(dāng)?shù)臅r(shí)候，全表掃描就會(huì)導(dǎo)致數(shù)據(jù)讀取太多，從而出現(xiàn)I/O。首先調(diào)整全表掃描，能夠通過SQL的修改，其次我們也能夠根據(jù)索引的建立、參數(shù)初始化的修改等實(shí)現(xiàn)。

4.4 分析統(tǒng)計(jì)表

Oracle數(shù)據(jù)庫在SQL語句執(zhí)行方案的選擇中，需要根據(jù)數(shù)據(jù)字典中的各種對(duì)象信息的統(tǒng)計(jì)的存儲(chǔ)，通過優(yōu)化器從而選擇最優(yōu)的方案，需要考慮到信息的統(tǒng)計(jì)。所以常統(tǒng)計(jì)收集工作，進(jìn)一步地提高查詢的相關(guān)速度。統(tǒng)計(jì)分析方法能夠選擇analyze命令，另外也能夠運(yùn)用DBMS STATS包。

4.5 使用DBMS STATS包

通常analyze命令是一次處理一個(gè)表或者一個(gè)索引，當(dāng)收集的表有多個(gè)的時(shí)候，索引以至于統(tǒng)計(jì)分析工作區(qū)中的數(shù)據(jù)信息，就需要用到DBMS-STATS代碼包。而且在使用DBMS STATS之前，就需要先用sysdba的方式成功進(jìn)入到數(shù)據(jù)庫中，運(yùn)行bmsstat．Sql以及prvtstat．plb腳本，腳本中包括了數(shù)據(jù)庫在執(zhí)行過程中的定義以及申明。

結(jié)語：軟件供應(yīng)商紛紛在軟硬件平臺(tái)投資了很多的資金成本，目的是增強(qiáng)查詢數(shù)據(jù)的速率。本文主要是基于Oracle數(shù)據(jù)庫進(jìn)行查詢的優(yōu)化。本文完成了數(shù)據(jù)查詢語句的整個(gè)過程，接著根據(jù)Oracle數(shù)據(jù)庫的特征，從數(shù)據(jù)的優(yōu)化目的出發(fā)，重點(diǎn)研究了Oracle實(shí)例以及Oracle物理文件對(duì)SQL語句的查詢速率的影響。本文主要分析了影響Oracle數(shù)據(jù)庫的性能因素，從多個(gè)因素方面出發(fā)研究數(shù)據(jù)庫的查詢優(yōu)化，并且對(duì)數(shù)據(jù)庫的環(huán)境應(yīng)用也進(jìn)行了研究。

[1] 楊小艷, 尹明, 戴學(xué)豐. Oracle數(shù)據(jù)庫查詢優(yōu)化方法研究[J]. 計(jì)算機(jī)與現(xiàn)代化, 2008(4):4-7

[2] 張輝, 趙郁亮, 徐江,等. 基于Oracle數(shù)據(jù)庫海量數(shù)據(jù)的查詢優(yōu)化研究[J]. 計(jì)算機(jī)技術(shù)與發(fā)展, 2012, 22(2):165-167

[3] 張學(xué)義, 王觀玉, 黃雋. 基于Oralce數(shù)據(jù)庫SQL查詢優(yōu)化研究[J]. 制造業(yè)自動(dòng)化, 2011, 33(2):116-118

[4] 彭建明, 生本有. ORACLE數(shù)據(jù)庫性能優(yōu)化的方法研究[J].醫(yī)學(xué)信息:醫(yī)學(xué)與計(jì)算機(jī)應(yīng)用, 2004, 17(8):485-486