ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用DETERMINISTIC声明提高性能(一)

利用DETERMINISTIC声明提高性能(一)

原创 Linux操作系统 作者:yangtingkun 时间:2011-05-25 23:44:59 0 删除 编辑

虽然Oracle提供的DETERMINISTIC声明,本意是确保函数的确定性,但是如何合理利用,是可以用来提高性能的。

这一篇描述ARRAY对性能的影响。

Deterministic函数:http://yangtingkun.itpub.net/post/468/26793

Deterministic函数(二):http://yangtingkun.itpub.net/post/468/394107

Oracle11新特性——PLSQL函数缓存结果(二):http://yangtingkun.itpub.net/post/468/394111

 

 

关于DETERMINISTIC函数,以前已经写过一些文章了,不过对于DETERMINISTIC声明用来提高性能只是简单提了一句,并没有展开来说。

由于函数声明了DETERMINISTIC特性,Oracle对于相同的输入,可以只运行一次,而这对于代码比较复杂,调用时间较长的函数而言,确实可以提高性能。

但是在上面的几篇文章中也提到了,DETERMINISTIC是基于调用的,因此使用DETERMINISTIC不但与输入参数是否重复有关,也与SQL调用次数有关。

一个前面文章提到过的简单的例子就是sqlplus的数组方式FETCH数据,不同的array的值,就会影响DETERMINISTIC函数的运行次数,哪怕

不过这里要澄清以前一个错误的观点,由于设置ARRAY1后,访问DETERMINISTIC函数发现每两条记录调用一次,当时认为ARRAY方式的最小值是2,但是现在发现,问题和ARRAY无关,导致问题的原因和DETERMINISTIC的实现算法有关。

由于DETERMINISTIC并不像RESULT_CACHE那样,在单独的内存区域中保存每次调用的结果,因此Oracle需要判断DETERMINISTIC函数两次输入是否一样,这对于输入参数相同的情况还简单一些,但是对于包含大量变化的变量,就使得函数的调用次数很难预料。

看一个简单的例子来说明这个问题:

SQL> CREATE OR REPLACE FUNCTION F_DETER (V_IN NUMBER)
2 RETURN NUMBER DETERMINISTIC AS
3 BEGIN
4 DBMS_LOCK.SLEEP(1);
5 DBMS_OUTPUT.PUT_LINE(V_IN);
6 RETURN V_IN;
7 END;
8 /

函数已创建。

SQL> CREATE TABLE T_DETER (ID NUMBER, C NUMBER);

表已创建。

SQL> INSERT INTO T_DETER
2 SELECT ROWNUM, 1
3 FROM TAB;

已创建15行。

SQL> SET SERVEROUT ON
SQL> SET TIMING ON
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

已用时间: 00: 00: 00.09
SQL> SHOW ARRAY
arraysize 15
SQL> SELECT ID, F_DETER(C), SYSDATE FROM T_DETER;

ID F_DETER(C) SYSDATE
---------- ---------- -------------------
1 1 2011-05-26 08:10:54
2 1 2011-05-26 08:10:54
3 1 2011-05-26 08:10:54
4 1 2011-05-26 08:10:54
5 1 2011-05-26 08:10:54
6 1 2011-05-26 08:10:54
7 1 2011-05-26 08:10:54
8 1 2011-05-26 08:10:54
9 1 2011-05-26 08:10:54
10 1 2011-05-26 08:10:54
11 1 2011-05-26 08:10:54
12 1 2011-05-26 08:10:54
13 1 2011-05-26 08:10:54
14 1 2011-05-26 08:10:54
15 1 2011-05-26 08:10:54

已选择15行。

1
1
已用时间: 00: 00: 02.80
SQL> CREATE OR REPLACE FUNCTION F_SYSDATE RETURN DATE AS
2 BEGIN
3 RETURN SYSDATE;
4 END;
5 /

函数已创建。

已用时间: 00: 00: 00.04
SQL> SELECT ID, F_DETER(C), F_SYSDATE FROM T_DETER;

ID F_DETER(C) F_SYSDATE
---------- ---------- -------------------
1 1 2011-05-26 08:11:13
2 1 2011-05-26 08:11:14
3 1 2011-05-26 08:11:14
4 1 2011-05-26 08:11:14
5 1 2011-05-26 08:11:14
6 1 2011-05-26 08:11:14
7 1 2011-05-26 08:11:14
8 1 2011-05-26 08:11:14
9 1 2011-05-26 08:11:14
10 1 2011-05-26 08:11:14
11 1 2011-05-26 08:11:14
12 1 2011-05-26 08:11:14
13 1 2011-05-26 08:11:14
14 1 2011-05-26 08:11:14
15 1 2011-05-26 08:11:14

已选择15行。

1
1
已用时间: 00: 00: 02.18

由于对于SYSDATE函数,Oracle做了优化,因为查询中看到的是同一个时间,将SYSDATE封装到一个函数中,可以看到,即使对于ARRAY设置为15的情况,在同一次的调用中,输入参数完全相同,DETERMINISTIC函数也会调用两次。这个就与DETERMINISTIC的具体实现有关系了。

对于DETERMINISTICARRAY的关系以前也描述过,这里不详细展开了,下面看看当参数发生变化的情况:

SQL> UPDATE T_DETER SET C = MOD(ROWNUM, 2);

已更新15行。

已用时间: 00: 00: 00.10
SQL> SELECT ID, F_DETER(C), F_SYSDATE FROM T_DETER;

