[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

--仅仅做一个记录，实际上以前也做过类似测试。