ORACLE當中自定義函數性優化淺析

為什麼函數影響性能

在SQL語句中，如果不合理的使用函數（Function）就會嚴重影響性能，其實這裡想說的是PL/SQL中的自定義函數，反而對於一些內置函數而言，影響性能的可能性較小。那麼為什麼SQL語句當中，不合理的使用函數會影響性能呢？

    在SELECT語句中調用函數時，那麼查詢返回的結果集中的每一行都會調用該函數。如果該函數需要執行1秒，返回的結果集是10行，那麼此時SQL語句就需要10秒，如果該函數執行時間需要3秒，返回的結果集是10000條記錄，那麼這個時間就是30000秒~= 500分鐘。是否很恐怖！因為生產環境中自定義函數有時候會出現複雜的業務邏輯，導致自定義函數性能開銷較高，如果出現不合理調用，那麼很容易就會出現性能問題。 下麵我們簡單來演示一個例子。

CREATE TABLE TEST

(

   ID  NUMBER

);

DECLARE RowIndex NUMBER;

BEGIN

RowIndex :=1;

WHILE RowIndex <= 8 LOOP

    INSERT INTO TEST

    SELECT RowIndex  FROM DUAL;

     RowIndex := RowIndex +1;

END LOOP;

COMMIT;

END;

/

--創建函數SLOW_FUNCTION，使用DBMS_LOCK.SLEEP休眠2秒，模擬這個函數較慢。

CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)

RETURN NUMBER

AS

BEGIN

    DBMS_LOCK.SLEEP(2);

    RETURN p_value+10;

END;

/

SQL> SET TIMING ON; 

SQL> SELECT * FROM TEST;

        ID

----------

         1

         2

         3

         4

         5

         6

         7

         8

8 rows selected.

Elapsed: 00:00:00.00

SQL> SELECT SLOW_FUNCTION(ID) FROM TEST;

SLOW_FUNCTION(ID)

-----------------

               11

               12

               13

               14

               15

               16

               17

               18

8 rows selected.

Elapsed: 00:00:16.01

如果在WHERE當中使用函數，由於有8條記錄，而每次調用函數需要Sleep 2秒， 總共耗費2*8=16秒。所以在WHERE條件中，也要謹慎使用自定義函數。

SQL> SET AUTOTRACE OFF;

SQL> 

SQL> SELECT * FROM TEST

  2  WHERE SLOW_FUNCTION(ID)>15;

        ID

----------

         6

         7

         8

Elapsed: 00:00:16.01

SQL> 

什麼情況下函數影響性能

其實自定義函數影響性能，主要在於函數（Function）調用的次數或函數（Function）本身的業務邏輯是否複雜，如果SELECT查詢中調用次數很少，影響還是非常小的。如下所示，如果只調用一次的話，這個影響還是非常小的。

SQL> SELECT SLOW_FUNCTION(ID) FROM TEST WHERE ID=2;

SLOW_FUNCTION(ID)

-----------------

               12

Elapsed: 00:00:02.01

其次，如果函數實現的業務邏輯簡單，即使調用次數多，對性能影響也很小。我們改寫一下下麵函數，通過實驗來驗證測試一下，如下所示： 

CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)

RETURN NUMBER

AS

BEGIN

    RETURN p_value+10;

END;

/

然後創建一個存儲過程，來測試一下迴圈次數對性能的影響。

CREATE OR REPLACE PROCEDURE TEST_SLOW_FUNCTION(ITER IN NUMBER)

AS RESULT VARCHAR2(60);

BEGIN

    FOR I IN 1 .. ITER LOOP

    SELECT SLOW_FUNCTION(I) INTO RESULT FROM DUAL;

    END LOOP;

END TEST_SLOW_FUNCTION;

/

如下所示，當函數業務邏輯簡單，性能開銷很低時，迴圈次數對性能的影響反而很小。10次迴圈調用跟1000000次迴圈調用差別是3秒多。可見如果自定義函數的業務邏輯簡單，迴圈次數對性能影響較小。 

SQL> EXEC TEST_SLOW_FUNCTION(10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL> EXEC TEST_SLOW_FUNCTION(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.40

SQL> EXEC TEST_SLOW_FUNCTION(100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.64

如何優化解決問題

對SQL中調用的自定義函數，可以通過等價改寫成多表關聯語句。避免產生大量的遞歸調用，另外就是想法設法減少函數被調用次數。SQL中儘量避免使用自定義函數(不是不能用，而是要看場合，儘量避免使用的原因：因為你寫的函數，可能會被其它人濫用，會偏離當初你寫這個函數的初衷)，或者儘量避免函數中實現複雜業務邏輯。

另外，如果實在不能避免的話，就儘量減少調用次數。另外，也有一些技巧可以優化自定義函數性能，下麵內容基本參考或翻譯Efficient Function Calls From SQL這篇文章。