Friday, February 20, 2015

Is the Function Result Cache operative on my database?

At a very fine RMOUG Training Days 2015 conference this week (about which I will post later...soon...promise!), an attendee complained that:

1. He'd learned about the function result cache and was really excited about it.
2. Spent two days trying to figure out why it wasn't working.
3. Then discovered that the feature is only enabled for Enterprise Edition.

He wondered if there was a way to have determined this from the start, and saved himself a couple of frustrating days.

So, first of all, if you want to quickly determine if this feature is working, run the following script:

/* Does the function result cache work in my database? */

CREATE OR REPLACE FUNCTION frc_test
   RETURN VARCHAR2 result_cache
IS
BEGIN
   dbms_output.put_line ('Ran FRC_TEST');

   RETURN 'Return Value';

END;
/
BEGIN
   dbms_output.put_line (frc_test());
   dbms_output.put_line (frc_test());

END;
/

If you see this:

Ran FRC_TEST
Return Value
Return Value

then the result cache feature is enabled and working.

If you see this:

Ran FRC_TEST
Return Value
Ran FRC_TEST
Return Value

then you know that it is not. Alternatively, if you have EXECUTE authority on the DBMS_RESULT_CACHE package, you can simply run:

BEGIN
   dbms_output.put_line (dbms_result_cache.status);
END;
/

"ENABLED" means, well, you get the idea.

So: are you running Enterprise Edition? Find out with this query:

select * from v$version;
Beyond that, you can check for availability of specific features of the Oracle Database 11g release here. For Oracle Database 12c, check out this page.

1 comment:

  1. It is enabled on Personal Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

    ReplyDelete