ID F_DETER(C) F_SYSDATE
---------- ---------- -------------------
1 1 2011-05-26 08:46:02
2 0 2011-05-26 08:46:03
3 1 2011-05-26 08:46:04
4 0 2011-05-26 08:46:04
5 1 2011-05-26 08:46:04
6 0 2011-05-26 08:46:04
7 1 2011-05-26 08:46:04
8 0 2011-05-26 08:46:04
9 1 2011-05-26 08:46:04
10 0 2011-05-26 08:46:04
11 1 2011-05-26 08:46:04
12 0 2011-05-26 08:46:04
13 1 2011-05-26 08:46:04
14 0 2011-05-26 08:46:04
15 1 2011-05-26 08:46:04

已选择15行。

1
0
1
已用时间: 00: 00: 03.22
SQL> SET ARRAY 5
SQL> SELECT ID, F_DETER(C), F_SYSDATE FROM T_DETER;

ID F_DETER(C) F_SYSDATE
---------- ---------- -------------------
1 1 2011-05-26 08:46:50
2 0 2011-05-26 08:46:51
3 1 2011-05-26 08:46:52
4 0 2011-05-26 08:46:52
5 1 2011-05-26 08:46:52
6 0 2011-05-26 08:46:52
7 1 2011-05-26 08:46:53
8 0 2011-05-26 08:46:54
9 1 2011-05-26 08:46:54
10 0 2011-05-26 08:46:54
11 1 2011-05-26 08:46:54
12 0 2011-05-26 08:46:55
13 1 2011-05-26 08:46:56
14 0 2011-05-26 08:46:56
15 1 2011-05-26 08:46:56

已选择15行。

1
0
1
1
0
0
1
已用时间: 00: 00: 07.50
SQL> SET ARRAY 2
SQL> SELECT ID, F_DETER(C), F_SYSDATE FROM T_DETER;

ID F_DETER(C) F_SYSDATE
---------- ---------- -------------------
1 1 2011-05-26 08:48:25
2 0 2011-05-26 08:48:26
3 1 2011-05-26 08:48:27
4 0 2011-05-26 08:48:28
5 1 2011-05-26 08:48:29
6 0 2011-05-26 08:48:30
7 1 2011-05-26 08:48:31
8 0 2011-05-26 08:48:32
9 1 2011-05-26 08:48:33
10 0 2011-05-26 08:48:34
11 1 2011-05-26 08:48:35
12 0 2011-05-26 08:48:36
13 1 2011-05-26 08:48:37
14 0 2011-05-26 08:48:38
15 1 2011-05-26 08:48:39

已选择15行。

1
0
1
0
1
0
1
0
1
0
1
0
1
0
1
已用时间: 00: 00: 15.38

可以看到,仅仅是两个不同参数的交替出现,在ARRAY5的情况下,OracleDETERMINISTIC的调用方式已经很复杂了,不过有一定是确定的,当ARRARY设置为2时,DETERMINISTIC将完全失效,这种情况下,得不到任何性能上的好处。

而如果不同参数的个数设置为3个,则情况会更加复杂:

SQL> UPDATE T_DETER SET C = MOD(ROWNUM, 3);

已更新15行。

已用时间: 00: 00: 00.03
SQL> SET ARRAY 15
SQL> SELECT ID, F_DETER(C), F_SYSDATE FROM T_DETER;

ID F_DETER(C) F_SYSDATE
---------- ---------- -------------------
1 1 2011-05-26 08:55:59
2 2 2011-05-26 08:56:00
3 0 2011-05-26 08:56:01
4 1 2011-05-26 08:56:02
5 2 2011-05-26 08:56:02
6 0 2011-05-26 08:56:02
7 1 2011-05-26 08:56:02
8 2 2011-05-26 08:56:02
9 0 2011-05-26 08:56:02
10 1 2011-05-26 08:56:02
11 2 2011-05-26 08:56:02
12 0 2011-05-26 08:56:02
13 1 2011-05-26 08:56:02
14 2 2011-05-26 08:56:02
15 0 2011-05-26 08:56:02

已选择15行。

1
2
0
1
已用时间: 00: 00: 04.25
SQL> SET ARRAY 5
SQL> SELECT ID, F_DETER(C), F_SYSDATE FROM T_DETER;

ID F_DETER(C) F_SYSDATE
---------- ---------- -------------------
1 1 2011-05-26 08:56:19
2 2 2011-05-26 08:56:20
3 0 2011-05-26 08:56:21
4 1 2011-05-26 08:56:22
5 2 2011-05-26 08:56:22
6 0 2011-05-26 08:56:22
7 1 2011-05-26 08:56:23
8 2 2011-05-26 08:56:24
9 0 2011-05-26 08:56:25
10 1 2011-05-26 08:56:25
11 2 2011-05-26 08:56:25
12 0 2011-05-26 08:56:26
13 1 2011-05-26 08:56:27
14 2 2011-05-26 08:56:28
15 0 2011-05-26 08:56:28

已选择15行。

1
2
0
1
1
2
0
0
1
2
已用时间: 00: 00: 10.19

虽然DETERMINISTIC的调用实现很复杂,但是这里至少可以得出一个结论,如果希望通过DETERMINISTIC来获取性能收益,那么采用批量方式是必须的,而且数组的值相对而言越大对于性能的帮助会越大,因此每次数组获取数据,被认为是一次调用,而DETERMINISTIC函数主要是在一次调用中生效。当然根据上面测试的结果,跨调用的生效也是可能存在的,但是多次调用必然会导致函数更多次数的调用,如果仅从DETERMINISTIC函数的调用效果考虑,ARRAY值越大,对于性能的提高就越大。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-696312/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10651825