1、引言

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

一、 數據庫設計的優化

數據庫設計是應用系統設計的基礎, 其性能直接影響應用系統的性能。數據庫性能包括查詢響應時間和吞吐量兩個方面。數據庫設計優化的主要目標就是減少磁盤I/ O、減少CPU 利用率和資源競爭, 降低查詢響應時間或提高系統吞吐量。

1.1 磁盤I/ O 優化

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

1.2內存優化調整

要充分發揮Oracle據庫的優勢,必須對數據庫的各項初始化參數進行合理配置。從Oracle 數據庫內存優化管理的角度出發,針對影響其性能的因素及其對應的參數,分別從數據緩沖區優化、共享池優化、重做日志緩沖區優化幾個方面完成內存優化配置。

1.3優化全表掃描操作

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

二、SQL 語句優化

SQL語句尤其是復雜SQL語句的性能優化對于數據庫的性能是至關重要的。實際數據庫使用過程中80% 的性能問題是由于用戶使用了不恰當的SQL 查詢語句造成的,因此優化SQL 語句可以提升整個系統的運行效率。在集中式數據庫中, SQL 查詢的執行總代價= I/ O 代價+ CPU 代價+ 內存代價。調整影響其執行效率的三大因素來減少系統總代價:一是減少查詢所產生的I/ O 總次數; 二是減少CPU 的計算頻度, 減少SQL 語句中需要計算的量和參數; 三是減少對系統內存的使用和占用時間。

2.1檢查不合理的SQL 語句

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

2.2共享SQL 語句

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

（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語句優化技術

3.1基于索引的優化

索引是影響SQL 語句性能的一個重要因素, 在表上建立合適的索引, 可以避免全表掃描并減少I/ O 開銷, 提高數據查詢速度。但是創建索引會增加系統時間和空間的開銷, 創建索引時必須要與實現應用系統的查詢需求密切結合, 才能達到優化查詢的目的。索引使用的一些原則：

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

（2） 由于表和索引的數據塊通常是被同時讀取的, 所以應該盡量將表和其相關聯的索引放置在不同的磁盤上, 減少I/ O 沖突;

（3）索引的創建也是需要代價的, 對于DML 操作, 每個索引都要進行相應的刪除、更新、插入操作。從而導致DML 操作的效率變低。因此定期的重構索引是有必要的, alter index < index name> rebuild < tablespacename> ;

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

3.2、基于Oracle 數據庫對表訪問的優化

3.2.1 全表掃描

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

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

Oracle 的解析器按照從右到左的順序對FROM 子句中的表名進行處理，FROM 子句中寫在最后的表將最先被處理，當FROM 子句中包含多個表時，必須選擇記錄條數最少的表作為基礎表。如果有3 個以上的表進行連接查詢，那么，就需要選擇交叉表作為基礎表，交叉表就是被其他表所引用的那個表？

例如: 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 訪問表

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

3.3 SQL 語句編寫需要滿足的規則

根據上面兩種Oracle 訪問表的方式，進行SQL 語句的編寫時，需要滿足以下規則：

（1） 盡量使用索引。

（2） 選擇聯合查詢的聯合次序。在SQL 語句的編寫中，應該注意首先需要選擇要查詢的主表，因為主表要掃描整個表數據，所以主表應該數據量最小。

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

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

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

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

3.4 實例

（1） 通過使用索引進行SQL 語句優化例如，比較下面兩條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);

通過執行發現,這兩條查詢語句實現的結果相同，但是在執行語句A 時，Oracle 會掃描整個student 表，而建立在student表上的deptno 索引沒有使用到，當執行語句B 時，由于在子查詢中使用了聯合查詢，Oracle 只掃描了student 表中的部分數據，并利用了deptno 列的索引，因此，語句B 的效率比語句A 的效率高。

四、 結束語

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