1、引言

Oracle 數(shù)據(jù)庫是當(dāng)前應(yīng)用最廣泛的大型數(shù)據(jù)庫管理系統(tǒng), 它在各個領(lǐng)域的使用不斷增長, 其查詢性能直接關(guān)系到系統(tǒng)的運(yùn)行效率, 對其查詢優(yōu)化方法的研究更具有現(xiàn)實(shí)意義。數(shù)據(jù)庫SQL 查詢優(yōu)化的一個基本原則就是: 通過盡可能少的磁盤I/ O訪問獲取所需要的數(shù)據(jù)。要評價查詢優(yōu)化性能, 需要在數(shù)據(jù)庫優(yōu)化前后比較其評價指標(biāo): 響應(yīng)時間和吞吐量之間的權(quán)衡、數(shù)據(jù)庫的命中率以及內(nèi)存的使用效率, 并以此來衡量優(yōu)化的效果和指導(dǎo)優(yōu)化的方向。優(yōu)質(zhì)的查詢語句可以使應(yīng)用系統(tǒng)的性能得到大大提高。拙劣的應(yīng)用SQL 語句、大小不合適的SGA 內(nèi)存結(jié)構(gòu)、效率差的SQL 語句、執(zhí)行計(jì)劃過度的文件I/O及訪問數(shù)據(jù)庫資源的紊亂等都會影響數(shù)據(jù)庫系統(tǒng)的性能和執(zhí)行效率。本文將從數(shù)據(jù)庫設(shè)計(jì)的角度及SQL語句優(yōu)化技術(shù)的兩方面進(jìn)行詳細(xì)地闡述如何提高數(shù)據(jù)庫的性能和執(zhí)行效率。

一、 數(shù)據(jù)庫設(shè)計(jì)的優(yōu)化

數(shù)據(jù)庫設(shè)計(jì)是應(yīng)用系統(tǒng)設(shè)計(jì)的基礎(chǔ), 其性能直接影響應(yīng)用系統(tǒng)的性能。數(shù)據(jù)庫性能包括查詢響應(yīng)時間和吞吐量兩個方面。數(shù)據(jù)庫設(shè)計(jì)優(yōu)化的主要目標(biāo)就是減少磁盤I/ O、減少CPU 利用率和資源競爭, 降低查詢響應(yīng)時間或提高系統(tǒng)吞吐量。

1.1 磁盤I/ O 優(yōu)化

磁盤I/ O是影響Oracle 數(shù)據(jù)庫性能的瓶頸,而影響磁盤I/ O 性能的主要原因有磁盤競爭、I/O。資源競爭增強(qiáng)時,響應(yīng)時間增長讀取次數(shù)和數(shù)據(jù)塊空間的分配管理不當(dāng)?shù)取racle進(jìn)程經(jīng)常需要訪問數(shù)據(jù)文件和重做日志文件,當(dāng)這兩種類型的文件位于同一磁盤中,就會造成磁盤競爭。將表空間所屬的數(shù)據(jù)文件存儲在多個不同的物理磁盤上,使系統(tǒng)I/ O 趨于平衡;把數(shù)據(jù)、日志、索引放到不同的I/O設(shè)備上,可以減少磁盤競爭和I/ O讀取次數(shù)。提高I/O設(shè)備的并發(fā)訪問率,可以有效提高SQL 語句的執(zhí)行效率。當(dāng)競爭增強(qiáng)的時候, 系統(tǒng)響應(yīng)時間將增長。

1.2內(nèi)存優(yōu)化調(diào)整

要充分發(fā)揮Oracle據(jù)庫的優(yōu)勢,必須對數(shù)據(jù)庫的各項(xiàng)初始化參數(shù)進(jìn)行合理配置。從Oracle 數(shù)據(jù)庫內(nèi)存優(yōu)化管理的角度出發(fā),針對影響其性能的因素及其對應(yīng)的參數(shù),分別從數(shù)據(jù)緩沖區(qū)優(yōu)化、共享池優(yōu)化、重做日志緩沖區(qū)優(yōu)化幾個方面完成內(nèi)存優(yōu)化配置。

1.3優(yōu)化全表掃描操作

一次I/O 能讀取多塊數(shù)據(jù)塊(由db_block_multiblock_read_count 參數(shù)設(shè)定),這極大地減少I/O總次數(shù),提高了系統(tǒng)的吞吐量, 所以利用多塊讀取的方法可以高效地實(shí)現(xiàn)全表掃描。

二、SQL 語句優(yōu)化

SQL語句尤其是復(fù)雜SQL語句的性能優(yōu)化對于數(shù)據(jù)庫的性能是至關(guān)重要的。實(shí)際數(shù)據(jù)庫使用過程中80% 的性能問題是由于用戶使用了不恰當(dāng)?shù)腟QL 查詢語句造成的,因此優(yōu)化SQL 語句可以提升整個系統(tǒng)的運(yùn)行效率。在集中式數(shù)據(jù)庫中, SQL 查詢的執(zhí)行總代價= I/ O 代價+ CPU 代價+ 內(nèi)存代價。調(diào)整影響其執(zhí)行效率的三大因素來減少系統(tǒng)總代價:一是減少查詢所產(chǎn)生的I/ O 總次數(shù); 二是減少CPU 的計(jì)算頻度, 減少SQL 語句中需要計(jì)算的量和參數(shù); 三是減少對系統(tǒng)內(nèi)存的使用和占用時間。

