Watch out with function result cache based on data dictionary views

Result cache is a powerful tool to gain performance in PL/SQL.
There are many examples on the internet that proves this, e.g. these articles on All things Oracle:
Result Cache(1)
Result Cache(2)

But I’m not going to talk about performance.
This article is some kind of warning.

First I’ll show you how result cache works on a normal view.
I’ll create a table, a view on this table and a function that counts the rows in the view.

SQL> create table x (field1 varchar2(1), field2 number(1));

Table created.

SQL> create or replace view vie_x as select * from x;

View created.

SQL> CREATE OR REPLACE FUNCTION vie_x_rowcount(p_field1 IN VARCHAR2)
RETURN NUMBER RESULT_CACHE
IS
   l_return NUMBER;
BEGIN
   SELECT count(*)
     INTO l_return
     FROM vie_x
     WHERE field1 = p_field1;

   RETURN l_return;

END vie_x_rowcount;
/

Function created.

SQL> insert into x(field1, field2) values('x', 1);

1 row created.

SQL> commit;

Commit complete.

These are the statistics for the result cache, just to show you we’re starting without any caching.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       0
Find Count		               0
Invalidation Count	           0

When we execute the function, the statistics show that there’s an entry created in the cache.

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  1

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               0
Invalidation Count	           0

When we execute the same code again, we’ll get the same result and the statistics show us that the result is found in the cache.
Good job Oracle!

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  1

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           0

Let’s insert a new row in the table.
This time the statistics show us that the cache is “invalidated”, meaning the function has to be executed again to return the correct value.

SQL> insert into x values('x', 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           1

And the expected result…

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  2

The Oracle database has its own data dictionary, a set of tables where it stores all information about the database and what’s in it.
Data of these tables are available through views, data dictionary views.
In the following example I’ll use the data dictionary view that holds the information on columns.
I created a function that returns the number of columns for a certain table.

SQL> CREATE OR REPLACE FUNCTION number_of_columns(p_table_name VARCHAR2)
RETURN NUMBER RESULT_CACHE
IS

   l_return NUMBER;

BEGIN

   SELECT count(*)
     INTO l_return
     FROM user_tab_columns
    WHERE table_name = p_table_name;

   RETURN l_return;

END number_of_columns;
/

Function created.

To make sure we’ll start with a clean cache, I’ll flush it using the dbms_result_cache.flush procedure.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       0
Find Count		               0
Invalidation Count	           0

When we execute the function, we’ll get the expected result: the function is executed and a cache entry is created.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               0
Invalidation Count	           0

We can execute it again and see that the return value is retrieved from the cache.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           0

Let’s add a column to the table.
This should add a new row in a data dictionary table and thus in the data dictionary view we use in our function.

SQL> alter table x add (field3 date);

Table altered.

SQL> desc x
 Name					   Null?    Type
 ------------------------- -------- ----------------------------
 FIELD1 					        VARCHAR2(1)
 FIELD2 					        NUMBER(1)
 FIELD3 					        DATE

Now execute the function again.
And the result is…

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

Not what we expected!
When we take a look at the result cache statistics, it shows that the cache wasn’t invalidated and the result was retrieved from the result cache.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               2
Invalidation Count	           0

When we flush the cash and execute the function again, we’ll get the correct result cache.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     3

So, it seems that the result cache isn’t invalidated on data dictionary tables.
And indeed this is what I found in the Oracle documentation:

You cannot cache results when the following objects or functions are in a query:

  • Temporary tables and tables in the SYS or SYSTEM schemas

APEX 5.0: Modal dialogs have never been so easy!

Like all of us, I have had the pleasure to take a look at APEX 5.0 EA1. One of the most anticipated features was build-in support for modal dialogs. In this blog we will examine this feature more closely.

An easy way to investigate this feature is by creating two simple pages: report & form using the wizard.

0Modaldialogs

Follow the wizard and base the report & form on the demo_customer table. When you come to the page where you are creating the form then you will notice the option “Page Mode”. This mode will determine the type of page you use.

1wizard

As you can see there are three modes:
–          Normal: used by normal pages
–          Modal: when this is selected your page will show as a modal dialog
–          Non – Modal:  in this mode the page will act as a normal pop-up allowing you to continue working in both windows. Note that the original window that called the pop-up can still interact with this pop – up

We select the modal mode, and continue the wizard. We have now created two pages with one being a modal dialog. So we see here that the modal dialog is a separate page, and not some region on the page that calls it. This will allow better re-usability, and easier validations. You might notice there is no code anywhere that actually calls the modal page. That is because any page that branches, links or redirects to a page with mode “Modal”, will call that page as a modal dialog automatically.

Apart from the mode difference between the two pages, APEX also created a “Close Dialog” process, and a dynamic action that fires when the cancel button is pressed on our modal page. Both of these will make sure our modal dialog is closed when the end-user presses a button.

2Close dialog

Lastly we might want our report to refresh when the dialog is closed. To do this we go to our report page and right click on our report to create a dynamic action. As event select “Dialog Closed” and region “Customers”.

3DA event4DA action

Then right click on your Dynamic action and create a Refresh action with affected item our Customers region. Now our Customers region will be refreshed when our modal dialog closes.

Time to test our page!

5Dialog

As you can see our modal dialog renders nicely.

There is one last note I would like to add, for the developers who like to write the code for their modal dialog themselves, the APEX developers have enabled us to do so. When you go to the theme for our modal page, then in the bottom you will notice these codes:

6Dialog init code

It basically means that, should you wish to, you can modify this code here, or just replace it with your own, allowing for maximum flexibility!

In conclusion I think it is safe to say that the APEX developers have done an ACE job implementing this feature, thumbs up! If you would like to try the EA and modal dialogs for yourself then you can do that here. If you want to read up on the full list of APEX EA1 features you can do that here.