The Oracle PL/SQL Results Cache

Oracle offers a results cache in the database (from 11.2 onwards) and in PL/SQL (again, from 11.2 onwards) which can greatly reduce execution time of repeated statements when insert/update/delete activity is not heavy. The mechanism in PL/SQL, however, may not work as expected with global temporary tables. Let’s look at what you might see when using this option, using a slightly modified example from Jonathan Lewis.

This starts with flushing the shared pool, creating and populating a global temporary table and creating a function to display the number of available records from the results cache. First the table is created and populated with a single row:

SQL> set serveroutput on size 1000000
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> truncate table glob_tmp;

Table truncated.

SQL> drop table glob_tmp;

Table dropped.

SQL> create global temporary table glob_tmp (fnum number)
  2  on commit preserve rows
  3  ;

Table created.

SQL>
SQL> insert into glob_tmp values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>

Compute stats on the global temporary table:

SQL> execute dbms_stats.gather_table_stats(user,'glob_tmp');

PL/SQL procedure successfully completed.

SQL>

Create the function to return the number of visible rows in the results cache:

SQL>
SQL> create or replace function f_cache
  2  return number
  3  result_cache
  4
  5  is
  6          m_ret number;
  7  begin
  8          select  max(fnum)
  9          into    f_cache.m_ret
 10          from    glob_tmp
 11          ;
 12
 13          return f_cache.m_ret;
 14  end;
 15  /

Function created.

SQL>

Execute the function for the current session; all seems well for the moment since we’ve inserted and committed a row:

SQL> execute dbms_output.put_line(f_cache)
1

PL/SQL procedure successfully completed.

SQL>

Now create a second session and do nothing except run the function:

SQL> execute dbms_output.put_line(f_cache);
1

PL/SQL procedure successfully completed.

SQL>

Insert a row into the global temporary table and run the function again:

SQL> insert into glob_tmp values(0);

1 row created.

SQL> execute dbms_output.put_line(f_cache);
0

PL/SQL procedure successfully completed.

SQL>

This is curious behavior. After Session 1 had inserted a row into the global temporary table and issued a commit both sessions could ‘see’ it, yet when Session 2 inserts a row, but doesn’t commit, that original row ‘goes away’ because Oracle thinks it’s providing the correct answer. Let’s do something really destructive and truncate the global temporary table in Session 1:

SQL> truncate table glob_tmp;

Table truncated.

SQL> execute dbms_output.put_line(f_cache);
1

PL/SQL procedure successfully completed.

SQL>

The table data has been truncated, yet the function still shows that one row exists in the results cache. Note that Session 2 has not issued a commit for its insert. Let’s do that now:

SQL> commit;

Commit complete.

SQL>

Back to Session 1, let’s execute the function again:

SQL> execute dbms_output.put_line(f_cache);

PL/SQL procedure successfully completed.

SQL>

And from Session 2, where nothing else has occurred other than the commit, we see the same results:

SQL> execute dbms_output.put_line(f_cache);

PL/SQL procedure successfully completed.

SQL>

Yet, the truncate in Session 1 only affected the data inserted by Session 1. Going back to Session 2, let’s chech that session’s contents of glob_tmp:

SQL> select count(*) from glob_tmp;

  COUNT(*)
----------
         1

SQL>

It would be expected, I would think, that the function would return a 1 for the committed row in Session 2, and either NULL or 0 for Session 1, but that’s not the case. Oracle reports several bugs in relation to the PL/SQL results cache:

Bug 21905592 : PL/SQL RESULT CACHE MUST BE BYPASSSED WHEN SESSION SCN NOT CURRENT
Bug 21905695 : BAD X-SESSION RESULTS W/ RESULT-CACHED PL/SQL FN AND “SET TXN READ ONLY”
Bug 21907155 : PL/SQL RC FN SHOWS ONE SESSION THE CONTENTS OF ANOTHER SESSION’S GTT
Bug 21885173 : THE LNPLS SECTION “RESTRICTIONS ON RESULT-CACHED FUNCTIONS” NEEDS RE-WRITE

So we see that the PL/SQL results cache can produce inconsistent results and unexpected behavior across sessions with global temporary tables. Take care when using the PL/SQL results cache with temporary tables since you may see ‘answers’ that aren’t correct even though Oracle thinks that they are. Looking in the documentation Oracle notes that global temporary tables don’t meet the criteria for result caching, and this example shows a good reason to believe that restriction. I’ve found that the documentation may not be referenced until a ‘problem’, like the one illustrated here, surfaces.

When properly used the PL/SQL results cache can be of great help to application programmers, once the known restrictions have been taken into account. For such features it’s best to read the documentation before attempting to use them, so that you won’t be surprised later.

See all articles by David Fitzjarrell