Oracle 11g PL/SQL function result cache, oracle11g

Source: Internet
Author: User

Oracle 11g PL/SQL function result cache, oracle11g

Two experiments have been conducted to simulate the Oracle Performance Diagnosis art. The book says that if you don't need RELIES_ON, then the changes to the objects on which the function depends will not result in the invalid result cache operation (result_cache RELIES_ON (test1, test2). The test proves that this operation is incorrect. The function f1 () does not use RELIES_ON, however, changes in the table affect the function.

C: \ Documents and Settings \ guogang> sqlplus gg_test/gg_test@10.10.15.25 _ gg

SQL * Plus: Release 10.2.0.1.0-Production on Monday August 4 19:46:44 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect:
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
Rochelle 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

Used time: 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

Used time: 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
Used time: 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

Time used: 00: 00: 07.09 -- you can see that the data has changed. Even if you do not use RELIES_ON, The result set is 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
Used time: 00: 00: 03.56



SQL> create or replace function f2
Return number
Result_cache RELIES_ON (test1, test2)
Is
Rochelle 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
Used time: 00: 00: 03.54
SQL> select f2 () from dual;
F2 ()
----------
59788330
Used time: 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

Used time: 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
Used time: 00: 00: 03.50
Oracle 11g pl/SQL

If the updated results are pre-displayed, execute the following statement:
Select deptno, empno, sal, decode (deptno, 10, '5% ', 20, '000000', 30, '000000') "sal % ",
Decode (deptno, 10, sal * 1.05, 20, sal * 1.1, 30, sal * 1.15) newsal
From emp order by deptno, empno;
To update and display the data first, you must add the updated column newsal to the table structure. Otherwise, the pre-Update salary cannot be output.
Update emp x newset sal = (select decode (deptno, 10, sal * 1.05, 20, sal * 1.1, 30, sal * 1.15)
From emp where empno = x. empno );
Display: select deptno, empno, sal, decode (deptno, 10, '5% ', 20, '123', 30, '123') "sal %", newsal
From emp order by deptno, empno;

PL/SQL query cache Problems

To clear the cache before each query, try:
10 Gb or above:
Alter system flush buffer_cache;
9i:
Alter session set events 'immediate trace name flush_cache ';
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.