[20150924]result cache problem.txt

--昨天看了连接,看到一个关于result cache的例子,重复测试看看:
--链接 https://jonathanlewis.wordpress.com/2015/09/22/result-cache/

1.环境:
SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

set serveroutput on;

alter system flush shared_pool;

truncate table gtt1;
drop table gtt1;
create global temporary table gtt1 (n1 number) on commit preserve rows ;

insert into gtt1 values(1);
commit;

execute dbms_stats.gather_table_stats(user,'gtt1');

create or replace function f_cache
return number
result_cache
-- relies_on (gtt1)
is
        m_ret number;
begin
        select max(n1) into f_cache.m_ret from gtt1 ;
        return f_cache.m_ret;
end;
/

SCOTT@test> execute dbms_output.put_line(f_cache);
1

PL/SQL procedure successfully completed.

2.测试1:
--session A:
set serveroutput on
SCOTT@test> execute dbms_output.put_line(f_cache);
1

PL/SQL procedure successfully completed.
--这个正确！

--session B:
set serveroutput on
SCOTT@test> execute dbms_output.put_line(f_cache);
1

PL/SQL procedure successfully completed.
--而这里存在问题。

SCOTT@test> insert into gtt1 values(0);
1 row created.

SCOTT@test> execute dbms_output.put_line(f_cache);
0
PL/SQL procedure successfully completed.

3.测试2:

--Session A:
SQL> truncate table gtt1;
Table truncated.

SQL> execute dbms_output.put_line(f_cache);
1

SCOTT@test> execute dbms_output.put_line(f_cache);
1

PL/SQL procedure successfully completed.
--已经truncate ，依旧存在输出，而是还是1.

--session B:
Session B (where I hadn't yet committed):

SCOTT@test> commit;
Commit complete.

--Session A (where I've done nothing new):

SCOTT@test> execute dbms_output.put_line(f_cache);
PL/SQL procedure successfully completed.

The row has finally "disappeared" because session B committed.

--Session B (where I haven't done anything since committing):

SCOTT@test> execute dbms_output.put_line(f_cache);
PL/SQL procedure successfully completed.

--而实际上这是应该输出0.
--当然这个情况很特殊，gtt1是临时表。仅仅对会话有效，做一个记录。