Oracle技术之利用DETERMINISTIC声明提高性能（一）

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

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

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

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

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

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

不过这里要澄清以前一个错误的观点，由于设置ARRAY为1后，访问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的具体实现有关系了。

对于DETERMINISTIC和ARRAY的关系以前也描述过，这里不详细展开了，下面看看当参数发生变化的情况：

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

可以看到，仅仅是两个不同参数的交替出现，在ARRAY为5的情况下，Oracle的DETERMINISTIC的调用方式已经很复杂了，不过有一定是确定的，当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值越大，对于性能的提高就越大。

oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html

转载于:https://blog.51cto.com/19880614/1243557