Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

RESULT_CACHE Hint in 12.1.0.2

4 September 2014 1 Comment Written by Christian Antognini

Every new Oracle Database patch introduces not only documented features, but also undocumented ones. In this short post I would like to provide information about an enhancement of the RESULT_CACHE hint.

According the documentation, the RESULT_CACHE hint has no parameter. But, as of 12.1.0.2, at least the SNAPSHOT parameter is supported. Its purpose is to specify how much time (in seconds) after the creation the result cache entry has to expire. In other words, you can specify that a specific entry expires even though the objects is based on doesn’t change.

The following example illustrates:

SQL> set time on

18:14:47 SQL> execute dbms_result_cache.flush

18:14:47 SQL> SELECT /*+ result_cache(snapshot=10) */ count(*) FROM scott.emp;

  COUNT(*)
----------
        14

18:14:47 SQL> SELECT status
18:14:47   2  FROM v$result_cache_objects
18:14:47   3  WHERE type = 'Result'
18:14:47   4  AND creator_uid = sys_context('userenv','current_userid');

STATUS
---------
Published

18:14:47 SQL> execute dbms_lock.sleep(10)

18:14:57 SQL> SELECT status
18:14:57   2  FROM v$result_cache_objects
18:14:57   3  WHERE type = 'Result'
18:14:57   4  AND creator_uid = sys_context('userenv','current_userid');

STATUS
---------
Expired

18:14:57 SQL> SELECT status
18:14:57   2  FROM v$result_cache_objects
18:14:57   3  WHERE type = 'Result'
18:14:57   4  AND creator_uid = sys_context('userenv','current_userid');

STATUS
---------
Invalid
12cR1, Result Cache
Differences between the First and the Second Edition of Troubleshooting Oracle Performance
The APPROX_COUNT_DISTINCT Function – A Test Case

1 Comment

  1. Dom Brooks Dom Brooks
    5 September 2014    

    Interesting addition.
    When 11.1 first came out, I remember playing with the plsql function result cache and using a default function argument of sysdate rounded to the nearest required interval to achieve a similar effect.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.