oracle SQL最佳化之高效的函數調用

標籤：

原文地址：https://oracle-base.com/articles/misc/efficient-function-calls-from-sql

　　　　　http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

1. 問題描述

　　我們說一個function是確定性（deterministic），當對於相同的輸入總是返回相同的輸出，oracle中的內建函數如abs，不管多少次調用，abs(-1)總是返回1。假設在sql中調用這種function，如果存在相同的輸入資料，每次調用都要重新執行function的話就會產生效能浪費。　

create table func_test(id number);--插入資料INSERT INTO func_testSELECT CASE         WHEN level = 10 THEN 3         WHEN MOD(level, 2) = 0 THEN 2         ELSE 1       ENDFROM   dualCONNECT BY level <= 10;COMMIT;

create or replace function slow_function(       p_in  number) return number  deterministic isbegin  sys.dbms_lock.sleep(1);  return p_in;end;

SQL> set timing on;SQL> select slow_function(id) from func_test;SLOW_FUNCTION(ID)-----------------                1                2                1                2                1                2                1                2                1                310 rows selectedExecuted in 10.046 seconds

上述SQL執行時間為10.046秒，說明對於每一行資料，都執行了slow_function方法。由於slow_function是確定性：對於slow_function(1)用於返回1，所以上述SQL對於造成了效能浪費。

 

2. 標量子查詢緩衝（scalar subquery caching）

　標量子查詢緩衝會通過緩衝結果減少sql對function的調用次數，改用標量子查詢的sql僅僅用時3.057秒，即SQL對於slow_function的調用只發生了三次。

SQL> select (select slow_function(id) from dual) from func_test;(SELECTSLOW_FUNCTION(ID)FROMDU------------------------------                             1                             2                             1                             2                             1                             2                             1                             2                             1                             310 rows selectedExecuted in 3.057 seconds

當使用標量子查詢的時候，oracle會在記憶體中建立一個很小的hash table用於緩衝子查詢結果,對多可以緩衝255個子查詢結果：

select (select slow_function(id) from dual) from func_test;
id	slow_function(id)

當第一次查詢slow_function(1)的時候，由於hash table沒有緩衝，所以需要執行slow_function;當第二次查詢slow_function(1)的時候可以直接從hash table拿出結果，而不用再次調用slow_function。即便有可能發生hash衝突，而且hash table只支援255個bucket，標量子查詢對於效能提高總是好的。

 

3. DETERMINISTIC

　　oracle通過關鍵字DETERMINISTIC來表名一個function是確定性，確定性函數可以用於建立基於函數的索引。

create or replace function slow_function(       p_in  number) return number deterministicisbegin  sys.dbms_lock.sleep(1);  return p_in;end;

deterministic緩衝受限於每次從伺服器fetch多少資料，緩衝僅在當前fetch的生命週期內有效，而標量子查詢是當前查詢內有效。

假設set arraysize 1 DETERMINISTIC關鍵字不會對效能起到任何協助，set arraysize 15，也僅僅是每15條資料的緩衝結果可以重用。

 

4. RESULT_CACHE

　　oracle通過關鍵字RESULT_CACHE對函數返回的結果進行緩衝，緩衝結果可以被session共用。

create or replace function slow_function(p_in number) return number result_cache isbegin  sys.dbms_lock.sleep(1);  return p_in;end;

通過關鍵字result_cache的函數在執行過以後，速度會大幅提升。

 

5. 即便通過deterministic和result_cache可以提高，但我們總是應該使用標量子查詢，因為deterinisitic和result_cache僅僅是緩衝結果，並不能減少SQL和PLSQL上下文質檢的切換，即總是會發生SQL引擎和PLSQL引擎的互動，並不會減少對CPU的消耗。

　標量子查詢也可以作用於where子句以提高查詢效能。

 

6. 函數調用中的讀一致性問題

　　在sql中調用function，假設function中也有sql的話，在隔離等級為read committed的情況下，有可能會發生不可重複讀取和幻想讀。可以通過dbms_flashback保證讀一致性。 

EXEC DBMS_FLASHBACK.enable_at_time(SYSTIMESTAMP);SELECT slow_function(id)FROM   func_test;EXEC DBMS_FLASHBACK.disable;

 

 

　　

 

oracle SQL最佳化之高效的函數調用