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
