Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Identifying Dependencies for the Result Cache in 11g

Oracle 11g New Features Tips by Donald BurlesonJuly 17, 2015

Oracle 11g New Features Tips

As shown in the example it is important to identify the dependencies for the result cache if you with to have current data.  And since all sessions can and will utilize the result cache, keeping it current with the data is critical.

Because the result cache uses the PL/SQL signature (name, passed variables and return type) to uniquely identify cached result sets there are some restriction on what can be passed as variables. 

  • No IN OUT or OUT variable parameter.

  • No invoker rights or anonymous blocks.

  • No pipelined table functions.

  • No IN variable parameters of type BLOB, CLOB,NCLOB, REF CURSOR, Collection, Object or Record.

  • No RETURN type of BLOB, CLOB, NCLOB, REF CURSOR, Object, Collection or Record.

The result cache will consider a request a cache hit if the function and all parameter are bit-wise identical.  The characters ?AA? and ?AA  - are not bit-wise identical and will not result in a result cache hit.

If the function throws an unhandled exception, the results are not stored.

In a RAC environment, each instance will maintain its own result cache but cache invalidation will propagate to all instances. This allows for caching for applications affiliated with that instance but still invalidates that result cache if the dependent object is changed by any instance.

There are 4 new database initialization parameters that are used to support the result cache.


This parameter will define the max amount of SGA memory the result cache can use.  If set to 0 then the result cache is disabled.


This parameter defines the max memory that one result set can access.  It is a percentage of the RESULT_CACHE_MAX_SIZE.


This parameter works on SQL statements discussed in the next chapter.  If set to FORCE, all valid SQL statement results are cached.  If this parameter is set to MANUAL, only SQL containing the resultcache hint will be cached.


This parameter defines the number of minutes that a result based on a remote object will remain valid.  Since the remote object cannot be monitored for changes, this parameter is used to invalidate those cached objects.

There is a new PL/SQL package added to the database to support using the result cache called DBMS_RESULT_CACHE.  Below is an edited description of the package.



The procedure BYPASS turns the result cache functionality on and off.  If set to true the result cache is turned off, and not used.  The cache is not flushed by using this procedure.  The example below turns off the result cache.

SQL> exec dbms_result_cache.bypass (true);

Running this command with a false parameter will turn on the result cache and any previously cached data will be returned.

The STATUS function will return the current status of the result cache functionality.

SQL> select dbms_result_cache.status from dual;


The MEMORY_REPORT procedure returns a report on the current memory used by the result cache.

SQL> set serveroutput on
SQL> exec dbms_result_cache.memory_report

R e s u l t   C a c h e   M e m o r y   R e p o r t

Block Size          = 1K bytes
Maximum Cache Size  = 2112K bytes (2112 blocks)
Maximum Result Size = 105K bytes (105 blocks)
Total Memory = 103536 bytes [0.069% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.065% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 7 blocks
........... Used Memory = 25 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 23 blocks
................... PLSQL   = 10 blocks (10 count)
................... Invalid = 13 blocks (13 count)

PL/SQL procedure successfully completed.

The report covers the functions discussed earlier in the chapter which have a Boolean result so the memory use is very small.  When functions return larger values, this report can assist you with tuning memory allocations.

The above report is the normal or default report.  A more detailed report is available by passing a 'true? parameter to the procedure.

The remaining functions and procedures in the package are used to invalidate results or flush the cache.  The package provides a procedure and a function for each operation, either can be used as they both execute with the same results.

The FLUSH operation will remove all results from the cache.  It has two Boolean parameters; the first determines if the memory is maintained in the cache or released to the system.  The second parameter determines if the cache statistics are maintained or also flushed.  The following example uses the defaults and will flush the cache, releasing the memory to the system and clearing the statistics.

SQL> exec dbms_result_cache.flush;

If the memory report is re-run the dynamic memory will report 0, indicating that the cache released the memory.

Total Memory = 5140 bytes [0.003% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

The INVALIDATE operation invalidates all results in the cache that are dependent on an object.  These functions are overloaded so you can identify the object by owner and name or by object_id.

SQL> exec dbms_result_cache.invalidate('PUBS','SALES');

The function will return a number indicating the number of results invalidated by the command.

The INVALIDATE_OBJECT operation invalidates specific result objects.  You find result objects from V$RESULT_CACHE_OBJECTS dynamic performance view.

SQL> select id, name from v$result_cache_objects order by 1; 

--- ----------------------------------------------------------
  2 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  3 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  4 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  5 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  6 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1

  7 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  8 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  9 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
 10 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
 11 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1

12 rows selected.

To invalidate object 11 use the command below.

SQL> exec dbms_result_cache.invalidate_object(11);

The cross-session result cache can provide an incredible performance boost by not constantly execution the same function again and again.  Many times a batch job will execute hundreds of queries on each pass that execute the same function for each query.  By adding the result cache, the function is executed once at the beginning of the run and the result will be used by all the other queries in that run.  This can make a huge difference in the execution performance.  The result cache is also available to a SQL query using a hint.  If you have a large number of queries that all execute the same subquery, placing the result of the subquery in the result cache can make the results available to all the other queries.  This is discussed in the next chapter covering SQL.


In this chapter, we covered the enhancements to the PL/SQL Language introduced in the 11g database. These included:

PL/SQL Sequence access allowing a more efficient method to get the nextval or curval of a sequence without using a query.  This new format is also easier to read making the code more manageable.

The SIMPLE number type is used when there is no need to check bounds or null values.  The SIMPLE integer is more efficient to use and executes in the compiler.

The CONTINUE clause provides a method to terminate the current iteration of a loop and start the next iteration.

PL/SQL Code Inlining provides compile time code optimization.

Compound Triggers provide the ability to perform multiple actions at different timing points in one trigger.  This keeps the trigger code in the trigger and still provides modularity. 

The Cross-Session PL/SQL Result Cache is a powerful new feature that save function results in a cache and will reuse the cached result if the function is called with the same parameters instead of repeatedly executing the function.

The next chapter will introduce some complementary SQL new features that integrate completely into PL/SQL.


This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.