SQL函数存在的问题

很多不了解Oracle数据库的开发人员很喜欢用PL/SQL的函数、存储等来达到代码上的简洁.推荐阅读：使用PL/SQL执行java存储来获得MAC地址 http://www.linuxidc.com/Linux/2013-04/83498.htm如: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 ASV_DEPTINFO VARCHAR2（50）;BEGINIF PTYPE="DNAME" THENSELECT DNAME INTO V_DEPTINFO FROM DEPT WHERE DEPTNO=PDEPTNO;END IF;IF PTYPE="LOC" THENSELECT 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.03Statistics
----------------------------------------------------------
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.50Statistics
----------------------------------------------------------
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函数可以实现单列转单行（有序连接列的各个元素）,他们自己写了函数来完成这个功能,最终其性能是不如系统自带的函数的.

使用PL/SQL执行OS命令解决 ORA-00060: Deadlock detected 小例相关资讯 PL/SQL

PL/SQL之存储过程和函数（今 14:09）
PL/SQL Developer连接本地Oracle （07月27日）
【PL/SQL系列】Oracle存储过程使用（04月23日）

PL/SQL Developer 使用技巧分享（09月16日）
PL/SQL实现Java中的split（）方法的（07月10日）
从一个案例看PL/SQL代码片的编译与（03月04日）

本文评论查看全部评论（0）