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

Bryxx has launched!

On Tuesday, May 7 the Bryxx launch event took place. In the beautiful setting of the Flandria boat, and in the presence of a large number of customers, we revealed the services of this new venture. As a joint venture between the iAdvise and Contribute infrastructure teams, Bryxx will specifically focus on the middleware field.
In bringing together both expert middleware teams, we will focus on
  • Opening up your business critical web applications to your intranet or to the internet
  • Making sure that these applications, deployed on your middleware stack, are secure on all layers of the underlying architecture. Security from-data-to-browser
  • Streamlining and automating your process of development towards production
  • Providing you with the opportunities to outsource the maintenance of your private middleware cloud or to outsource your entire private middleware cloud
From a technical point of view Bryxx will dedicate its expertise to 4 domains:
  • Oracle Cloud Application Foundation (with web logic as the main driver)
  • Oracle Identity & Access management
  • Oracle Database Security
  • DevOps
With respect to these 4 areas of expertise, Bryxx provides strong consultancy profiles on all levels (pre-sales, infrastructure architects, senior implementation engineers, etc) to design, install, configure, maintain and monitor your middleware platform as well as to streamline the process of application development towards your preferred middleware solution.
When you add our managed services and hosted solutions offering on each of these domains to this package, with strong partnerships in the backend, we believe Bryxx has a strong and complete offering for all your middleware challenges !Our team of 14 dedicated and experienced middleware engineers is ready for you.
Want to know more?
Visit us at www.bryxx.eu or contact us at info@bryxx.eu
 bryxx1bryxx2bryxx3bryxx4

Mobile Business Development seminar in Belgium and The Netherlands(4 and 6 June)

iAdvise organises a free seminar about Mobile Business Development on 4(Antwerp, Belgium) and 6(Breda, The Netherlands) June.

In this seminar we will discuss Mobile Development and Usability(UX – User Experience) in Mobile Development.
But we also compare different solutions, give pros and cons about: APEX Mobile, ADF Mobile and Tabris.

Of course we will also show you best practices, problems and solutions,…

More information(Dutch): http://www.iadvise.eu/seminars

OBUG Connect 2013: iAdvise presentation on ADF & Web Services

On 26 March OBUG Connect, the yearly Oracle Benelux User Group conference, will be held in Antwerp.

iAdvise will be presenting about ADF and web services.
We’ll show you how you can expose your ADF Business components as Web Services.
But also how you can consume Web Services in your application.

The presentation is session 3 in the “Middleware track”(track 7) and starts at 15.45.
We hope to see you in Antwerp!

 

Slides of the ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”

A few weeks ago we did an ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”.

About 186 followed the seminar online.
Those people received a link to the recorded session and the slides of the presentation.

For those who couldn’t attend, these are the slides of the presentation: ADF Immersion presentation
But the presentation was a lot more than a few slides, there was also a demo(>30 minutes).
So if you want to see the full recording, you can see all past webinars as a full ODTUG member.

If you need more info on ADF methodologies and ADF best practices or want to ask questions about these topics, check out the ADF EMG group.

ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”

On Oracle Open World we had the chance to present our “Oracle ADF Immersion” track thanks to the ADF EMG.
In case you couldn’t attend OOW or missed the session, you’ve got another chance: on january 10, 2013 we will present it as an ODTUG Webinar.

We will show you how you can start with ADF, coming from an Oracle Forms(or non-java) background.
You can register for the webinar here.

If you like to join the ADF EMG, don’t hesitate, it’s a free ADF usergroup!

Generating XML from SQL & PL/SQL and Code Instrumentation

From time to time you’ll have to represent your data stored in a database in an XML format, eg. to exchange it between systems, to send it to external parties, etc.
In an article on All Things Oracle I give an introduction on how you can generate XML documents:

 

Another interesting article from my colleague Jan Leers is about Code Instrumentation.

Whenever we execute a procedure, it’s out of our hands. We expect it to do what it was designed to do, but what if it doesn’t?
What if it takes a lot more time then expected?
All kind of questions start to arise:

  • Is it almost finished, should I wait just a few more minutes?
  • Is it trapped in an infinitive loop? Or are my queries running slow?
  • Can I safely kill it? Or did it already commit some changes and should I reverse them?
  • Which job should I kill, is this mine?

Want to find out how you can answer these questions?  Read his article on All Things Oracle

Seminar: Oracle and Reporting(Mechelen 08/11/2012)

Do you have one or more applications on top of on Oracle Database?

Do you want to show this data in documents and/or reports?

Did you know you don’t have to make a big investment in a BI solution to create attractive letters, orders, invoices or lists?

But which reporting tool, from a long list, is the best solution?

After many years of using and testing different tools, we will show and share our experiences of our preferred reporting tools:

  • Oracle Application Express
  • Oracle Reports
  • Oracle BI Publisher
  • JasperReports
  • SQL Word
  • Eclipse Birt
  • PL/PDF
  • DocuFy

We’re going to demo and compare those tools so you can learn the possibilities, advantages/disadvantages, …
Of course we’re also taking the learning curves and prices in count, so you can find out the best solution for you and your company.

More info and Registration(Dutch)