Function Result Cache Performance: A Demonstration

View All Scripts Login to Run Script

Script Name Function Result Cache Performance: A Demonstration
Description Let's see just how much of a benefit one can get from the function result cache! This script compares the performance of an uncached lookup via a function (select from table each time); a PGA cached lookup (package body persistent data); and the function result cache.
Area PL/SQL General
Contributor Steven Feuerstein
Created Friday September 21, 2018

Statement 1

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2

Traditional Approach to Row Retrieval

CREATE OR REPLACE PACKAGE emplu1 
IS 
   FUNCTION onerow ( 
      employee_id_in   IN employees.employee_id%TYPE) 
      RETURN employees%ROWTYPE; 
END;

Package created.

Statement 3

Need a row? Execute a SELECT-INTO. Oracle Database performs all sort of optimizations, but still you have to navigate through the SQL layer. If the data hasn't changed since last retrieval, why do that?

Query Every Time

CREATE OR REPLACE PACKAGE BODY emplu1 
IS 
   FUNCTION onerow ( 
      employee_id_in   IN employees.employee_id%TYPE) 
      RETURN employees%ROWTYPE 
   IS 
      onerow_rec   employees%ROWTYPE; 
   BEGIN 
      SELECT * 
        INTO onerow_rec 
        FROM employees 
       WHERE employee_id = employee_id_in; 
 
      RETURN onerow_rec; 
   END; 
END;

Package Body created.

Statement 4

Looks the same on the outside

A PGA Caching Package

CREATE OR REPLACE PACKAGE emplu2 
IS 
   FUNCTION onerow ( 
      employee_id_in   IN employees.employee_id%TYPE) 
      RETURN employees%ROWTYPE; 
END;

Package created.

Statement 5

In this implementation, I use the initialization section to load the table into a collation in PGA memory (one copy per connection!), indexed by the primary key. Then the onerow function is nothing but a retrieval from the collection. Super-duper fast, but only really useful for fully-static datasets. Plus, it does not scale well and is not well suited for stateless applications (which are most of what we build nowadays).

Super Fast, PGA-Based Lookup

CREATE OR REPLACE PACKAGE BODY emplu2 
IS 
   TYPE employee_tt IS TABLE OF employees%ROWTYPE 
      INDEX BY PLS_INTEGER; 
 
   employee_cache   employee_tt; 
 
   FUNCTION onerow ( 
      employee_id_in   IN employees.employee_id%TYPE) 
      RETURN employees%ROWTYPE 
   IS 
   BEGIN 
      RETURN employee_cache (employee_id_in); 
   END onerow; 
 
   PROCEDURE load_cache 
   IS 
   BEGIN 
      FOR rec IN (SELECT * FROM employees) 
      LOOP 
         employee_cache (rec.employee_id) := rec; 
      END LOOP; 
   END load_cache; 
BEGIN 
   load_cache; 
END emplu2;

Package Body created.

Statement 6
One of the things I like best about the result cache is that as a developer "all you do" is add one keyword to spec and body. Done. Assuming you've worked closely with your DBA, and made a sensible choice about the caching. :-)
Add That Keyword!
```
CREATE OR REPLACE PACKAGE emplu11g 
IS 
   FUNCTION onerow ( 
      employee_id_in   IN employees.employee_id%TYPE) 
      RETURN employees%ROWTYPE 
      RESULT_CACHE; 
END;
```
```
Package created.
```

Statement 7

Yep, just add the keyword and that's it. You've just handed Oracle Database another opportunity to use one of the many tools at its disposal to speed up your application.

No Change to Body of Function

CREATE OR REPLACE PACKAGE BODY emplu11g 
IS 
   FUNCTION onerow ( 
      employee_id_in   IN employees.employee_id%TYPE) 
      RETURN employees%ROWTYPE 
      RESULT_CACHE   
   IS 
      onerow_rec   employees%ROWTYPE; 
   BEGIN 
      SELECT * 
        INTO onerow_rec 
        FROM employees 
       WHERE employee_id = employee_id_in; 
 
      RETURN onerow_rec; 
   END; 
END;

Package Body created.

Statement 8

Compare performance of repeated querying of data to caching in the PGA (packaged collection) and the Oracle Database 11g Result Cache. The PGA approach should be fastest, but you will be amazed at how close you get with the result cache, and how much faster it is than repeated SELECT execution!

Compare Performance of Different Approaches

CREATE OR REPLACE PROCEDURE test_emplu ( 
   counter          IN   INTEGER 
 , employee_id_in   IN   employees.employee_id%TYPE := 138 
) 
IS 
   emprec   employees%ROWTYPE; 
 
   l_start TIMESTAMP;  
  
   PROCEDURE start_timer  
   IS  
   BEGIN  
      l_start := SYSTIMESTAMP;  
   END start_timer;  
  
   PROCEDURE show_elapsed_time ( message_in IN VARCHAR2 )  
   IS  
   BEGIN  
      DBMS_OUTPUT.put_line ( 
            CASE 
               WHEN message_in IS NULL THEN 'Completed in:' 
               ELSE '"' || message_in || '" completed in: ' 
            END 
         || REGEXP_SUBSTR (SYSTIMESTAMP - l_start,  
               '([1-9][0-9:]*|0)\.\d{3}') 
         || ' seconds'); 
 
      /* Reset timer */ 
      start_timer; 
   END show_elapsed_time; 
BEGIN 
   start_timer; 
 
   FOR i IN 1 .. counter 
   LOOP 
      emprec := emplu11g.onerow (employee_id_in); 
   END LOOP; 
 
   show_elapsed_time ('Oracle 11g result cache'); 
 
   FOR i IN 1 .. counter 
   LOOP 
      emprec := emplu2.onerow (employee_id_in); 
   END LOOP; 
 
   show_elapsed_time ('Cache table in PGA memory'); 
 
   FOR i IN 1 .. counter 
   LOOP 
      emprec := emplu1.onerow (employee_id_in); 
   END LOOP; 
 
   show_elapsed_time ('Execute query each time'); 
END;

Procedure created.

Statement 9
A total of 300,000 "look ups" - though only the first (always select) approach actually executes a SQL statement 100,000 times - hence the difference. You should expect the PGA cache to be fastest, but it is full of shortcomings: memory repeated for each session; only useful for completely static datasets; not helpful in a stateless environment, such as web apps. Ugh.
Let 'er Rip!
```
BEGIN 
   test_emplu ( 
      counter => 100000 
    , employee_id_in => 138); 
END;
```
```
"Oracle 11g result cache" completed in: 6:59:59.827 seconds
"Cache table in PGA memory" completed in: 6:59:59.959 seconds
"Execute query each time" completed in: 6:59:54.895 seconds
```

Additional Information

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