[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

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