Oracle sql 效能最佳化

Oracle sql 效能最佳化調整

1. 選用適合的ORACLE最佳化器
ORACLE的最佳化器共有3種：
a. RULE （基於規則）
b. COST （基於成本）
c. CHOOSE （選擇性）設定預設的最佳化器，可以透過對init.ora檔案中OPTIMIZER_MODE引數的各種宣告，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS . 你當然也在SQL句級或是會話（session）級對其進行覆蓋。為了使用基於成本的最佳化器（CBO， Cost-Based Optimizer） ， 你必須經常執行analyze 命令，以增加資料庫中的物件統計資訊（object statistics）的準確性。如果資料庫的最佳化器模式設定為選擇性（CHOOSE），那麼實際的最佳化器模式將和是否執行過analyze命令有關。 如果table已經被analyze過， 最佳化器模式將自動成為CBO ， 反之，資料庫將採用RULE形式的最佳化器。在預設情況下，ORACLE採用CHOOSE最佳化器，為了避免那些不必要的全表掃描（full table scan） ， 你必須儘量避免使用CHOOSE最佳化器，而直接採用基於規則或者基於成本的最佳化器。
2. 訪問Table的方式ORACLE 採用兩種訪問表中記錄的方式：
a. 全表掃描全表掃描就是順序地訪問表中每條記錄。 ORACLE採用一次讀入多個資料塊（database block）的方式最佳化全表掃描。
b. 透過ROWID訪問表你可以採用基於ROWID的訪問方式情況，提高訪問表的效率， ROWID包含了表中記錄的物理位置資訊……ORACLE採用索引（INDEX）實現了資料和存放資料的物理位置（ROWID）之間的聯絡。 通常索引提供了快速訪問ROWID的方法，因此那些基於索引列的查詢就可以得到效能上的提高。
3. 共享SQL語句為了不重複解析相同的SQL語句，在第一次解析之後， ORACLE將SQL語句存放在記憶體中。這塊位於系統全域性區域SGA（system global area）的共享池（shared buffer pool）中的記憶體可以被所有的資料庫使用者共享。 因此，當你執行一個SQL語句（有時被稱為一個遊標）時，如果它和之前的執行過的語句完全相同， ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑。 ORACLE的這個功能大大地提高了SQL的執行效能並節省了記憶體的使用。可惜的是ORACLE只對簡單的表提供高速緩衝（cache buffering） ，這個功能並不適用於多表連線查詢。 資料庫管理員必須在init.ora中為這個區域設定合適的引數，當這個記憶體區域越大，就可以保留更多的語句，當然被共享的可能性也就越大了。當你向ORACLE 提交一個SQL語句，ORACLE會首先在這塊記憶體中查詢相同的語句。這裡需要註明的是，ORACLE對兩者採取的是一種嚴格匹配，要達成共享，SQL語句必須完全相同（包括空格，換行等）。共享的語句必須滿足三個條件：
A. 字元級的比較： 當前被執行的語句和共享池中的語句必須完全相同。例如：
SELECT * FROM EMP；和下列每一個都不同
SELECT * from EMP；
Select * From Emp；
SELECT * FROM EMP；
B. 兩個語句所指的物件必須完全相同： 例如：使用者 物件名 如何訪問
Jack sal_limit private synonym 　　　　Work_city public synonym 　　　　Plant_detail public synonym 　Jill sal_limit private synonym 　　　　Work_city public synonym 　　　　Plant_detail table owner　考慮一下下列SQL語句能否在這兩個使用者之間共享。

5. WHERE子句中的連線順序。 ORACLE採用自下而上的順序解析WHERE子句，根據這個原理，表之間的連線必須寫在其他WHERE條件之前， 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。例如：　（低效，執行時間156.3秒）
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,執行時間10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;　6. SELECT子句中避免使用 ‘ * ’　當你想在SELECT子句中列出所有的COLUMN時，使用動態SQL列引用 ‘*’ 是一個方便的方法。不幸的是，這是一個非常低效的方法。 實際上，ORACLE在解析的過程中， 會將‘*’ 依次轉換成所有的列名， 這個工作是透過查詢資料字典完成的， 這意味著將耗費更多的時間。
7. 減少訪問資料庫的次數　當執行每條SQL語句時， ORACLE在內部執行了許多工作： 解析SQL語句， 估算索引的利用率， 繫結變數 ， 讀資料塊等等。 由此可見， 減少訪問資料庫的次數 ， 就能實際上減少ORACLE的工作量。例如，以下有三種方法可以檢索出僱員號等於0342或0291的職員。方法1 （最低效）
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 291;方法2 （次低效）
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END;方法3 （高效）
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;　注意：　在SQL*Plus ， SQL*Forms和Pro*C中重新設定ARRAYSIZE引數， 可以增加每次資料庫訪問的檢索資料量 ，建議值為200. 　8. 使用DECODE函式來減少處理時間　使用DECODE函式可以避免重複掃描相同記錄或重複連線相同的表。例如：
SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE　‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE　‘SMITH%’;　你可以用DECODE函式高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;　類似的，DECODE函式也可以運用於GROUP BY 和ORDER BY子句中。
9. 整合簡單，無關聯的資料庫訪問　如果你有幾個簡單的資料庫查詢語句，你可以把它們整合到一個查詢中（即使它們之間沒有關係）例如：
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;

SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;

SELECT NAME
FROM CAT
WHERE CAT_TYPE = ‘RD’;　上面的3個查詢可以被合併成一個：
SELECT E.NAME , D.NAME , C.NAME
FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’;（譯者按： 雖然採取這種方法，效率得到提高，但是程式的可讀性大大降低，所以讀者還是要權衡之間的利弊）

很不錯的文件（轉）Oracle+performance.rar

[@more@]