2.1檢查不合理的SQL 語句

要檢測出不合理的SQL 語句, 首先要生成執(zhí)行計(jì)劃,最簡單的辦法有兩種: 一是SQL> set autotrace on 自動記錄執(zhí)行計(jì)劃; 二是explainplan  for ‘SQL 語句’，然后通過select * from table(dbms_xplan.display ( ) ) 來查看執(zhí)行計(jì)劃。第一種方法查看執(zhí)行時間較長的SQL 語句時, 需要等待該語句執(zhí)行成功后才返回執(zhí)行計(jì)劃。如果只想得到執(zhí)行計(jì)劃可以采用explainplan 或者SQL> set autotrace traceonly, 它們不會真正的執(zhí)行語句。通過執(zhí)行計(jì)劃來確定不合理的SQL 語句, 為SQL 優(yōu)化做準(zhǔn)備。

2.2共享SQL 語句

在第一次解析之后, Oracle 將SQL語句存放位在系統(tǒng)全局區(qū)域( SGA) 的共享池中,它可以為所有的數(shù)據(jù)庫用戶共享,大大地提高了SQL 的執(zhí)行性能并節(jié)省了內(nèi)存。當(dāng)用戶提交一個SQL 語句時,服務(wù)器進(jìn)程在共享池中查找有無該條語句,如果有就跳過語法分析等過程,節(jié)省了SQL語句的分析和編譯的開銷只有在共享池中不存在等價SQL語句的情況下,才對該語句作語法分析,并為該語句分配新的共享SQL 區(qū)。對于編程者來說,要盡量提高SQL 語句的重用率,盡量使用Bind 變量,來減少語句的分析時間。但是Oracle 對語句的匹配是相當(dāng)嚴(yán)格的,要達(dá)成共享, 它要遵循三條規(guī)則:

（1） SQL 語句必須完全相同( 包括空格、換行等)。下列SQL 語句都是不同的,

SELECT * from EMP; Select * From Emp; SELECT * FROM EMP;

（2）語句中的對象必須完全一致。

（3）語句中的綁定變量必須使用相同的名字。例如: 下面的兩個SQL 語句是相同的,select ename from emp w here empno=:emp.no 和

select enam from emp where empno=:emp. no。

三、SQL語句優(yōu)化技術(shù)

3.1基于索引的優(yōu)化

索引是影響SQL 語句性能的一個重要因素, 在表上建立合適的索引, 可以避免全表掃描并減少I/ O 開銷, 提高數(shù)據(jù)查詢速度。但是創(chuàng)建索引會增加系統(tǒng)時間和空間的開銷, 創(chuàng)建索引時必須要與實(shí)現(xiàn)應(yīng)用系統(tǒng)的查詢需求密切結(jié)合, 才能達(dá)到優(yōu)化查詢的目的。索引使用的一些原則：

（1） 當(dāng)選擇數(shù)據(jù)少于全表的20%, 并且表的大小超過ORACLE的5個數(shù)據(jù)塊時,使用索引才會有效,否則用于索引的I/ O 加上用于數(shù)據(jù)的I/ O 就會大于做一次全表掃描的I/O,反而會降低查詢效率;

（2） 由于表和索引的數(shù)據(jù)塊通常是被同時讀取的, 所以應(yīng)該盡量將表和其相關(guān)聯(lián)的索引放置在不同的磁盤上, 減少I/ O 沖突;

（3）索引的創(chuàng)建也是需要代價的, 對于DML 操作, 每個索引都要進(jìn)行相應(yīng)的刪除、更新、插入操作。從而導(dǎo)致DML 操作的效率變低。因此定期的重構(gòu)索引是有必要的, alter index < index name> rebuild < tablespacename> ;

（4）避免隱式轉(zhuǎn)換和不必要的類型轉(zhuǎn)換。例如: to_char( ) , to_number( ) , to_date( ) 等會導(dǎo)致該字段的索引失效, 從而對該表進(jìn)行全表掃描, 對一個百萬數(shù)量級的表進(jìn)行全表掃描的會對系統(tǒng)帶來嚴(yán)重的性能隱患;

3.2、基于Oracle 數(shù)據(jù)庫對表訪問的優(yōu)化

3.2.1 全表掃描

全表掃描就是對表中每條記錄進(jìn)行順序的訪問。Oracle 采用一次讀入多個數(shù)據(jù)塊(database block)的方式對全表掃描進(jìn)行優(yōu)化。

3.2.2 選擇最有效率的表名順序

