Function Result Cache Demo

View All Scripts Login to Run Script

Script Name Function Result Cache Demo
Description Demonstrate the use and performance advantage of function result cache
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Binuraj Somanathan Nair (http://oracleappsdiary.blogspot.co.uk)
Created Friday October 30, 2015

Statement 1

Create Demo table

create table employees(emp_no number, name varchar2(30))

Table created.

Statement 2

Populate Demo table with 1000 records

begin
for i in 1..1000 loop
    insert into employees values (i,'Employee'||i);
end loop;
commit;
end;

Statement 3

Custom sleep procedure since there is no permission for DBMS_LOCK.SLEEP

Create s sleep procedure

CREATE OR REPLACE PROCEDURE sleep(p_milli_seconds IN NUMBER) IS
  l_date                      DATE := SYSDATE;
  l_differen_in_milli_seconds NUMBER;
BEGIN
  LOOP
    SELECT SYSDATE - l_date INTO l_differen_in_milli_seconds FROM dual;
    EXIT WHEN l_differen_in_milli_seconds >((1 / 24 / 60 / 60 / 1000) * p_milli_seconds);
  END LOOP;
END sleep;

Statement 4

The function will fetch and the employee number. It also sleeps for a second. So each call last for 1 second

Create function to fetch data

CREATE OR REPLACE FUNCTION get_emp_no(p_emp_name IN VARCHAR2) RETURN NUMBER IS
  l_empno NUMBER;
BEGIN

  SELECT emp_no INTO l_empno FROM employees WHERE NAME = p_emp_name;

  sleep(100);/*Introducing a delay of 100 milliseconds to demonstrate*/

  RETURN l_empno;

END get_emp_no;

Statement 5

The function will be called 20 times with 5 different parameters. Each execution takes 1 second each. Thus expected to complete in 20 seconds

Call the function multiple times

DECLARE
emp_no NUMBER;
l_date date :=sysdate;
l_execution_seconds number ;
begin 
/* Call the function 20 times, but only with 5 different values (logic introduced with mod operator below)*/
   for i in 1..20 loop 
     emp_no:=  get_emp_no ('Employee'||((i MOD 5)+1));
   end loop; 
   l_execution_seconds := round((sysdate-l_date)*24*60*60,2);
   dbms_output.put_line ('Execution complete in '|| l_execution_seconds || ' seconds');
end;

Statement 6

The RESULT_CACHE clause will ensure that the function and result will be stored in the cache. The result will be retrieved from cache if invoked with the same parameter for a second time.

Add RESULT_CACHE clause to the function

CREATE OR REPLACE FUNCTION get_emp_no(p_emp_name IN VARCHAR2) RETURN NUMBER RESULT_CACHE IS
  l_empno NUMBER;
BEGIN

  SELECT emp_no INTO l_empno FROM employees WHERE NAME = p_emp_name;

  sleep(100);/*Introducing a delay of 100 milliseconds to demonstrate*/

  RETURN l_empno;

END get_emp_no;

Statement 7

The function will be called 20 times with 5 different parameters. However the code inside the function will be invoked only the first time a new parameter is passed. For the rest of the times, data will be fetched from the cache. Thus the execution will take about 5 seconds.

Test the Result Cache function

DECLARE
emp_no NUMBER;
l_date date :=sysdate;
l_execution_seconds number ;
begin 
/* Call the function 20 times, but only with 5 different values (logic introduced with mod operator below)*/
   for i in 1..20 loop 
     emp_no:=  get_emp_no ('Employee'||((i MOD 5)+1));
   end loop; 
   l_execution_seconds := round((sysdate-l_date)*24*60*60,2);
   dbms_output.put_line ('Execution complete in '|| l_execution_seconds || ' seconds');
end;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database