ORACLE SQL效能最佳化系列 (六)

oracle|效能|最佳化
20.       用表串連替換EXISTS

 

     通常來說 , 採用表串連的方式比EXISTS更有效率

      SELECT ENAME

      FROM EMP E

      WHERE EXISTS (SELECT ‘X’

                      FROM DEPT

                      WHERE DEPT_NO = E.DEPT_NO

                      AND DEPT_CAT = ‘A’);

 

     (更高效)

      SELECT ENAME

      FROM DEPT D,EMP E

      WHERE E.DEPT_NO = D.DEPT_NO

      AND DEPT_CAT = ‘A’ ;

 

(譯者按: 在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP)

 

21.       用EXISTS替換DISTINCT

當提交一個包含一對多表資訊(比如部門表和僱員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換

 

例如:

低效:

    SELECT DISTINCT DEPT_NO,DEPT_NAME

    FROM DEPT D,EMP E

    WHERE D.DEPT_NO = E.DEPT_NO

高效:

    SELECT DEPT_NO,DEPT_NAME

    FROM DEPT D

    WHERE EXISTS ( SELECT ‘X’

                    FROM EMP E

                    WHERE E.DEPT_NO = D.DEPT_NO);

 

  EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果.

 

22.       識別’低效執行’的SQL語句

 

用下列SQL工具找出低效SQL:

 

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,

        ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

        ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

        SQL_TEXT

FROM   V$SQLAREA

WHERE  EXECUTIONS>0

AND     BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

ORDER BY 4 DESC;

    

(譯者按: 雖然目前各種關於SQL最佳化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法)

 

23.       使用TKPROF 工具來查詢SQL效能狀態

 

SQL trace 工具收集正在執行的SQL的效能狀態資料並記錄到一個追蹤檔案中. 這個追蹤檔案提供了許多有用的資訊,例如解析次數.執行次數,CPU使用時間等.這些資料將可以用來最佳化你的系統.

 

設定SQL TRACE在會話層級: 有效

 

  ALTER SESSION SET SQL_TRACE TRUE

 

設定SQL TRACE 在整個資料庫有效仿, 你必須將SQL_TRACE參數在init.ora中設為TRUE, USER_DUMP_DEST參數說明了產生追蹤檔案的目錄

 

(譯者按: 這一節中,作者並沒有提到TKPROF的用法, 對SQL TRACE的用法也不夠準確, 設定SQL TRACE首先要在init.ora中設定TIMED_STATISTICS, 這樣才能得到那些重要的時間狀態. 產生的trace檔案是不可讀的,所以要用TKPROF工具對其進行轉換,TKPROF有許多執行參數. 大家可以參考ORACLE手冊來瞭解具體的配置. )