[20181015]为什么是3秒.txt

[20181015]为什么是3秒.txt --//以前测试:连接http://blog.itpub.net/267265/viewspace-2144765/=>为什么是12秒.txt. --//很奇怪12.1.0.1版本测试12秒(windows版本),而11g是3秒(在使用标量子查询的情况下).不知道为什么? --//在12.2.0.1下测试看看: 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 SYS@test01p> grant EXECUTE ON  dbms_lock to scott; Grant succeeded. 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; / 3.测试: SCOTT@test01p> set timing on SCOTT@test01p> set feedback only SCOTT@test01p> select empno, ename, deptno, get_dept(deptno) c20 from emp;      EMPNO ENAME          DEPTNO C20 ---------- ---------- ---------- -------------------- 14 rows selected. Elapsed: 00:00:14.00 --//14秒,这是正确的,14条记录.调用14次需要14秒. --//换成标量子查询: SCOTT@test01p> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;      EMPNO ENAME          DEPTNO C20 ---------- ---------- ---------- -------------------- 14 rows selected. Elapsed: 00:00:03.03 --//执行时间是3秒,这次是正确的,因为标量子查询缓存结果,而仅仅有3个部门在emp表.这样3秒就正确了. 4.继续探究: SCOTT@test01p> set timing off SCOTT@test01p> alter session set statistics_level=all; Session altered. SCOTT@test01p> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;      EMPNO ENAME          DEPTNO C20 ---------- ---------- ---------- -------------------- 14 rows selected. SCOTT@test01p> set feedback on SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  apagxtf1p2puy, child number 1 ------------------------------------- select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp Plan hash value: 1340320406 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |     9 (100)|          |     14 |00:00:00.01 |       8 | |   1 |  FAST DUAL        |      |      3 |      1 |       |     2   (0)| 00:00:01 |      3 |00:00:00.01 |       0 | |   2 |  TABLE ACCESS FULL| EMP  |      1 |     14 |   182 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       8 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$2 / DUAL@SEL$2    2 - SEL$1 / EMP@SEL$1 --//从执行计划也可以发现fast dual执行了3.再次说明12.1版本有问题. --//也再次说明oracle任何XX.1版本不能在生产系统使用. 5.继续测试使用 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@test01p> show array arraysize 200 --//arraysize=200 SCOTT@test01p> set timing on SCOTT@test01p> select empno, ename, deptno, get_dept(deptno) c20  from emp; ... 14 rows selected. Elapsed: 00:00:04.06 --//这次正确了4秒.大家可以自行设置array=2等各种情况. --//为什么?大家可以看看我写的.http://blog.itpub.net/267265/viewspace-2138042/=>[20170426]为什么是4秒.txt 6.最后补充测试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@test01p> select empno, ename, deptno, get_dept(deptno) c20  from emp; 14 rows selected. Elapsed: 00:00:03.07 SCOTT@test01p> select empno, ename, deptno, get_dept(deptno) c20  from emp; Elapsed: 00:00:00.07 --//第1次执行3秒,第2次执行0秒,因为结果缓存了.第二次执行直接取结果.修改如下结果一样. CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)    RETURN dept.dname%TYPE    RESULT_CACHE    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; / 总结: --//再次验证我以前的结论oracle 任何xx.1版本不要在生产系统使用.