在SQL中使用PL/SQL函數存在的問題

很多不瞭解Oracle資料庫的開發人員很喜歡用PL/SQL的函數、儲存等來達到代碼上的簡潔.

推薦閱讀：

使用PL/SQL執行java儲存來獲得MAC地址  

如:

SELECT EMPNO,ENAME,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

這樣一個SQL,開發人員可能覺得冗長(這裡假設SQL冗長),他們喜歡用函數,這樣:

CREATE FUNCTION F_GETDEPTINFO(PDEPTNO NUMBER,PTYPE VARCHAR2)

RETURN VARCHAR2 AS

V_DEPTINFO VARCHAR2(50);

BEGIN

IF PTYPE='DNAME' THEN

SELECT DNAME INTO V_DEPTINFO FROM DEPT WHERE DEPTNO=PDEPTNO;

END IF;

IF PTYPE='LOC' THEN

SELECT LOC INTO V_DEPTINFO FROM DEPT WHERE DEPTNO=PDEPTNO;

END IF;

RETURN V_DEPTINFO;

END;

 

有的還會為函數加上異常處理,返回某些自訂的值.(我這裡就沒有加了)

最後,SQL就改寫為:SELECT EMPNO,ENAME,F_GETDEPTINFO(DEPTNO,'DNAME'),F_GETDEPTINFO(DEPTNO,'LOC') FROM EMP;

這樣開發人員認為SQL簡潔多了,並且可能有的還認為效能會有提升.尤其是在處理某些複雜的商務邏輯的時候,SQL中PL/SQL函數使用的更為頻繁.

由於對資料庫的不熟悉,尤其不善於SQL的表串連等方式,大量的函數使用導致應用變的日益緩慢起來.所以在這裡建議開發的TX們也多瞭解一下相應的資料庫原理.

在這裡我們來分析以下在SQL中使用PL/SQL函數存在的幾個問題:

1.熟悉oracle資料庫的人知道在oracle資料庫中存在SQL引擎和PL/SQL引擎,分別用來處理sql語句和PLSQL語句.雖然在9i之後,SQL語句和PL/SQL語句可以共用同一個

解析器,但解析後的語句還是會在兩個引擎之間進行切換,這勢必會帶來效能開銷.

樣本:

直接執行SQL語句

11:17:33 SCOTT@orcl> set autot trace
11:18:05 SCOTT@orcl> SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 1e6;

1000000 rows selected.

Elapsed: 00:00:10.03

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
  11846828  bytes sent via SQL*Net to client
    733734  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

可以看到效能基本在網路的I/O上.耗時10.03秒.

下面我們來在SQL中使用PL/SQL函數來完成同樣的功能.

11:25:11 SCOTT@orcl> CREATE FUNCTION plsql_function(p_number IN NUMBER)
11:25:14  2  RETURN NUMBER AS
11:25:14  3  BEGIN
11:25:14  4    RETURN p_number;
11:25:14  5  END plsql_function;
11:25:15  6  /

Function created.

Elapsed: 00:00:00.04
11:25:28 SCOTT@orcl> SELECT plsql_function(ROWNUM) t FROM dual CONNECT BY ROWNUM<= 1e6;

1000000 rows selected.

Elapsed: 00:00:13.50

Statistics
----------------------------------------------------------
        17  recursive calls
          0  db block gets
        24  consistent gets
          0  physical reads
          0  redo size
  11846823  bytes sent via SQL*Net to client
    733734  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

此時不僅有網路I/O,而且出現了緩衝的I/O.耗時為13.50秒.

通過autotrace我們感覺到在SQL中使用PL/SQL函數會比直接使用SQL語句要慢。

注:這裡AUTOTRACE資訊不是很明顯,可以通過之前介紹的DBMS_HPROF包來查看詳細的效能資訊.

這裡我們再分析上面兩個語句的SQL_TRACE情況.

直接使用SQL語句查詢的SQL_TRACE如下:

PARSING IN CURSOR #11 len=48 dep=0 uid=84 oct=3 lid=84 tim=1359750169623584 hv=2961471920 ad='3a9180b0' sqlid='67j3zkks88ydh'
SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 1e6
END OF STMT
PARSE #11:c=5999,e=5932,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1731520519,tim=1359750169623579
EXEC #11:c=0,e=218,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1731520519,tim=1359750169624022
FETCH #11:c=1000,e=194,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1731520519,tim=1359750169624591
FETCH #11:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=1731520519,tim=1359750169625626
...
FETCH #11:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=1731520519,tim=1359750173026323

*** 2013-02-02 04:23:05.560
STAT #11 id=1 cnt=22441 pid=0 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=51932 us)'
STAT #11 id=2 cnt=22441 pid=1 pos=1 obj=0 op='CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=30133 us)'
STAT #11 id=3 cnt=1 pid=2 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
CLOSE #11:c=1000,e=511,dep=0,type=0,tim=1359750185560442

可以看到解析花費了一點時間,其後的EXE沒有產生CPU TIME,也只有218的elapse time.

 

我們再來看看使用PL/SQL函數的SQL語句的trace:

PARSING IN CURSOR #10 len=66 dep=0 uid=84 oct=3 lid=84 tim=1359749602256931 hv=2764084342 ad='3a90d680' sqlid='9739cfkkc153q'
SELECT plsql_function(ROWNUM) t FROM dual CONNECT BY ROWNUM <= 1e6
END OF STMT
PARSE #10:c=7999,e=9102,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1731520519,tim=1359749602256928
EXEC #10:c=1000,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1731520519,tim=1359749602257357
FETCH #10:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1731520519,tim=1359749602257894
...
FETCH #10:c=95986,e=95756,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=1,plh=1731520519,tim=1359749617924141
STAT #10 id=1 cnt=1000000 pid=0 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=2208907 us)'
STAT #10 id=2 cnt=1000000 pid=1 pos=1 obj=0 op='CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=1259599 us)'
STAT #10 id=3 cnt=1 pid=2 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'

可以看到解析時間以及執行時間、FETCH都高於上面.

 

尤其是使用者自訂的函數,其效能會比系統內建函數更低,譬如有些開發的TX不知道11G的LISTAGG函數可以實現單列轉單行(有序串連列的各個元素),他們自己寫了函數來完成這個功能,最終其效能是不如系統內建的函數的.

1 2 下一頁