ITPub博客

首页 > 数据库 > Oracle > [20170426]为什么是4秒.txt

[20170426]为什么是4秒.txt

原创 Oracle 作者:lfree 时间:2017-04-26 15:25:14 0 删除 编辑

[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数量...,有谁能给出合理解析呢?

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2421
  • 访问量
    6188506