欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20181015]为什么是3秒.txt

程序员文章站 2022-03-02 17:17:37
[20181015]为什么是3秒.txt--//以前测试:连接http://blog.itpub.net/267265/viewspace-2144765/=>为什么是12秒.txt.--//很奇怪12.1.0.1版本测试12秒(windows版本),而11g是3秒(在使用标量子查询的情况下).不知 ......

[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版本不要在生产系统使用.