[20170426]为什么是4秒.txt
--//在开发程序时我一般会强调开发尽量不要写一些自定义函数，往往可能导致CPU忙。今天测试遇到一个问题:
--//原始的测试来之nimishgarg.blogspot.com/2016/03/avoiding-unnecessary-function-calls-to.html
--//先重复测试。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.普通函数:
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);
SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;
RETURN l_dname;
END;
/
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
EMPNO ENAME DEPTNO C20
---------- ---------- ---------- --------------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
Elapsed: 00:00:14.02
--//这样每一次调用都需要1秒。如果改用标量子查询。
SCOTT@book> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;
EMPNO ENAME DEPTNO C20
---------- ---------- ---------- --------------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
Elapsed: 00:00:03.01
--//因为标量子查询，缓存了相同的结果，deptno仅仅有3个值，这样需要3秒。自己还真没想到标量子查询有这样效果!!
3. DETERMINISTIC Functions:
--//一般如果在在某个函数定义索引，需要DETERMINISTIC,表示返回结果固定。其实即使不固定，也可以这样定义。
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
DETERMINISTIC
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);
SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;
RETURN l_dname;
END;
/
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
EMPNO ENAME DEPTNO C20
---------- ---------- ---------- --------------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
Elapsed: 00:00:04.02
--//4秒，为什么呢？仅仅3个值，按照道理应该3秒，而不是4秒，这个问题先放在后面解析。
4.RESULT CACHE
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
RESULT_CACHE
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);
SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;
RETURN l_dname;
END;
/
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
..
14 rows selected.
Elapsed: 00:00:03.01
--//再次执行因为结果缓存了。很快返回。
SCOTT@book> select empno, ename, deptno, get_dept(deptno) dname from emp;
14 rows selected.
Elapsed: 00:00:00.00
5.回到前面，采用 DETERMINISTIC Functions为什么是4秒呢？
--//重新定义函数
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
DETERMINISTIC
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);
SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;
RETURN l_dname;
END;
/
--//如果仔细阅读原文：
As we can see here that execution time went down to near 4 seconds which was originally near 14 seconds. We can notice
that using scalar sub-query was little faster because Deterministic cache is maintained per fetch instead of total SQL
execution. I prefer to modify my function to deterministic only when I know that this function is used in various SQLs
and all SQL can not be modified (or required alot of effort) with scalar sub-query.
SCOTT@book> show array
arraysize 200
--//仔细看就明白了，实际与array大小有关，这样就很容易理解为什么是4秒，因为返回第1行是单独1个逻辑读。
--如果我设置array=2
20 1
30 30 1
20 30 2
30 10 2
20 10 2
30 20 2
30 20 2
10 1
--//这样13秒验证是否正确。
SCOTT@book> set array 2
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
14 rows selected.
Elapsed: 00:00:13.02
--//如果我设置array=3
20 1
30 30 20 2
30 30 10 2
20 10 30 3
20 30 20 2
10 1
--//这样11秒验证是否正确。
SCOTT@book> set array 3
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
...
14 rows selected.
Elapsed: 00:00:11.02
--//如果我设置array=4
set array 4
20 1
30 30 20 30 2
30 10 20 10 3
30 20 30 20 2
10 1
--//应该是9秒：
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp ;
14 rows selected.
Elapsed: 00:00:09.01
--//如果你仔细观察输出时的停顿，可以发现是4条4条输出的，这里非常不好理解!!
--//但是如果改用标量子查询，结果就是3秒。
SCOTT@book> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;
14 rows selected.
Elapsed: 00:00:03.01
--//还有1个简单的验证方法就是排序输出看看。
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp order by deptno;
14 rows selected.
Elapsed: 00:00:03.01
--//这样数据先进入排序区，arraysize大小就变得没有关系，有回到3秒。
--//其中细节可以自己体会。
--//这个测试终于让我明白oracle一些逻辑读细节。
总结
1.oracle 逻辑读不能跨块。
2.oracle 逻辑读第1行作为一个逻辑读,接着读取数量array作为第2个逻辑读。
3.做一个特殊情况：
SCOTT@book> create table empx as select * from emp order by deptno;
Table created.
SCOTT@book> select * from empx ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
14 rows selected.
SCOTT@book> set array 7
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from empx ;
EMPNO ENAME DEPTNO C20
---------- ---------- ---------- --------------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected.
Elapsed: 00:00:04.02
10 1
10 10 20 20 20 20 20 2
30 30 30 30 30 30 1
--//如果设置6，多2秒。
SCOTT@book> set array 6
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from empx ;
EMPNO ENAME DEPTNO C20
---------- ---------- ---------- --------------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected.
Elapsed: 00:00:06.01
10 1
10 10 20 20 20 20 2
20 30 30 30 30 30 2
30 1
--//OK正确。
4.不过我自己还是有一点不明白的，oracle的输出是按照array定义的数量输出的，不知道为什么?
按照我的理解输出应该是1,array数量-1,array数量...,有谁能给出合理解析呢？