PL/SQL function result cache for Oracle 11g

Two test samples were made in the art of simulating Oracle performance diagnostics. The book says not to bear relies_on. Cache (Result_cache relies_on (test1,test2)). The test proved incorrect, and the function F1 () did not use relies_on, but the changes on the table affected the function.

C:\Documents and Settings\guogang>sqlplus Gg_test/[email Protected]_gg

Sql*plus:release 10.2.0.1.0-production on Monday August 4 19:46:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connect to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
Sql> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production

sql> drop table Test1 Purge;
sql> drop table Test2 purge;
Sql> CREATE TABLE Test1 as SELECT * from Dba_objects;
Sql> CREATE TABLE Test2 as SELECT * from All_objects;
Sql> Select COUNT (*) from test1;
COUNT (*)
----------
74144
Sql> Select COUNT (*) from test2;
COUNT (*)
----------
73248


sql> Create or Replace function F1
return number
Is
L_ret number;
Begin
Select COUNT (*) into L_ret
From Test1,test2
where Test1.object_type = Test2.object_type
and Test1.object_type in (' Table subpartition ', ' VIEW ', ' INDEX ', ' table ');
return l_ret;
End
/
The function has been created.

Sql> Set Timing on
Sql> Select F1 () from dual;
F1 ()
----------
60681409

Time used: 00:00:07.29

--Disable result caching

sql> execute dbms_result_cache. Bypass (bypass_mode=>true,session=>true);
Sql> Select F1 () from dual;
F1 ()
----------
60681409

Time used: 00:00:03.60

--Enable result caching

sql> execute dbms_result_cache. Bypass (bypass_mode=>false,session=>true);
Sql> Select F1 () from dual;
F1 ()
----------
60681409
Time used: 00:00:00.00


Sql> Delete from test1 where object_type = ' VIEW ' and rownum <100;
Sql> Delete from test2 where object_type = ' VIEW ' and rownum <100;
Sql> commit;
Sql> Select F1 () from dual;
F1 ()
----------
59788330

Used time: 00:00:07.09 -can see the data change, even if not using relies_on, the result set is also correct.

Sql> Select COUNT (*)
From Test1, test2
where Test1.object_type = Test2.object_type
and Test1.object_type in (' Table subpartition ', ' VIEW ', ' INDEX ', ' table ');
COUNT (*)
----------
59788330
Time used: 00:00:03.56


sql> Create or Replace function F2
return number
Result_cache relies_on (TEST1,TEST2)
Is
L_ret number;
Begin
Select COUNT (*) into L_ret
From Test1,test2
where Test1.object_type = Test2.object_type
and Test1.object_type in (' Table subpartition ', ' VIEW ', ' INDEX ', ' table ');
return l_ret;
End
/
The function has been created.


Sql> Select F2 () from dual;
F2 ()
----------
59788330
time used: 00:00:03.54
Sql> Select F2 () from dual;
F2 ()
----------
59788330
time used: 00:00:00.00

Sql> Delete from test1 where object_type = ' VIEW ' and rownum <100;
Sql> Delete from test2 where object_type = ' VIEW ' and rownum <100;
Sql> commit;
Sql> Select F2 () from dual;
F2 ()
----------
58914853

Time used: 00:00:03.50

sql> Select COUNT (*)
From Test1, test2
where Test1.object_type = Test2.object_type
and Test1.object_type in (' Table subpartition ', ' VIEW ', ' INDEX ', ' table ');
COUNT (*)
----------
58914853
time used: 00:00:03.50

Copyright notice: This article Bo Master original article. Blog, not reproduced without consent.

PL/SQL function result cache for Oracle 11g