[20160919]Result cache问题.txt
--看了链接http://blog.dbi-services.com/result-cache-side-effects-on-number-of-calls/，重复测试：
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
SYS@book> grant execute on dbms_lock to scott;
Grant succeeded.
create or replace function F return number is
begin
dbms_lock.sleep(5);
dbms_output.put_line('Hello World');
return 255;
end;
/
SCOTT@book> set serveroutput on
SCOTT@book> set timing on
SCOTT@book> select f from dual;
F
----------
255
Hello World
Elapsed: 00:00:05.01
2.如果清除result_cache.
SCOTT@book> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SCOTT@book> select /*+ result_cache */ f from dual;
F
----------
255
Hello World
Hello World
Elapsed: 00:00:10.01
--使用提示result_cache,显示2次,说明调用2次.时间上也能说明问题，需要10秒完成。
SCOTT@book> select id, type, status, name from v$result_cache_objects;
ID TYPE STATUS NAME
---------- ---------------------------------------- --------- --------------------------------------------------
0 Dependency Published SCOTT.F
1 Result Published select /*+ result_cache */ f from dual
Elapsed: 00:00:00.00
--//再次执行，很快完成。
SCOTT@book> select /*+ result_cache */ f from dual ;
F
----------
255
Elapsed: 00:00:00.01
3.Note that if the function is declared as deterministic, it is executed only once.
--//加入deterministic
create or replace function F return number deterministic is
begin
dbms_lock.sleep(5);
dbms_output.put_line('Hello World');
return 255;
end;
/
exec dbms_result_cache.flush;
SCOTT@book> select /*+ result_cache */ f from dual ;
F
----------
255
Hello World
Elapsed: 00:00:05.01
--使用提示result_cache,显示1次,说明调用1次.时间上也能说明问题，需要5秒完成。说明参数deterministic作用。
SCOTT@book> select /*+ result_cache */ f from dual ;
F
----------
255
Elapsed: 00:00:00.01
--//再次执行很快完成，因为结果已经result cache。
SCOTT@book> select id, type, status, name from v$result_cache_objects;
ID TYPE STATUS NAME
---------- ---------------------------------------- --------- --------------------------------------------------
0 Dependency Published SCOTT.F
1 Result Published select /*+ result_cache */ f from dual
Elapsed: 00:00:00.00
4.如果修改如下:
create or replace function F return number RESULT_CACHE is
begin
dbms_lock.sleep(5);
dbms_output.put_line('Hello World');
return 255;
end;
/
SCOTT@book> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SCOTT@book> select /*+ result_cache */ f from dual;
F
----------
255
Hello World
Elapsed: 00:00:05.01
SCOTT@book> select id, type, status, name from v$result_cache_objects;
ID TYPE STATUS NAME
---------- ---------------------------------------- --------- --------------------------------------------------
0 Dependency Published SCOTT.F
2 Result Published "SCOTT"."F"::8."F"#9689ba467a19cd19 #1
1 Result Published select /*+ result_cache */ f from dual
Elapsed: 00:00:00.01
SCOTT@book> select /*+ result_cache */ f from dual;
F
----------
255
Elapsed: 00:00:00.00
--仅仅做一个记录，实际上以前也做过类似测试。