[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是临时表。仅仅对会话有效，做一个记录。