Oracle Tips and Tricks — David Fitzjarrell

October 26, 2015

Results, What Results?

Filed under: General — dfitzjarrell @ 11:55

"For instance," said the boy again, "if Christmas trees were people and people were Christmas trees,
we'd all be chopped down, put up in the living room, and covered in tinsel, while the trees opened our presents."
"What does that have to do with it?" asked Milo.
"Nothing at all," he answered, "but it's an interesting possibility, don't you think?"
Norton Juster, The Phantom Tollbooth 

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, a fact mentioned in the documentation but possibly overlooked until its effects are seen when it is actually used. Let’s look at what behavior 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 garden-variety stats on the global temporary table (no ‘special’ options in use):


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 since we’ve inserted and committed a row, but this is about to change:


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>

From the second sesson 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 as evidenced here. Oracle reports several bugs in relation to the PL/SQL results cache, listed below:

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 documentatipon Oracle notes that global temporary tables don’t meet the criteria for result caching, and this example shows a good reason to beleive 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.

Sometimes a change in perspective can be very helpful.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: