After a totally correct post from Coskan I had to rework it totally. I mixed up the problem and conclusio so much it was totally messed up.
I hope this one is the better one:
During some Tests with result cache in 11gR2 I discovered a small docu bug.
You can still see the bug in the 11gR1 docu:
In 11gR2 it sounds slightly different: 7.6.4.2 Additional Requirements for the Result Cache:
You cannot cache results when you use the following database objects or functions in your SQL query:
-
Dictionary and temporary tables
-
Sequence
CURRVAL
andNEXTVAL
pseudo columns -
SQL functions
current_date
,current_timestamp
,local_timestamp
,userenv/sys_context
(with non-constant variables),sys_guid
,sysdate
, andsys_timestamp
-
Non-deterministic PL/SQL functions
In 11gR2 it sounds slightly different: 7.6.4.2 Additional Requirements for the Result Cache:
You cannot cache results when the following objects or functions are in a query:
Temporary tables and tables in the
SYS
orSYSTEM
schemasSequence
CURRVAL
andNEXTVAL
pseudo columnsSQL functions
CURRENT_DATE
,CURRENT_TIMESTAMP
,LOCAL_TIMESTAMP
,USERENV/SYS_CONTEXT
(with non-constant variables),SYS_GUID
,SYSDATE
, andSYS_TIMESTAMP
for those of you who want to run the testcase:
create tablespace berx1 datafile '+DG' SIZE 50M;
create user berx1 identified by berx1;
grant connect to berx1;
grant unlimited tablespace to berx1;
create table sys.berx1_1 tablespace system as select * from dual;
create table sys.berx1_2 tablespace sysaux as select * from dual;
create table sys.berx1_3 tablespace berx1 as select * from dual;
create table system.berx1_4 tablespace system as select * from dual;
create table system.berx1_5 tablespace sysaux as select * from dual;
create table system.berx1_6 tablespace berx1 as select * from dual;
create table berx1.berx1_7 tablespace system as select * from dual;
create table berx1.berx1_8 tablespace sysaux as select * from dual;
create table berx1.berx1_9 tablespace berx1 as select * from dual;
grant select on sys.berx1_1 to berx1;
grant select on sys.berx1_2 to berx1;
grant select on sys.berx1_3 to berx1;
grant select on system.berx1_4 to berx1;
grant select on system.berx1_5 to berx1;
grant select on system.berx1_6 to berx1;
set autotrace on
-- run as user sys, system and berx1:
select /*+ result_cache */ * from sys.berx1_1;
select /*+ result_cache */ * from sys.berx1_2;
select /*+ result_cache */ * from sys.berx1_3;
select /*+ result_cache */ * from system.berx1_4;
select /*+ result_cache */ * from system.berx1_5;
select /*+ result_cache */ * from system.berx1_6;
select /*+ result_cache */ * from berx1.berx1_7;
select /*+ result_cache */ * from berx1.berx1_8;
select /*+ result_cache */ * from berx1.berx1_9;
my results are reflected in this matrix:
berx1_1 | berx1_2 | berx1_3 | berx1_4 | berx1_5 | berx1_6 | berx1_7 | berx1_8 | berx1_9 | |
sys | N | N | N | N | N | N | Y | Y | Y |
system | N | N | N | N | N | N | Y | Y | Y |
berx1 | N | N | N | N | N | N | Y | Y | Y |
So in my testcase it's not dependend of the schema (or the data dichtionary), but the tablespace.
This issue is addressed in BUG:8558309 and fixed in the latest docu :)
I know, I should never create any objects in SYS or SYSTEM schema. Especially if you want this object to be cached in result cache.
Once again thank you Coskan for showing my ridiculous error.
3 Kommentare:
Vielen Dank!
That is simple, relevant and actionable information. The best kind of content a blog can deliver.
Hi Martin
I think I am missing something or something is being missed?
"tables in the SYS or SYSTEM schemas"
Your test and same test on my machine only caches for tables under berx schema with running with any user am I wrong ?
So where is the dependency to tablespace here ? Only Berx schema tables worked which was expected and the dependecy is schema is that wrong ?
Coskan,
Thank you for the comment. I had to rework the entry totally. shame on me.
I hope it's more consistent, now.
Martin
Kommentar veröffentlichen