1 Reply Latest reply: Sep 28, 2010 1:52 AM by Saubhik RSS

    result_cache and data dictionary views

      Are there any special considerations when caching the results of a function which uses data dictionary views to determine it's results?
      This question has popped because I have a such a result_cached function for which the result_cache objects are not getting invalidated even when the underlying data dictionary views have changed and the function gives 'stale' values in it's output. Adding the relies_on clause has not helped either.

      Here is what I am trying to do:
      The function accepts table name as its input and tries to determine all the child tables using the sys.dba_constraints view. The results are returned in a pl/sql table and are cached so that the subsequent calls to this function use the result_cache.
      Everything works fine for the parent/child tables which have been created before the creation of this function. All the results are correct.

      The problem starts when a new child table is added to an existing parent table.
      The v$result_cache_objects view shows the result of this function as 'Published' and the output of the function does not show the newly created child table.
      Same is the case when an existing child table is deleted; the function continues to return it in the output as it is pulled from the result_cache.

      Oracle version:
      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE Production
      TNS for Linux: Version - Production
      NLSRTL Version - Production
        • 1. Re: result_cache and data dictionary views
          Restrictions on Result-Cached Functions*

          To be result-cached, a function must meet all of these criteria:

          * It is not defined in a module that has invoker's rights or in an anonymous block.
          * It is not a pipelined table function.
          * It does not reference dictionary tables, temporary tables, sequences, or nondeterministic SQL functions.
          For more information, see Oracle Database Performance Tuning Guide.
          * It has no OUT or IN OUT parameters.
          * No IN parameter has one of these types:
          o BLOB
          o CLOB
          o NCLOB
          o REF CURSOR
          o Collection
          o Object
          o Record
          * The return type is none of these:
          o BLOB
          o CLOB
          o NCLOB
          o REF CURSOR
          o Object
          o Record or PL/SQL collection that contains an unsupported return type

          It is recommended that a result-cached function also meet these criteria:

          * It has no side effects.
          For information about side effects, see "Subprogram Side Effects".
          * It does not depend on session-specific settings.
          For more information, see "Making Result-Cached Functions Handle Session-Specific Settings".
          * It does not depend on session-specific application contexts.