Oracle 的解析器按照從右到左的順序?qū)ROM 子句中的表名進(jìn)行處理，F(xiàn)ROM 子句中寫在最后的表將最先被處理，當(dāng)FROM 子句中包含多個表時，必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。如果有3 個以上的表進(jìn)行連接查詢，那么，就需要選擇交叉表作為基礎(chǔ)表，交叉表就是被其他表所引用的那個表？

例如: student 表描述了location 表和category 表的交集?

SELECT * FROM location L, category C, student S WHERE S.sno BETWEEN 1000 AND 2000 AND S.catno=C.catno AND S.locn=L.locn

相對于下列SQL 語句更有效率:

SELECT * FROM student S, location L, category C WHERE S.catno=C.catno AND S.locn=L.locn AND S.sno BETWEEN 1000 AND 2000.

3.2.3 通過ROWID 訪問表

為了提高訪問表的效率，強(qiáng)烈采用基于ROWID 的訪問方式情況。ROWID 包含了表中記錄的物理位置信息。Oracle 數(shù)據(jù)和存放數(shù)據(jù)的物理位置之間的聯(lián)系，采用索引進(jìn)行實(shí)現(xiàn)。通常情況下，索引提供了快速訪問ROWID 的方法，因此那些基于索引列的查詢，能夠得到性能上的提高。

3.3 SQL 語句編寫需要滿足的規(guī)則

根據(jù)上面兩種Oracle 訪問表的方式，進(jìn)行SQL 語句的編寫時，需要滿足以下規(guī)則：

（1） 盡量使用索引。

（2） 選擇聯(lián)合查詢的聯(lián)合次序。在SQL 語句的編寫中，應(yīng)該注意首先需要選擇要查詢的主表，因?yàn)橹鞅硪獟呙枵麄€表數(shù)據(jù)，所以主表應(yīng)該數(shù)據(jù)量最小。

（3） IN 或者NOT IN 語句在子查詢中慎重使用，可以使用(NOT) EXISTS。

（4） 慎重使用視圖的聯(lián)合查詢，尤其慎重使用比較復(fù)雜的視圖之間的聯(lián)合查詢。一般來說，將對視圖的查詢分解為對數(shù)據(jù)表的直接查詢能夠取得更好的效果。

（5） 可以在參數(shù)文件中進(jìn)行SHARED_POOL_RESERVED_SIZE 參數(shù)的設(shè)置，這個參數(shù)保留了一個連續(xù)的內(nèi)存空間在SGA 共享池中，對于存放大的SQL 程序包非常有幫助。

（6） 對于某些經(jīng)常使用的存儲過程，可以通過Oracle 公司提供的DBMS_SHARED_POOL 程序固定在SQL 區(qū)中而不被換出內(nèi)存，對于提高最終用戶的響應(yīng)時間是非常有利的。

3.4 實(shí)例

（1） 通過使用索引進(jìn)行SQL 語句優(yōu)化例如，比較下面兩條SQL 語句：

語句A： SELECT deptno, deptname FROM dept WHERE  deptno  NOT IN (SELECT deptno FROM student);

語句B: SELECT deptno, deptname FROM dept WHERE  NOT EXISTS SELECT * FROM student WHERE dept.deptno=student.deptno);

通過執(zhí)行發(fā)現(xiàn),這兩條查詢語句實(shí)現(xiàn)的結(jié)果相同，但是在執(zhí)行語句A 時，Oracle 會掃描整個student 表，而建立在student表上的deptno 索引沒有使用到，當(dāng)執(zhí)行語句B 時，由于在子查詢中使用了聯(lián)合查詢，Oracle 只掃描了student 表中的部分?jǐn)?shù)據(jù)，并利用了deptno 列的索引，因此，語句B 的效率比語句A 的效率高。

四、 結(jié)束語

通過進(jìn)行SQL 語句優(yōu)化，提高了Oracle 數(shù)據(jù)庫系統(tǒng)的性能,明顯降低了其系統(tǒng)響應(yīng)時間，提高了程序運(yùn)行速度，使系統(tǒng)的順利運(yùn)行得到良好的保障。對于Oracle 數(shù)據(jù)庫應(yīng)用系統(tǒng)來說, SQL查詢語句的性能優(yōu)劣直接影響整個信息系統(tǒng)的效率, 效率高的查詢語句和效率低的查詢語句速度相差可以達(dá)到上百倍。只有認(rèn)真分析Oracle 運(yùn)行過程當(dāng)中出現(xiàn)的各種性能問題, 才能保證Oracle 數(shù)據(jù)庫高效可靠地運(yùn)行。論文從影響SQ L 性能的最主要的幾個方面入手, 分析了如何優(yōu)化SQ L 查詢的I/ O、內(nèi)存參數(shù)的調(diào)整和SQL 語句的優(yōu)化。數(shù)據(jù)庫的性能調(diào)整是一個系統(tǒng)工程, 需要在大量的實(shí)踐工作中不斷地積累經(jīng)驗(yàn), 結(jié)合上述各種優(yōu)化技術(shù), 從而更好地進(jìn)行數(shù)據(jù)庫調(diào)優(yōu)。